如何用MySQL实现多级菜单的正确方式？

Question

我正在尝试做一个菜单，这是一个主菜单，去2层深度(子菜单和子菜单)。

我以前用GROUP_CONCAT做过这件事，但只有1级深度。事实上，这是两个层次的深度，这实际上是一个循环的最佳实践。

CREATE TABLE `database`.`main_menu` ( `main_menu_id` INT(11) NOT NULL AUTO_INCREMENT , `main_menu_name` VARCHAR(255) NOT NULL , PRIMARY KEY (`main_menu_id`)) ENGINE = InnoDB;

CREATE TABLE `database`.`sub_menu` ( `main_menu_id` INT(11) NOT NULL , `sub_menu_id` INT(11) NOT NULL AUTO_INCREMENT , `sub_menu_name` VARCHAR(255) NOT NULL , PRIMARY KEY (`sub_menu_id`)) ENGINE = InnoDB;

CREATE TABLE `database`.`sub_sub_menu` ( `main_menu_id` INT(11) NOT NULL , `sub_menu_id` INT(11) NOT NULL , `sub_sub_menu_id` INT(11) NOT NULL AUTO_INCREMENT , `sub_sub_menu_name` VARCHAR(255) NOT NULL , PRIMARY KEY (`sub_sub_menu_id`)) ENGINE = InnoDB;


INSERT INTO `main_menu` (`main_menu_id`, `main_menu_name`) VALUES (NULL, 'Food'), (NULL, 'Treats')

INSERT INTO `sub_menu` (`main_menu_id`, `sub_menu_id`, `sub_menu_name`) VALUES ('1', NULL, 'Duck'), ('1', NULL, 'Chicken')
INSERT INTO `sub_menu` (`main_menu_id`, `sub_menu_id`, `sub_menu_name`) VALUES ('2', NULL, 'Bacon Bits'), ('2', NULL, 'Dental')

INSERT INTO `sub_sub_menu` (`main_menu_id`, `sub_menu_id`, `sub_sub_menu_id`, `sub_sub_menu_name`) VALUES ('1', '1', NULL, 'In Gravy'), ('1', '2', NULL, 'in Soup')

INSERT INTO `sub_sub_menu` (`main_menu_id`, `sub_menu_id`, `sub_sub_menu_id`, `sub_sub_menu_name`) VALUES ('2', '3', NULL, 'Sticks'), ('2', '4', NULL, 'Chunks')

MySQL查询：

SELECT ssm.sub_sub_menu_name  as    sub_sub_menu_name ,
sm.sub_menu_name  as    sub_menu_name ,
mm.main_menu_name  as   main_menu_name ,

ssm.sub_sub_menu_id  as     sub_sub_menu_id ,
sm.sub_menu_id  as      sub_menu_id ,
mm.main_menu_id  as     main_menu_id 

FROM        main_sub_sub_menu  as ssm
LEFT JOIN main_menu as mm
    ON mm.main_menu_id  = ssm.main_menu_id 
LEFT JOIN sub_menu as sm
    ON sm.sub_menu_id   = ssm.sub_menu_id  

ORDER BY ssm.sub_sub_menu_id, sm.sub_menu_id, ssm.main_menu_id

关于如何将信息划分为类别的PHP：

<?php $cat = $getData->get_menu_categories(); //SQL Query above with PDO
$i = 1;
$j = 1;
for($a=0; $a<= count($cat); $a++){
        if($i == $cat[$a]['main_menu_id']){
            for($j=1; $j < count($cat[$a]['sub_menu_id']); $j++){ // Was getting stuck here because I can't count it unless it is an array
                 //Would show each submenu name, then I would make another for loop for the sub sub menu 
            }
        }else{
            $i++;
        }

产出将

主菜单

子菜单

子菜单

我正在制作列表(一个<ul><li>风格的html菜单列表)，基本上是一棵树。

Answer 1

您将希望循环查询的结果，并打印如下所示。我使用虚线缩进，但您可以使用html来更干净地进行缩进。我已经为任何想帮忙的人做了个数据库小提琴。https://www.db-fiddle.com/f/sbTyQbNemcEM5vX3xwuUaJ/0#&togetherjs=wkJd3lwafs

<?php 
$cat = $getData->get_menu_categories(); 
$lastmain = '';
$lastsub = '';
$lastsubsub = '';

foreach ($cat as $line) {
   $main = $line['main_menu_name'];
   $sub = $line['sub_menu_name'];
   $subsub = $line['sub_sub_menu_name'];

   if ($lastmain <> $main) {
       print "$main\n";
       $lastmain = $main;
   }

   if ($lastsub <> $sub) {
       print "---$sub\n";
       $lastsub = $sub;
   }

   if ($lastsubsub <> $subsub) {
       print "------$subsub\n";
       $lastsubsub = $subsub;
   }
}

Answer 2

<ul>
    <li>
        <ul class="nav__list">

<?php
// $cat = $getData->get_menu_categories(); // put your SQL request to the database here
$lastmain = '';
$lastsub = '';
$lastsubsub = '';
$mainx=1;
$mainy=0;
$subx=0;
$suby=0;
$subsubx=0;
$subsuby=0;
$startmainx = 0;
?>
<nav class="nav" role="navigation">
  <ul class="nav__list">
<?php
foreach ($cat as $line) {
   $main = $line['main_menu_name'];
   $sub = $line['sub_menu_name'];
   $subsub = $line['sub_sub_menu_name'];

   $mainid = $line['main_menu_id'];
   $subid = $line['sub_menu_id'];
   $subsubid = $line['sub_sub_menu_id'];

   if ($lastmain <> $main) {
        if($mainx <> $mainy){
            if($startmainx == 0){
                $mainx = $mainy;
                $startmainx =1;
            }else{
                print '</ul></li></ul></li>';//beginning
                $mainx = $mainy;
            }
        }
       echo '<li>
       <input id="group-'.$subid.'" type="checkbox" hidden />
       <label for="group-'.$subid.'">'.$main.'&nbsp; <span class="fa fa-angle-right"></span></label>';
        if(($mainx == $mainy) && ($lastmain == '')){
            echo '<ul class="group-list">';
            $mainx++;
        }
       $lastmain = $main;
        if(($mainx == $mainy) && ($lastmain == $main)){
            echo '<ul class="group-list"><li>'; //end
            $lastsub = '';
            $mainx++;
        }
   }//end 1st menu 
   if ($lastsub <> $sub) {
    if(($subx <> $suby) && ($lastsub == '')){
        $suby++;
    }
    if($subx <> $suby){
        print "</ul>";
        $suby++;
    }
       echo '<input id="sub-group-'.$subsubid.'" type="checkbox" hidden />
       <label for="sub-group-'.$subsubid.'">'.$sub.'&nbsp; <span class="fa fa-angle-right"></span></label>';
        $lastsub = $sub;
        if($subx == $suby){
                echo '<ul class="sub-group-list">';
            $subx++;
        }
   }//End 2nd menu
   if ($lastsubsub <> $subsub) {
        if($subsubx <> $subsuby){
            $subsubx =$subsuby;
        }
       echo '<li><a href="'.$subsubid.'">'.$subsub.'</a></li>';
        $lastsubsub = $subsub;
        if($subsubx == $subsuby){
           $subsubx = $subsuby;
        }
   }
}
?>

我想向大家展示我是如何根据mankowitz的答案添加html的。