类别层次结构（PHP/MySQL）

Question

我正在尝试从MySQL数据库中按层次结构获取所有类别和子类别：

我的结果应该像这样（只是示例）：

1. Cat A
   • Sub-Cat 1
     • Sub_Sub_Cat 1
     • Sub_Sub_Cat 2
   • Sub_Cat 2
2. Cat B
3. Cat C
4. ...

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 ;

简单来说，如何使用PHP代码以分层方式获取它？

- kuzey beytar

Answer 1

使用邻接列表模型时，您可以一次生成结构。

来自One Pass Parent-Child Array Structure (Sep 2007; by 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;
    }
}

从链接的文章中，这里有一个代码片段用于创建输出列表。如果一个节点有子节点，它将递归调用自身以建立子树。

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;
}

相关问题：

• 如何从对象的数组记录集中获取嵌套的HTML列表？

- simshaun

---

+1 太棒了，第一部分正好满足了我的需求，而且比我想象的要简单得多。 - brenjt

我在使用已经动态创建的菜单时遇到了问题。我需要摆脱第一个生成的ul。 - HenryW

我通过引用开始和结束的UL并在其中关闭toUL来修复了它。 例如：$html .= '<ul>'.toUL($v['children']). '</ul>'; - HenryW

传递到toUL()方法的数组应该是什么？$refs还是$list还是$row？ - Dev

@Dev 你应该传递 $list。 - Rob Schmuecker

Answer 2

尝试下面的代码

//connect to mysql and select db

$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);
?>

More...

- Waruna Manjula

---

#Waruna Manjula，为您的解决方案加1！我已将其转换为下拉菜单，但我遇到了设置父类别为选定状态的问题。我知道如何设置当前类别为选定状态，但我需要显示父类别为选定状态。 - Europeuser

Answer 3

我有一个新的想法，我认为它会很好。 这个想法是： 在 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  |
+----+----------------------+-----------------+

如果您查看我的更新表，您会注意到每条记录都有其父级的链接，不仅是直接的父级，还包括所有父级。 为此，我对插入进行了一些修改：

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'))))

现在让我们制作您想要的查询：

1- 所有汽车的子类别：

select * from table_name where hierarchy like '1-%'

2- 如果你需要获取BLACK的所有父级元素，只需输入：

select * from table_name where hierarchy = '1-4-8-9' or hierarchy = '1-4-8' or hierarchy = '1-4' or hierarchy = '1'

(您可以通过PHP构建该查询，将层次结构字段在“-”字符处拆分)

3- 查看所有类别，包括级别和直接父级：

select *, SUBSTR(hierarchy, 1, (LENGTH(hierarchy) - LENGTH(id) - 1)) as parent, LENGTH(hierarchy) - LENGTH(REPLACE(hierarchy, '-', '')) as level From table_name

+----+----------------------+-----------------+-----------+--------+
| id | 分类名称             |    层级         |   父级    |  等级   |
+----+----------------------+-----------------+-----------+--------+
| 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   |
+----+----------------------+-----------------+-----------+--------+

这是一个新思路，需要一些改进。

- Wajih

---

如果您需要对层次结构进行更改，例如将“subcat1_2”转移到“subcat3_2”，或者删除一个类别，该怎么办？ - Akhil Gupta

Answer 4

@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, "");

?>

我唯一改变的是添加了自己的数组($all_cats)。

- Farhan

---

谢谢。你让它变得更加清晰、更容易定制以满足自己的需求，也更易于理解。谢谢！我花了几个小时来处理类似的代码，最终让它工作了。 - 今際のアリス

Answer 5

有另一种方法可以实现相同的效果，我发现这种方法更容易理解（不需要参考技巧）。您可以通过将相关信息添加到当前节点及其父节点来构建树形结构（假设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;

并显示树形结构：

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, "");

- Amnon