在同一个表中加入FOREACH和内爆获取菜单

Question

表： categories

╔═════════════╦═══════════════════╦═════════════════╗
║ id_category ║ category_name     ║ category_father ║
╠═════════════╬═══════════════════╬═════════════════╣
║  1          ║ Home              ║   0             ║
║  2          ║ Category_1        ║   1             ║
║  3          ║ Category_2        ║   1             ║
║  4          ║ Category_3        ║   1             ║
║  5          ║ Category_4        ║   1             ║
║  6          ║ Category_5        ║   1             ║
║  7          ║ Sub_Category_1    ║   2             ║
║  8          ║ Sub_Category_2    ║   2             ║
║  9          ║ Sub_Category_3    ║   2             ║
║  10         ║ Sub_Category_4    ║   2             ║
║  11         ║ Sub_Category_5    ║   3             ║
║  12         ║ Sub_Category_6    ║   3             ║
║  13         ║ Sub_Category_7    ║   3             ║
║  14         ║ Sub_Category_8    ║   3             ║
║  15         ║ Sub_Category_9    ║   3             ║
╚═════════════╩═══════════════════╩═════════════════╝

我正在使用meekro.class查询我的数据库，现在我正在构建我的应用程序的菜单，但是我需要对同一个table执行JOIN，以显示category_name而不是category_father的id，而我不能。

我还需要以列表形式显示主要类别和子类别以下。我打算只使用一个级别的sub categories，所以这个表结构应该可以工作。

这是我的查询：

$querycategories = DB::query("SELECT * FROM categories");
foreach ($querycategories as $row) {
    echo "<b>" . $row['category_name'] . "</b>";
    echo "<ul>";
        echo "<li>" . $row['category_father'] . "</li>";
    echo "</ul>";
}

当然，通过这次咨询，我遇到了灾难，这就是为什么我来找你寻求帮助的原因！

，这是我希望得到的：

Category_1

• Sub_Category_1
• Sub_Category_2
• Sub_Category_3
• Sub_Category_4

Category_2

• Sub_Category_5
• Sub_Category_6
• Sub_Category_7
• Sub_Category_8
• Sub_Category_9

Category_3

Category_4

Category_5

我希望你能帮我，写这个问题花了将近一个小时，请用你一天中的5分钟来帮助我，谢谢！

Answer 1

LEFT JOIN将提供构建无序列表所需的空值。

我说使用这个：(http://sqlfiddle.com/#!9/6e7fe/3)

SELECT A.category_name AS parent, B.category_name AS child
FROM categories A 
LEFT JOIN categories B on A.id_category=B.category_father
WHERE A.category_father=1
ORDER BY parent

输出：

╔════════════╦════════════════╗
║   parent   ║     child      ║
╠════════════╬════════════════╣
║ Category_1 ║ Sub_Category_1 ║
║ Category_1 ║ Sub_Category_2 ║
║ Category_1 ║ Sub_Category_3 ║
║ Category_1 ║ Sub_Category_4 ║
║ Category_2 ║ Sub_Category_5 ║
║ Category_2 ║ Sub_Category_6 ║
║ Category_2 ║ Sub_Category_7 ║
║ Category_2 ║ Sub_Category_8 ║
║ Category_2 ║ Sub_Category_9 ║
║ Category_3 ║ (null)         ║
║ Category_4 ║ (null)         ║
║ Category_5 ║ (null)         ║
╚════════════╩════════════════╝

PHP：(演示)

$querycategories=[
    ['parent'=>'Category_1','child'=>'Sub_Category_1'],
    ['parent'=>'Category_1','child'=>'Sub_Category_2'],
    ['parent'=>'Category_1','child'=>'Sub_Category_3'],
    ['parent'=>'Category_1','child'=>'Sub_Category_4'],
    ['parent'=>'Category_2','child'=>'Sub_Category_5'],
    ['parent'=>'Category_2','child'=>'Sub_Category_6'],
    ['parent'=>'Category_2','child'=>'Sub_Category_7'],
    ['parent'=>'Category_2','child'=>'Sub_Category_8'],
    ['parent'=>'Category_2','child'=>'Sub_Category_9'],
    ['parent'=>'Category_3','child'=>null],
    ['parent'=>'Category_4','child'=>null],
    ['parent'=>'Category_5','child'=>null]
];

$cat=null;
foreach ($querycategories as $row){
    if($cat!==$row['parent']){
        echo "<b>{$row['parent']}</b>";
    }
    if(!is_null($row['child'])){
        echo "<ul><li>{$row['child']}</li></ul>";
    }
    $cat=$row['parent'];
}

输出：

<b>Category_1</b>
<ul>
  <li>Sub_Category_1</li>
</ul>
<ul>
  <li>Sub_Category_2</li>
</ul>
<ul>
  <li>Sub_Category_3</li>
</ul>
<ul>
  <li>Sub_Category_4</li>
</ul>

<b>Category_2</b>
<ul>
  <li>Sub_Category_5</li>
</ul>
<ul>
  <li>Sub_Category_6</li>
</ul>
<ul>
  <li>Sub_Category_7</li>
</ul>
<ul>
  <li>Sub_Category_8</li>
</ul>
<ul>
  <li>Sub_Category_9</li>
</ul>

<b>Category_3</b>

<b>Category_4</b>

<b>Category_5</b>

Answer 2

SQL Fiddle

MySQL 5.6架构设置

CREATE TABLE categories
    (`id_category` int, `category_name` varchar(14), `category_father` int)
;

INSERT INTO categories
    (`id_category`, `category_name`, `category_father`)
VALUES
    (1, 'Home', 0),
    (2, 'Category_1', 1),
    (3, 'Category_2', 1),
    (4, 'Category_3', 1),
    (5, 'Category_4', 1),
    (6, 'Category_5', 1),
    (7, 'Sub_Category_1', 2),
    (8, 'Sub_Category_2', 2),
    (9, 'Sub_Category_3', 2),
    (10, 'Sub_Category_4', 2),
    (11, 'Sub_Category_5', 3),
    (12, 'Sub_Category_6', 3),
    (13, 'Sub_Category_7', 3),
    (14, 'Sub_Category_8', 3),
    (15, 'Sub_Category_9', 3)
;

查询1

SELECT 
    c1.category_name as parent_name
  , c2.category_name as child_name
  , c1.id_category as parent_id
  , c2.id_category as child_id
FROM categories c1 
inner join categories c2 on c1.id_category = c2.category_father
order by parent_id, child_name

结果

| parent_name |     child_name | parent_id | child_id |
|-------------|----------------|-----------|----------|
|        Home |     Category_1 |         1 |        2 |
|        Home |     Category_2 |         1 |        3 |
|        Home |     Category_3 |         1 |        4 |
|        Home |     Category_4 |         1 |        5 |
|        Home |     Category_5 |         1 |        6 |
|  Category_1 | Sub_Category_1 |         2 |        7 |
|  Category_1 | Sub_Category_2 |         2 |        8 |
|  Category_1 | Sub_Category_3 |         2 |        9 |
|  Category_1 | Sub_Category_4 |         2 |       10 |
|  Category_2 | Sub_Category_5 |         3 |       11 |
|  Category_2 | Sub_Category_6 |         3 |       12 |
|  Category_2 | Sub_Category_7 |         3 |       13 |
|  Category_2 | Sub_Category_8 |         3 |       14 |
|  Category_2 | Sub_Category_9 |         3 |       15 |