如何在选择列表中显示类别、子类别、子子类别 - php/mysql？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25084926/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to display categories, subcategories, sub-subcategories in select list - php/mysql?
提问by Sachin
I want to display categories, subcategories and sub-subcategories in a select list (drop-down list) like the way the WordPress shows in its admin panel. First look at my database table (tb_categories) -
我想在选择列表（下拉列表）中显示类别、子类别和子子类别，就像 WordPress 在其管理面板中显示的方式一样。先看看我的数据库表（tb_categories）——
I want the following output in HTML form -
我想要以下 HTML 形式的输出 -
The two items "None" and "Uncategorized" are hardcoded in the code. I am wondering how to display categories and their subcategories in hierarchical order using select list options.
“无”和“未分类”这两项在代码中是硬编码的。我想知道如何使用选择列表选项以分层顺序显示类别及其子类别。
I am trying with the following sql query in which I am using self join. Here it is -
我正在尝试使用以下 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'
And the output it is giving is -
它给出的输出是 -
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
)
)
I don't know and even not sure how can I display above data in that select list. How we do that? How can we do it using joins? If we use joins then do we need some array to store and sort the results? And also how do we do it using several queries in a loop? Which method will be best?
我不知道，甚至不确定如何在该选择列表中显示上述数据。我们怎么做？我们如何使用连接来做到这一点？如果我们使用连接，那么我们是否需要一些数组来存储和排序结果？以及我们如何在循环中使用多个查询来做到这一点？哪种方法最好？
回答by hellcode
Assuming your given array is in $array you can use this. But as I told you already you should select the ids to handle categories with the same name and to use them as option values in your selectbox:
假设您给定的数组在 $array 中，您可以使用它。但是正如我已经告诉过您的那样，您应该选择 id 来处理具有相同名称的类别，并将它们用作选择框中的选项值：
$options = get_options($array);
echo "<select>";
foreach($options as $val) {
echo "<option>".$val."</option>";
}
echo "</select>";
function get_options($array, $parent="", $indent="") {
$return = array();
foreach($array as $key => $val) {
if($val["parent category"] == $parent) {
$return[] = $indent.$val["category name"];
$return = array_merge($return, get_options($array, $val["category name"], $indent." "));
}
}
return $return;
}
Assuming that you now have the ids in your array as "category_id" and "parent_category_id" you can use this. The "x" prior to the key in $return is just to avoid that php changes your keys, because they are numeric.
假设您现在数组中的 id 为“category_id”和“parent_category_id”，您可以使用它。$return 中键之前的“x”只是为了避免 php 更改您的键，因为它们是数字。
$options = get_options($array);
echo "<select>";
foreach($options as $key => $val) {
echo "<option value='".substr($key,1)."'>".$val."</option>";
}
echo "</select>";
function get_options($array, $parent=0, $indent="") {
$return = array();
foreach($array as $key => $val) {
if($val["parent_category_id"] == $parent) {
$return["x".$val["category_id"]] = $indent.$val["category name"];
$return = array_merge($return, get_options($array, $val["category_id"], $indent." "));
}
}
return $return;
}
回答by hex494D49
This is the simplest category table structure
这是最简单的类别表结构
+-------------------------+
| categories |
+-------------------------+
| category_id (int) | PK
| parent_id (int) | Index
| category_name (varchar) |
| ... |
+-------------------------+
The function below will recursively fetch all categories and build a hierarchy. As you may see, you can choose a value to be preselected or you can fetch only a part of the hierarchy changing parent value.
下面的函数将递归获取所有类别并构建层次结构。如您所见，您可以选择要预选的值，也可以仅获取层次结构更改父值的一部分。
function build_category_tree(&$output, $preselected, $parent=0, $indent=""){
$r = mysql_query("
SELECT category_id, category_name FROM categories WHERE parent_id = " . $parent . "
");
while($c = mysql_fetch_array($r, MYSQL_ASSOC)){
$selected = ($c["category_id"] == $preselected) ? "selected=\"selected\"" : "";
$output .= "<option value=\"" . $c["category_id"] . "\" " . $selected . ">" . $indent . $c["category_name"] . "</option>";
if($c["category_id"] != $parent){
build_category_tree($output, $preselected, $c["category_id"], $indent . " ");
}
}
}
Usage:
用法：
<?php
build_category_tree($categories, 0);
// if you want to preselect a value and start from some subcategory
build_category_tree($categories, 5, 2);
?>
<!-- HTML -->
<select><?php echo $categories ?></select>
<!-- if you want to add some extra options -->
<select>
<option value="-1">Choose a category</option>
<?php echo $categories ?>
</select>
Side note: Although this function looks elegant, do not use it if you have thousands of categories. Also, use MySQLi or PDO_MySQL extension in your final version.
旁注：虽然这个功能看起来很优雅，但如果你有数千个类别，请不要使用它。此外，在您的最终版本中使用 MySQLi 或 PDO_MySQL 扩展。
回答by Thirumani Raj
$con = mysqli_connect("localhost","root","","categories");
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql = mysqli_query($con,"SELECT `cat`.`categoryName` AS 'category name', `cat2`.`categoryName` AS 'parent category' FROM `product_category` AS `cat` LEFT JOIN `product_category` AS `cat2` ON `cat`.`parentId` = `cat2`.`categoryId` where `cat2`.`categoryName` !='NULL' order by `cat2`.`categoryName`");
while($res=mysqli_fetch_array($sql,MYSQLI_ASSOC)){
$cat[] = $res['category name'];
$parent[]= $res['parent category'];
}
$parents = "";
for($i=0;$i<count($cat);$i++){
if($parents!=$parent[$i]){
if($i!=0){ echo "</ul>"; }
echo $parent[$i]."<ul><li>".$cat[$i]."</li>" ;
}else{
echo "<li>".$cat[$i]."</li>" ;
}
$parents = $parent[$i];
}
echo "</ul>";
Form More tutorials regarding php, ajax, jquery, mysql, interview questions. kindly see my blogs hackandphp.com
形成更多关于 php、ajax、jquery、mysql、面试问题的教程。请查看我的博客 hackandphp.com
