检索分层数据 - Retrieve hierarchical data

Retrieve hierarchical data

本文关键字：数据分层检索 | 更新日期: 2023-09-27

我正在尝试为 CMS 系统创建一个动态菜单这是我的SQL表，它有一个额外的字段，称为内容

parent_id  order     id  titel                  
0          1         2   Home                   
0          2         4   Contact                
0          3         9   stuff         
2          1         1   more stuff
2          2         3   way more stuff             
9          1         10  all stuff

我想要这个结果：

home
 more stuff
 way more stuff
contact
stuff
 all stuff

这就是我如何称呼菜单：

        $resultPaginas = mysqli_query($mysqli, "select id, titel, parent_id, volgorde from TABLE order by order");
    if (!$resultPaginas) {
        die(mysqli_error($mysqli));
    }

这将显示按parent_id顺序排序的表。

一切都完成了，我知道我需要做一个 while 循环，但是我如何执行正确的 while 循环以获得我想要的结果？

附言这将是一个菜单

使用此递归函数构建菜单：

function buildMenu($parentId = 0) {
    $menu = array();
    $result = mysqli_query($mysqli, "SELECT `id`, `titel`, `parent_id`, `volgorde` FROM [TABLE] WHERE `parent_id` = $parentId ORDER BY `order`");
    if ($result !== false) {
        while ($page = $result->fetch_assoc()) {
            $page["children"] = buildMenu($page["id"]);
            $menu[] = $page;
        }
    }
    return $menu;
}

像这样调用递归函数

$resultPaginas = buildMenu();

$resultPaginas的示例内容：

Array
(
    [0] => Array
        (
            [id] => 1
            [parent_id] => 0
            [titel] => home
            [children] => Array
                (
                    [0] => Array
                        (
                            [id] => 2
                            [parent_id] => 1
                            [titel] => more stuff
                        )
                    [1] => Array
                        (
                            [id] => 3
                            [parent_id] => 1
                            [titel] => way more stuff
                        )
                )
        )
    [1] => Array
        (
            [id] => 4
            [parent_id] => 0
            [titel] => contact
        )
)

首先尝试从父 id 为 0 的表中全部选择

并循环播放

样本：

foreach($parent as $par){
//Then you need to write another query to take all submenus for current parentid menu
  $submenus= mysqli_query($mysqli, "select id, titel, parent_id, volgorde from TABLE where parent_id = $par.id order by order");
//Then again loop trough 
foreach($submenus as $sub){
// and add existing submenus to parent menu
}
}

注意：如果语法不正确，这只是概念抱歉。