我在一个数据库中有3个表，我想加入它们，以构建一个3级嵌套列表（将用作菜单的一部分）。到目前为止，我已经学会了如何从下表中选择数据。我想要一些关于生成php部分中的第三级菜单的帮助
SQL表：
page_list:pageID（primary_key）page_name pageURL
level2_menu：GeneralID（primary_key）genera_name pageID（foreign_key）
level3_menu:deepID（primary_key）generalID（foreign_key）deep_title
使用：
<?php
get_menu("SELECT page_list.pageID as pageID, page_list.page_name as page_name, page_list.pageURL as pageURL, level2_menu.GeneralID as GeneralID, level2_menu.gener_name as general_name, level3_menu.deepID as deepID, level3_menu.deep_title as deep_title
FROM page_list
LEFT OUTER JOIN level2_menu ON page_list.pageID = level2_menu.pageID
LEFT OUTER JOIN level3_menu ON level2_menu.GeneralID = level3_menu.generalID
UNION
SELECT page_list.pageID as pageID, page_list.page_name as page_name, page_list.pageURL as pageURL, level2_menu.GeneralID as GeneralID, level2_menu.gener_name as general_name, level3_menu.deepID as deepID, level3_menu.deep_title as deep_title
FROM page_list
RIGHT OUTER JOIN level2_menu ON page_list.pageID = level2_menu.pageID
RIGHT OUTER JOIN level3_menu ON level2_menu.GeneralID = level3_menu.generalID
ORDER BY pageID desc
");
?>
这将给出以下sql结果：http://sqlfiddle.com/#！9/927a0
+--------------------+------------+----------+-----------+---------------+--------+------------+
| pageID Ascending 1 | page_name | pageURL | GeneralID | general_name | deepID | deep_title |
+--------------------+------------+----------+-----------+---------------+--------+------------+
| 7 | get quote | 0 | NULL | NULL | NULL | NULL |
| 4 | tjanster | info.php | 9 | Title | NULL | NULL |
| 3 | products | info.php | 5 | Title 2 | 8 | Subtitle |
+--------------------+------------+----------+-----------+---------------+--------+------------+
以及获取级别1列表和级别2列表的数据，使用：
function get_menu($sql)
{
include 'connect.php';
$result = $conn->query($sql);
if ($result->num_rows > 0)
;
$current_album = "";
echo "<div id='menu'><div class='menu_pos'><ul class='main-navigation'>'n";
while ($row = $result->fetch_assoc()) {
if ($current_album <> "" && $current_album <> $row['pageID'])
echo "</ul></li>";
if ($current_album <> $row['pageID']) {
echo "<li id='qoute'><a href='#'>" . $row['page_name'] . "</a><ul>'n";
$current_album = $row['pageID'];
}
if ($row['GeneralID'] == 'NULL') {
echo "bob";
} else {
echo "<li><a href='#'>" . $row['general_name'] . "</a></li>'n";
}
}
echo "</ul></div></div>";
}
这导致这里显示的第一个菜单示例是一个2级列表，但我想要3级列表，就像jsfiddle中显示的第二个菜单示例一样。
首先，这是一种艰难的做事方式。原始SQL/Raw HTML/Raw PHP。模板化（例如trick）、DB助手（例如Doctrine）可以制造魔法。
但为了回答您的问题，这可能会起作用：
第一：我建议修改SQL
SELECT
page_list.pageID as pageID, page_list.page_name as page_name, page_list.pageURL as pageURL,
level2_menu.GeneralID as GeneralID, level2_menu.gener_name as general_name,
level3_menu.deepID as deepID, level3_menu.deep_title as deep_title
FROM page_list
LEFT OUTER JOIN level2_menu ON page_list.pageID = level2_menu.pageID
LEFT OUTER JOIN level3_menu ON level2_menu.GeneralID = level3_menu.generalID
ORDER BY pageID DESC, GeneralID DESC, deepID DESC
第二：准备菜单和输出菜单的分离。
function get_menu($sql) {
// the final output is in $output
// @var string $output
$output = "";
include 'connect.php';
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// constructing the structure of menu
$current_album = null;
$current_general_id = null;
$menu = [];
while ($row = $result->fetch_assoc()) {
// checking if top level menu is already added
if (!array_key_exists($row['pageID'], $menu)) {
$menu[$row['pageID']] = [
'id' => $row['pageID'],
'name' => $row['page_name'],
'url' => $row['pageURL'],
'children' => []
];
}
$currentTopMenu = $menu[$row['pageID']];
// checking if second level is defined and already exists
if (!array_key_exists($row['GeneralID'], $currentTopMenu['children'])) {
$sLevel = [
'id' => $row['GeneralID'],
'name' => $row['general_name'],
'url' => '',
'children' => []
];
$menu[$row['pageID']]['children'][$row['GeneralID']] = $sLevel;
}
$currentSecondMenu = $menu[$row['pageID']]['children'][$row['GeneralID']];
// last level of menu
if ($row['deepID']) {
$menu[$row['pageID']]['children'][$row['GeneralID']]['children'][$row['deepID']] = [
'id' => $row['deepID'],
'name' => $row['deep_title'],
'url' => '',
'children' => []
];
}
}
// making an output -> should be moved to the template
$output .= "<div id='menu'><div class='menu_pos'><ul class='main-navigation'>'n";
foreach ($menu as $menuTopItem) {
$output .= "<li class='first'><a href='" . $menuTopItem['url'] . "'>" . $menuTopItem['name'] . "</a><ul>'n";
foreach ($menuTopItem['children'] as $secondMenuItem) {
$output .= "<li class='second'><a href='" . $secondMenuItem['url'] . "'>" . $secondMenuItem['name'] . "</a><ul>'n";
foreach ($secondMenuItem['children'] as $thirdMenuItem) {
$output .= "<li class='third'><a href='" . $thirdMenuItem['url'] . "'>" . $thirdMenuItem['name'] . "</a></li>'n";
}
$output .= "</ul></li>";
}
$output .= "</ul></li>";
}
$output .= "</ul></div></div>";
}
echo $output;
// or
return $output;
}