php - 在无序列表中显示类别

Question

我在无序列表中对数据库中的类别进行排序和显示时遇到了问题。

我编写了这段代码来完成任务:

$roots = $GLOBALS['sql']->query("SELECT `id`,`name` FROM `category` WHERE `parent`='0'");
$out = '<ul>';
foreach($roots as $root){
    $out .= '<li>'.$root['name'].'<ul>';
    $downs = $GLOBALS['sql']->query("SELECT `id`,`name` FROM `category` WHERE `parent`='".$root['id']."'");
    foreach($downs as $down){
        $out .= '<li>'.$down['name'].'<ul>';
        $bots = $GLOBALS['sql']->query("SELECT `name` FROM `category` WHERE `parent`='".$down['id']."'");
        foreach($bots as $bot){
            $out .= '<li>'.$bot['name'].'</li>';
        }
        $out .= '</ul></li>';
    }
    $out .= '</ul></li>';
}
$out .= '</ul>';
return str_replace('<ul></ul>', '', $out);

如果有很多类别，这似乎有很长的运行时间。有更有效的方法吗？或者一般来说，php 数组处理比 MySQL 查询更快吗？

编辑:我尝试连接这些表，通过这个查询我得到了正确的表。

SELECT 
       root.name  AS root_name, 
       down1.name AS down1_name, 
       down2.name AS down2_name 
FROM
            category AS root
  LEFT JOIN category AS down1 ON down1.parent = root.id 
  LEFT JOIN category AS down2 ON down2.parent = down1.id
WHERE 
      root.parent = '0' 
ORDER BY 
      root_name, down1_name, down2_name

但是我如何在 php 中处理这个表以获得无序列表呢？这更快吗？

Answer 1

尝试这样的事情:

$rows = $GLOBALS['sql']->query("
    SELECT
        root.name AS root_name,
        down1.name AS down1_name,
        down2.name AS down2_name
    FROM category AS root
    LEFT JOIN category AS down1 ON down1.parent = root.id
    LEFT JOIN category AS down2 ON down2.parent = down1.id
    WHERE root.parent = '0'
    ORDER BY root_name, down1_name, down2_name
");

$tree = array();
foreach ($rows as $row) {
    $tree[$row['root_name']][$row['down1_name'] = $row['down2_name'];
}

$out = '';
foreach ($tree as $rootName => $root) {
    $out .= "<li>{$rootName}<ul>";  
    foreach ($root as $down1Name => $down1) {
        $out .= "<li>{$down1Name}<ul>";
        foreach ($down1 as $down2Name) {
            $out .= "<li>{$down2Name}</li>";
        }   
        $out .= "</ul></li>";
    }
    $out .= "</ul></li>";
}

在第一个循环(foreach ($rows as $row))中，您构建一个树形结构数组。在第二个嵌套循环中，您构建 HTML 结构。