mysql 选择列表_如何在选择列表中显示类别,子类别,子子类别 – php / mysql？

我想在选择列表(下拉列表)中显示类别,子类别和子子类别,就像wordpress在其管理面板中显示的方式一样.首先看看我的数据库表(tb_categories) –

我想要HTML格式的以下输出 –

“无”和“未分类”这两个项目在代码中是硬编码的.我想知道如何使用选择列表选项按层次顺序显示类别及其子类别.

我正在尝试使用以下SQL查询,其中我使用自联接.这里是 –

SELECT

`cat`.`category_name` AS 'category name',`cat2`.`category_name` AS 'parent category'

FROM

`tb_categories` AS `cat`

LEFT JOIN `tb_categories` AS `cat2` ON `cat`.`category_parent` = `cat2`.`category_id`

ORDER BY

'parent category'

它给出的输出是 –

Array

(

[0] => Array

(

[0] => My Parent Category

[category name] => My Parent Category

[1] =>

[parent category] =>

)

[1] => Array

(

[0] => Parent Category 2

[category name] => Parent Category 2

[1] =>

[parent category] =>

)

[2] => Array

(

[0] => Parent Category 3

[category name] => Parent Category 3

[1] =>

[parent category] =>

)

[3] => Array

(

[0] => My Child Category

[category name] => My Child Category

[1] => My Parent Category

[parent category] => My Parent Category

)

[4] => Array

(

[0] => Sports

[category name] => Sports

[1] =>

[parent category] =>

)

[5] => Array

(

[0] => Cricket is best

[category name] => Cricket is best

[1] => Sports

[parent category] => Sports

)

[6] => Array

(

[0] => AJAX

[category name] => AJAX

[1] =>

[parent category] =>

)

[7] => Array

(

[0] => hockey is best

[category name] => hockey is best

[1] => Sports

[parent category] => Sports

)

)

我不知道甚至不确定如何在该选择列表中显示上述数据.我们怎么做？我们怎样才能使用连接呢？如果我们使用连接,那么我们是否需要一些数组来存储和排序结果？还有我们如何在循环中使用多个查询来做到这一点？哪种方法最好？