PHP开发者求助：修复Android API菜单分类查询，实现三级层级展示

我是一名PHP开发者，正在为Android开发API。当前SQL查询只能显示分类的左连接内容，无法完整展示数据，需要获取menu -> tbl_category -> tbl_recipes的三级层级数据。

数据库表数据 #

menu表数据 #

INSERT INTO `menu` (`mid`, `menu_Name`, `menu_icon`) VALUES
(15, 'v', '9673-2022-08-24.png'),
(21, 'dgh', '9088-2022-08-24.jpg'),
(22, 'fg', '5378-2022-08-24.png'),
(24, 'ft', '7245-2022-08-24.png');

tbl_category表数据 #

INSERT INTO `tbl_category` (`cid`, `category_name`, `category_image`, `menu_id`) VALUES
(31, 'fgj', '2048-2022-08-24.jpg', 15),
(32, 'fgh', '0644-2022-08-24.png', 16);

tbl_recipes表数据 #

INSERT INTO `tbl_recipes` (`recipe_id`, `cat_id`, `recipe_title`, `menu_id`, `recipe_description`, `recipe_image`, `video_url`, `video_id`, `content_type`, `size`, `featured`, `tags`, `total_views`, `last_update`) VALUES
(47, 0, 'dsg', 0, '<p>sg</p>\r\n', '1661254132_Screenshot (6).png', '', 'cda11up', 'Post', '', 0, '0', 0, '2022-08-23 11:28:52'),
(48, 14, 'sad', 0, '<p>asdasd</p>\r\n', '1661254498_Ent-card-1.jpg', '', 'cda11up', 'Post', '', 0, '0', 0, '2022-08-23 18:57:49'),
(49, 25, 'fgh', 13, '<p>fghfgh</p>\r\n', '1661280418_Screenshot (5).png', '', 'cda11up', 'Post', '', 0, '0', 0, '2022-08-23 18:57:44'),
(50, 25, 'try', 13, '<p>tyt</p>\r\n', '1661281058_Screenshot (5).png', '', 'cda11up', 'Post', '', 1, '0', 0, '2022-08-23 18:57:54');

现有查询语句 #

$query_category = "SELECT DISTINCT m.mid, m.menu_Name, m.menu_icon, c.cid, c.category_name, c.category_image, COUNT(DISTINCT r.recipe_id) as recipes_count FROM tbl_recipes r LEFT JOIN tbl_category c ON r.cat_id = c.cid left join menu m on m.mid=r.menu_id GROUP BY c.cid ORDER BY c.cid DESC LIMIT $limit";

期望数据结构 #

menu
   tbl_category
           tbl_recipes

解决方案 #

要构建三级嵌套结构，建议分两步处理：先通过SQL获取基础层级关联数据，再在PHP端组装完整嵌套结构。

1. 调整SQL查询（获取菜单+分类+菜谱数量）

从menu表开始关联，确保层级关系正确，同时保留无分类/无菜谱的菜单：

SELECT 
    m.mid, m.menu_Name, m.menu_icon,
    c.cid, c.category_name, c.category_image,
    COUNT(r.recipe_id) as recipes_count
FROM menu m
LEFT JOIN tbl_category c ON m.mid = c.menu_id
LEFT JOIN tbl_recipes r ON c.cid = r.cat_id
GROUP BY m.mid, c.cid
ORDER BY m.mid DESC, c.cid DESC
LIMIT $limit

2. PHP端组装嵌套数据

通过查询结果构建菜单-分类-菜谱的三级结构：

// 假设已建立数据库连接$conn
$result = mysqli_query($conn, $query_category);
$menuData = [];

// 先构建菜单和分类的基础层级
while ($row = mysqli_fetch_assoc($result)) {
    $menuId = $row['mid'];
    $catId = $row['cid'];
    
    if (!isset($menuData[$menuId])) {
        $menuData[$menuId] = [
            'mid' => $row['mid'],
            'menu_Name' => $row['menu_Name'],
            'menu_icon' => $row['menu_icon'],
            'categories' => []
        ];
    }
    
    if (!is_null($catId)) {
        $menuData[$menuId]['categories'][$catId] = [
            'cid' => $row['cid'],
            'category_name' => $row['category_name'],
            'category_image' => $row['category_image'],
            'recipes_count' => $row['recipes_count'],
            'recipes' => []
        ];
    }
}

// 查询所有菜谱并匹配到对应分类
$recipesQuery = "SELECT * FROM tbl_recipes";
$recipesResult = mysqli_query($conn, $recipesQuery);

while ($recipe = mysqli_fetch_assoc($recipesResult)) {
    $menuId = $recipe['menu_id'];
    $catId = $recipe['cat_id'];
    
    if (isset($menuData[$menuId]['categories'][$catId])) {
        $menuData[$menuId]['categories'][$catId]['recipes'][] = $recipe;
    }
}

// 转换为索引数组，方便API返回
$menuData = array_values($menuData);
echo json_encode($menuData);

说明

• SQL从menu出发做左连接，确保所有菜单都能被返回，包括无分类的菜单。
• 分两次查询避免SQL返回大量重复数据，提升查询效率。
• 如果不需要菜谱详情，仅需数量，直接使用第一个SQL的结果即可。

内容的提问来源于stack exchange，提问作者atozcodes