类别层次结构 (PHP/MySQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4452472/
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
Category Hierarchy (PHP/MySQL)
提问by beytarovski
I am trying to get my all categories and sub-categories from MySQL database in a hierarchy:
我正在尝试从层次结构中的 MySQL 数据库中获取我的所有类别和子类别：
My result should be like that (just example):
我的结果应该是这样的（只是例子）：
- Cat A
- Sub-Cat 1
- Sub_Sub_Cat 1
- Sub_Sub_Cat 2
- Sub_Cat 2
- Cat B
- Cat C
- ...
- 甲类
- 子类别 1
- Sub_Sub_Cat 1
- Sub_Sub_Cat 2
- 子_猫 2
- 猫乙
- 猫 C
- ...
MySQL code:
MySQL代码：
CREATE TABLE IF NOT EXISTS `categories` (
`category_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT 'for sub-categories'
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Simply, how can get it in a hirarchy with PHP codes?
简单地说，如何使用PHP 代码将其置于层次结构中？
回答by simshaun
When using an adjacency list model, you can generate the structure in one pass.
使用邻接表模型时，您可以一次性生成结构。
Taken from One Pass Parent-Child Array Structure (Sep 2007; by Nate Weiner):
取自One Pass 父子数组结构（2007 年 9 月；Nate Weiner）：
$refs = array();
$list = array();
$sql = "SELECT item_id, parent_id, name FROM items ORDER BY name";
/** @var $pdo \PDO */
$result = $pdo->query($sql);
foreach ($result as $row)
{
$ref = & $refs[$row['item_id']];
$ref['parent_id'] = $row['parent_id'];
$ref['name'] = $row['name'];
if ($row['parent_id'] == 0)
{
$list[$row['item_id']] = & $ref;
}
else
{
$refs[$row['parent_id']]['children'][$row['item_id']] = & $ref;
}
}
From the linked article, here's a snippet to create a list for output. It is recursive, if there a children for a node, it calls itself again to build up the subtree.
从链接的文章中，这里有一个片段来创建输出列表。它是递归的，如果节点有子节点，它会再次调用自己来构建子树。
function toUL(array $array)
{
$html = '<ul>' . PHP_EOL;
foreach ($array as $value)
{
$html .= '<li>' . $value['name'];
if (!empty($value['children']))
{
$html .= toUL($value['children']);
}
$html .= '</li>' . PHP_EOL;
}
$html .= '</ul>' . PHP_EOL;
return $html;
}
Related Question:
相关问题：
回答by wajih
I have a new idea I think it will be nice. The idea is this: in category_parent column we will insert a reference to all parents of this node.
我有一个新想法，我认为它会很好。想法是这样的：在 category_parent 列中，我们将插入对该节点所有父节点的引用。
+----+----------------------+-----------------+ | id | category_name | hierarchy | +----+----------------------+-----------------+ | 1 | cat1 | 1 | +----+----------------------+-----------------+ | 2 | cat2 | 2 | +----+----------------------+-----------------+ | 3 | cat3 | 3 | +----+----------------------+-----------------+ | 4 | subcat1_1 | 1-4 | +----+----------------------+-----------------+ | 5 | subcat1_2 | 1-5 | +----+----------------------+-----------------+ | 6 | subsubcat1_1 | 1-4-6 | +----+----------------------+-----------------+ | 7 | subsubcat1_2 | 1-4-7 | +----+----------------------+-----------------+ | 8 | subsubcat1_3 | 1-4-8 | +----+----------------------+-----------------+ | 9 | subsubcat1_3_1 | 1-4-8-9 | +----+----------------------+-----------------+ | 10 | subsubcat1_3_2 | 1-4-8-10 | +----+----------------------+-----------------+ | 11 | subsubcat1_3_1_1 | 1-4-8-9-11 | +----+----------------------+-----------------+ | 12 | subsubsubcat1_3_1_1 | 1-4-8-9-12 | +----+----------------------+-----------------+ | 13 | subsubsubcat1_3_1_2 | 1-4-8-9-11-13 | +----+----------------------+-----------------+ | 14 | subsubsubcat1_2_1_3 | 1-4-8-9-11-14 | +----+----------------------+-----------------+
if you look at my updated table you will notice that every record has an link to its parents, not only the direct one, But also all of parents. And for that job I made some modification to insert to be:
如果您查看我更新的表格，您会注意到每条记录都有一个指向其父项的链接，不仅是直接的，而且还有所有的父项。对于这项工作，我做了一些修改以插入：
Insert into table_name (category_name, hierarchy) values ('new_name', (concat(parent_hierarch, '-', (SELECT Auto_increment FROM information_schema.tables WHERE table_name='table_name'))))
Now lets make your desired queries:
现在让我们进行您想要的查询：
1- all sub categories of cars:
1-汽车的所有子类别：
select * from table_name where hierarchy like '1-%'
2- if you need all parent of BLACK you simply type:
2- 如果您需要 BLACK 的所有父级，您只需键入：
select * from table_name where hierarchy = '1-4-8-9' or hierarchy = '1-4-8' or hierarchy = '1-4' or hierarchy = '1'
(you can build that query from php, splitting hierarchy field at '-' char)
（您可以从 php 构建该查询，在 '-' 字符处拆分层次结构字段）
3- To see all categories, with level and direct parent:
3- 要查看所有类别，具有级别和直接父级：
select *, SUBSTR(hierarchy, 1, (LENGTH(hierarchy) - LENGTH(id) - 1)) as parent, LENGTH(hierarchy) - LENGTH(REPLACE(hierarchy, '-', '')) as level From table_name
+----+----------------------+-----------------+-----------+--------+ | id | category name | hierarchy | parent | level | +----+----------------------+-----------------+-----------+--------+ | 1 | cat1 | 1 | | 0 | +----+----------------------+-----------------+-----------+--------+ | 2 | cat2 | 2 | | 0 | +----+----------------------+-----------------+-----------+--------+ | 3 | cat3 | 3 | | 0 | +----+----------------------+-----------------+-----------+--------+ | 4 | subcat1_1 | 1-4 | 1 | 1 | +----+----------------------+-----------------+-----------+--------+ | 5 | subcat1_2 | 1-5 | 1 | 1 | +----+----------------------+-----------------+-----------+--------+ | 6 | subsubcat1_1 | 1-4-6 | 1-4 | 2 | +----+----------------------+-----------------+-----------+--------+ | 7 | subsubcat1_2 | 1-4-7 | 1-4 | 2 | +----+----------------------+-----------------+-----------+--------+ | 8 | subsubcat1_3 | 1-4-8 | 1-4 | 2 | +----+----------------------+-----------------+-----------+--------+ | 9 | subsubcat1_3_1 | 1-4-8-9 | 1-4-8 | 3 | +----+----------------------+-----------------+-----------+--------+ | 10 | subsubcat1_3_2 | 1-4-8-10 | 1-4-8 | 3 | +----+----------------------+-----------------+-----------+--------+ | 11 | subsubcat1_3_1_1 | 1-4-8-9-11 | 1-4-8-9 | 4 | +----+----------------------+-----------------+-----------+--------+ | 12 | subsubsubcat1_3_1_1 | 1-4-8-9-12 | 1-4-8-9 | 4 | +----+----------------------+-----------------+-----------+--------+ | 13 | subsubsubcat1_3_1_2 | 1-4-8-9-11-13 |1-4-8-9-11 | 5 | +----+----------------------+-----------------+-----------+--------+ | 14 | subsubsubcat1_2_1_3 | 1-4-8-9-11-14 |1-4-8-9-11 | 5 | +----+----------------------+-----------------+-----------+--------+
This is a new idea and need some improvement.
这是一个新的想法，需要一些改进。
回答by Waruna Manjula
Try the following code
试试下面的代码
//connect to mysql and select db
//连接mysql并选择db
$conn = mysqli_connect('localhost', 'user', 'password','database');
$conn = mysqli_connect('localhost', 'user', 'password','database');
if( !empty($conn->connect_errno)) die("Error " . mysqli_error($conn));
//call the recursive function to print category listing
category_tree(0);
//Recursive php function
function category_tree($catid){
global $conn;
$sql = "select * from category where parent_id ='".$catid."'";
$result = $conn->query($sql);
while($row = mysqli_fetch_object($result)):
$i = 0;
if ($i == 0) echo '<ul>';
echo '<li>' . $row->cat_name;
category_tree($row->id);
echo '</li>';
$i++;
if ($i > 0) echo '</ul>';
endwhile;
}
//close the connection
mysqli_close($conn);
?>
回答by Farhan
@Amnon Your code works perfectly. Just tested it with CodeIgniter and it worked like a charm. Here's the working code if anyone needs it:
@Amnon 您的代码运行良好。刚刚使用 CodeIgniter 对其进行了测试，它的效果非常好。如果有人需要，这是工作代码：
<?php
function disTree($all_cats) {
$tree = array();
foreach ($all_cats as $cat)
{
$pid = $cat->parent_id;
$id = $cat->cat_id;
$name = $cat->cat_name;
// Create or add child information to the parent node
if (isset($tree[$pid]))
// a node for the parent exists
// add another child id to this parent
$tree[$pid]["children"][] = $id;
else
// create the first child to this parent
$tree[$pid] = array("children"=>array($id));
// Create or add name information for current node
if (isset($tree[$id]))
// a node for the id exists:
// set the name of current node
$tree[$id]["name"] = $name;
else
// create the current node and give it a name
$tree[$id] = array( "name"=>$name );
}
return $tree;
}
function toUL($tree, $id, $html){
$html .= '<ul>'.PHP_EOL;
if (isset($tree[$id]['name']))
$html .= '<li>' . $tree[$id]['name'];
if (isset($tree[$id]['children']))
{
$arChildren = &$tree[$id]['children'];
$len = count($arChildren);
for ($i=0; $i<$len; $i++) {
$html .= toUL($tree, $arChildren[$i], "");
}
$html .= '</li>'.PHP_EOL;
}
$html .= '</ul>'.PHP_EOL;
return $html;
}
$tree = disTree($all_cats);
// Display the tree
echo toUL($tree, 0, "");
?>
The only thing I changed was adding my own array ($all_cats).
我唯一改变的是添加我自己的数组 ($all_cats)。
回答by Amnon
There's another way to achieve the same effect which I find a bit easier to follow (without the reference trick). You build the tree by adding the relevant information to the current node and to its parent (assume the foreach iterates over the returned rows from the SQL query):
还有另一种方法可以实现相同的效果，我发现它更容易理解（没有参考技巧）。您可以通过将相关信息添加到当前节点及其父节点来构建树（假设 foreach 遍历 SQL 查询返回的行）：
$tree = array();
foreach ($query->result() as $row)
{
$pid = $row->parent_id;
$id = $row->id;
$name = $row->name;
// Create or add child information to the parent node
if (isset($tree[$pid]))
// a node for the parent exists
// add another child id to this parent
$tree[$pid]["children"][] = $id;
else
// create the first child to this parent
$tree[$pid] = array("children"=>array($id));
// Create or add name information for current node
if (isset($tree[$id]))
// a node for the id exists:
// set the name of current node
$tree[$id]["name"] = $name;
else
// create the current node and give it a name
$tree[$id] = array( "name"=>$name );
}
return $tree;
and to display the tree:
并显示树：
function toUL($tree, $id, $html){
$html .= '<ul>'.PHP_EOL;
if (isset($tree[$id]['name']))
$html .= '<li>' . $tree[$id]['name'];
if (isset($tree[$id]['children']))
{
$arChildren = &$tree[$id]['children'];
$len = count($arChildren);
for ($i=0; $i<$len; $i++) {
$html .= toUL($tree, $arChildren[$i], "");
}
$html .= '</li>'.PHP_EOL;
}
$html .= '</ul>'.PHP_EOL;
return $html;
}
// Display the tree
echo toUL($tree, 0, "");
