使用PHP从MySql表创建一个子菜单

Question

我有以下数据结构：

|--------|----------|-----------|------------|-----------------|
| NAV_Id | NAV_Name | NAV_Order | NAV_Parent | NAV_ParentOrder |
|--------|----------|-----------|------------|-----------------|
|      1 |     Home |         1 |         no |               0 |
|      2 |   Fruits |         2 |        yes |               0 |
|      3 |   Apples |         2 |         no |               1 |
|      4 |    Pears |         2 |         no |               2 |
|--------|----------|-----------|------------|-----------------|

• NAV_Order是我菜单中的导航位置
• NAV_Parent表示项目是否具有子菜单
• NAV_ParentOrder是该项目在子菜单中的位置顺序

我想要的输出如下：

<ol class="dd-list">
<li class="dd-item" data-id="1">
<div class="dd-handle">Home</div>
</li>
<li class="dd-item" data-id="2">
<div class="dd-handle">Fruits</div>
<ol class="dd-list">
<li class="dd-item" data-id="3"><div class="dd-handle">Apples</div></li>
<li class="dd-item" data-id="4"><div class="dd-handle">Pears</div></li>
</ol>
</li>
</ol>

如何，从我的数据库结构，我可以反映这个输出？

以下是我实际尝试的内容：

$query = $pdo->prepare('SELECT * FROM ___Navigation');
$query->execute();
while ($fetch = $query->fetch(PDO::FETCH_ASSOC)) {
if(NAV_Parent == 0) {
echo '<li class="dd-item" data-id="'.$fetch[NAV_Id].'"><div class="dd-handle">'.$fetch[NAV_Name].'</div></li>';
}
else {
echo '<li class="dd-item" data-id="'.$fetch[NAV_Id].'"><div class="dd-handle">'.$fetch[NAV_Name].'</div></li>';
foreach($fetch[NAV_ParentOrder]) {
echo '<li class="dd-item" data-id="'.$fetch[NAV_Id].'"><div class="dd-handle">'.$fetch[NAV_Name].'</div></li>';
}
}
}

但我完全迷失了。。。

Answer 1

怎么样：

$query = $pdo->prepare('SELECT * FROM ___Navigation WHERE NAV_ParentOrder=0 ORDER BY NAV_Order');
$query->execute();
while ($fetch = $query->fetch(PDO::FETCH_ASSOC)) {
//your parent menu
print '<li class="dd-item" data-id="' . $fetch[NAV_Id] . '"><div class="dd-handle">' . $fetch[NAV_Name] . '</div></li>';
$query_child = $pdo->prepare('SELECT * FROM ___Navigation WHERE NAV_ParentOrder !=0 AND NAV_ParentOrder=' . $fetch[NAV_Id] . 'ORDER BY NAV_Order');
$query_child->execute();
//child menu
while ($fetch_child = $query_child->fetch(PDO::FETCH_ASSOC)) {
print '<ol class="dd-list">';
print '<li class="dd-item" data-id="' . $fetch_child[NAV_Id] . '"><div class="dd-handle">' . $fetch_child[NAV_Name] . '</div></li>';
}
}