我需要通过连接三个mysql表来创建一个垂直菜单。
这是正常菜单应该喜欢的。
Main Cat Items 1 -> Categories 1 --> Sub Cat Items 1
Categories 1 --> Sub Cat Items 2
Categories 2 --> Sub Cat Items 3
Categories 3 --> Sub Cat Items 4
Main Cat Items 2 -> Categories 4 --> Sub Cat Items 5
这是我的三张桌子表：Main_cat
+-------------+---------------+
| main_cat_id | main_cat_name |
+-------------+---------------+
| 1 | main cat 1 |
| 2 | main cat 2 |
| 3 | main cat 3 |
+-------------+---------------+
表：类别
+--------+-------------+----------------+
| cat_id | main_cat_id | cat_name |
+--------+-------------+----------------+
| 1 | 1 | cat1 parent 1 |
| 4 | 1 | cat2 parent 1 |
| 5 | 1 | cat3 parent 1 |
| 6 | 2 | cat 4 parent 2 |
| 7 | 2 | cat 5 parent 2 |
| 8 | 3 | cat 6 parent 3 |
+--------+-------------+----------------+
表：Sub_cat
+------------+-------------+--------+----------------------+
| sub_cat_id | main_cat_id | cat_id | sub_cat_name |
+------------+-------------+--------+----------------------+
| 1 | 1 | 1 | sub 1 cat1 parent 1 |
| 2 | 1 | 1 | sub 2 cat1 parent 1 |
| 3 | 1 | 4 | sub 3 cat 4 parent 1 |
+------------+-------------+--------+----------------------+
每次我尝试，我得到的只是一个有一些字段的数组，但我期待的是一个麦芽维数组。这些是我尝试过的查询。
这是我通过以下查询得到的数组之一。
Array
(
[0] => stdClass Object
(
[main_cat_name] => main cat 1
[main_cat_id] => 1
[main_cat_url] =>
[cat_id] => 1
[cat_name] => cat1 parent 1
[cat_url] =>
[sub_cat_id] => 1
[sub_cat_name] => sub 1 cat1 parent 1
[sub_cat_url] =>
)
[1] => stdClass Object
(
[main_cat_name] => main cat 2
[main_cat_id] => 2
[main_cat_url] =>
[cat_id] =>
[cat_name] =>
[cat_url] =>
[sub_cat_id] =>
[sub_cat_name] =>
[sub_cat_url] =>
)
[2] => stdClass Object
(
[main_cat_name] => main cat 3
[main_cat_id] => 3
[main_cat_url] =>
[cat_id] =>
[cat_name] =>
[cat_url] =>
[sub_cat_id] =>
[sub_cat_name] =>
[sub_cat_url] =>
)
)
$query = "SELECT "
. "mc.main_cat_name, mc.main_cat_url, cg.cat_name, cg.cat_url, sc.sub_cat_name, sc.sub_cat_url "
. "FROM"
. " main_cat AS mc "
. "LEFT JOIN "
. "categories AS cg "
. "ON "
. "mc.main_cat_id = cg.main_cat_id "
. "LEFT JOIN "
. "sub_cat AS sc "
. "ON "
. "cg.cat_id = sc.sub_cat_id ";
$query = "SELECT "
. "mc.main_cat_name, mc.main_cat_id, mc.main_cat_url, cg.cat_id, cg.cat_name, cg.cat_url, sc.sub_cat_id, sc.sub_cat_name, sc.sub_cat_url "
. "FROM"
. " main_cat AS mc "
. "LEFT JOIN "
. "sub_cat AS sc "
. "ON "
. "sc.main_cat_id = mc.main_cat_id "
. "LEFT JOIN "
. "categories AS cg "
. "ON "
. "cg.cat_id = sc.cat_id "
. "GROUP BY "
. "main_cat_id";
$query = "SELECT "
. "* "
. "FROM "
. "main_cat mc "
. "LEFT JOIN sub_cat sc "
. "ON sc.main_cat_id = mc.main_cat_id "
. "LEFT JOIN categories cg "
. "ON "
. "cg.cat_id = sc.cat_id "
. "Group BY "
. "sc.cat_id, sc.main_cat_id";
但这些查询中没有一个能满足我的期望。你能帮我做这件事吗
最接近的查询是：
select mc.main_cat_name, c.cat_name, sc.sub_cat_name
from main_cat mc left join
categories c
on c.main_cat_id = mc.main_cat_id left join
sub_cat sc
on sc.cat_id = c.cat_id
order by mc.main_cat_id, c.cat_id, sc.sub_cat_id;
然而，这将为您提供填充每个单元格的"关系"结果
Main Cat Items 1 -> Categories 1 --> Sub Cat Items 1
Main Cat Items 1 -> Categories 1 --> Sub Cat Items 2
Main Cat Items 1 -> Categories 2 --> Sub Cat Items 3
Main Cat Items 1 -> Categories 3 --> Sub Cat Items 4
Main Cat Items 2 -> Categories 4 --> Sub Cat Items 5
如果您想删除重复的名称以进行演示，那么您应该在应用程序中这样做。可以在数据库中进行这种转换。然而，SQL查询的结果集通常是表，这意味着排序并不重要。在这种情况下，排序是至关重要的，因为否则您将不知道第二行、第三行和第四行的正确主类别。