php - 菜单和子菜单的Mysql连接表

Question

我一直在从事这个新元素，我需要有关这种情况的帮助。我有包含两个表的 sql 数据库:菜单和子菜单。

表的结构是:

Menu : id | title | order

Submenu : id | title | parentId

当我想在下面生成菜单项和子菜单项时，我使用两个简单的查询，有没有更简单的连接表的方法。基本上我想要这个:

1. 菜单项

   + Submenu Item 1
   + Submenu Item 2
   + Submenu Item 3
   

2. 菜单项

   + Submenu Item 1
   + Submenu Item 2
   + Submenu Item 3
   

Answer 1

假设数据库是这样的

CREATE TABLE menu
    (`id` int, `title` varchar(15), `order` int)
;

INSERT INTO menu
    (`id` , `title` , `order` )
VALUES
    (1, 'itstuff', 100),
    (2, 'book', 200),
    (3, 'toy', 300),
    (4, 'car', 400),
    (5, 'clothes', 500),
    (6, 'stuff', 600),
    (7, 'otherstuff', 700),
    (8, 'otherotherstuff', 900)
;


CREATE TABLE submenu
    (`id` int, `title` varchar(10), `order` int, `parentid` int)
;

INSERT INTO submenu
  (`id` , `title` , `order` , `parentid` )

VALUES
   (1, 'laptop', 100, 1),
    (2, 'mouse', 200, 1),
    (3, 'printer', 300, 1),
    (4, 'lcd screen', 400, 1),
    (5, 'volvo', 500, 4),
    (6, 'toyota', 600, 4),
    (7, 'bmw', 700, 4),
    (8, 'ford', 900, 4)
;

---

查询

SELECT
submenu.title
FROM
submenu
INNER JOIN menu ON submenu.parentid = menu.id
WHERE
menu.title = "itstuff"

此查询将返回属于菜单“itstuff”的所有子菜单

---

结果

[title]
laptop
mouse
printer
lcd screen

DEMO

用于创建菜单的 PHP 代码

<?php

// Create connection
$db = new mysqli('localhost', 'username', 'password', 'database_name');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
};


// Perform queries
$sql1 = 'select * from menu';
$result1 = $db->query($sql1);

echo "<ul>";
foreach ($result1 as $menu)
{
     echo "<li>".$menu["title"];
     echo "<ul>";
     $menu_title = $menu["title"];

    $sql2 = 'SELECT
    submenu.title
    FROM
    submenu
    INNER JOIN menu ON submenu.parentid = menu.id
    WHERE
    menu.title = "'.$menu_title.'"';    

    $result2 = $db->query($sql2);
    foreach ($result2 as $submenu)
    {
        echo "<li>".$submenu["title"]."</li>";
    }
     echo "</ul>";
     echo "</li>";
}
echo "</ul>";


$db->close();

?> 

结果应该是这样的

<ul><li>itstuff<ul><li>laptop</li><li>mouse</li><li>printer</li><li>lcd screen</li></ul></li><li>book<ul></ul></li><li>toy<ul></ul></li><li>car<ul><li>volvo</li><li>toyota</li><li>bmw</li><li>ford</li></ul></li><li>clothes<ul></ul></li><li>stuff<ul></ul></li><li>otherstuff<ul></ul></li><li>otherotherstuff<ul></ul></li></ul> 

希望对你有帮助