PHP PDO嵌套Foreach+SQL Join实现CMS菜单显示问题求助

问题原因 #

你当前的代码内层foreach会遍历所有链接，没有判断链接所属的菜单ID，导致每个菜单分组都显示全部导航链接。

解决方案一：对链接数据分组处理 #

保留原有两次查询，将链接数据按menu_id分组，循环菜单时只加载对应ID的链接：

修改PHP代码（处理链接分组）

$navbar = $systems->prepare('SELECT * FROM nav_menus WHERE menu_status = "Active" AND menu_users = ? ORDER BY menu_order ASC');
// 使用参数绑定避免SQL注入
$navbar->execute([$_SESSION['account_type']]);
$allmenus = $navbar->fetchAll();

$navlinks = $systems->prepare('SELECT l.* FROM nav_links as l INNER JOIN nav_menus as m ON m.id = l.menu_id WHERE m.menu_status = "Active" AND m.menu_users = ? ORDER BY l.link_order ASC');
$navlinks->execute([$_SESSION['account_type']]);
$menudata = $navlinks->fetchAll();

// 将链接按menu_id分组
$menuLinks = [];
foreach ($menudata as $link) {
    $menuLinks[$link['menu_id']][] = $link;
}

修改HTML循环代码

<nav class="sidebar active">
    <?php foreach ($allmenus as $navi): ?>
    <div class="sidebar-group">
        <span class="sidebar-header"><?=$navi['menu_title']?></span>
        <a href="dashboard.php"><i class="fa-sharp fa-thin fa-home"></i><span>Dashboard</span></a>
        <?php
        // 只加载当前菜单对应的链接，无链接则不显示
        $currentLinks = $menuLinks[$navi['id']] ?? [];
        foreach ($currentLinks as $menu): ?>
        <a href="<?=$menu['link_url']?>"><i class="fa-sharp fa-thin <?=$menu['link_icon']?>"></i><span><?=$menu['link_name']?></span></a>
        <?php endforeach; ?>
    </div>
    <?php endforeach; ?>
</nav>

解决方案二：单SQL查询+数据整理 #

通过一次关联查询获取菜单和对应链接，减少数据库请求，同时整理数据结构：

优化SQL查询与数据处理

// 一次查询获取菜单及对应链接
$stmt = $systems->prepare('
    SELECT m.id as menu_id, m.menu_title, l.link_url, l.link_icon, l.link_name, l.link_order
    FROM nav_menus m
    LEFT JOIN nav_links l ON m.id = l.menu_id
    WHERE m.menu_status = "Active" AND m.menu_users = ?
    ORDER BY m.menu_order ASC, l.link_order ASC
');
$stmt->execute([$_SESSION['account_type']]);
$results = $stmt->fetchAll();

// 整理为菜单=>链接的层级结构
$menuData = [];
foreach ($results as $row) {
    $menuId = $row['menu_id'];
    if (!isset($menuData[$menuId])) {
        $menuData[$menuId] = [
            'menu_title' => $row['menu_title'],
            'links' => []
        ];
    }
    // 仅当存在链接时添加
    if (!empty($row['link_url'])) {
        $menuData[$menuId]['links'][] = $row;
    }
}

循环渲染菜单

<nav class="sidebar active">
    <?php foreach ($menuData as $menu): ?>
    <div class="sidebar-group">
        <span class="sidebar-header"><?=$menu['menu_title']?></span>
        <a href="dashboard.php"><i class="fa-sharp fa-thin fa-home"></i><span>Dashboard</span></a>
        <?php foreach ($menu['links'] as $link): ?>
        <a href="<?=$link['link_url']?>"><i class="fa-sharp fa-thin <?=$link['link_icon']?>"></i><span><?=$link['link_name']?></span></a>
        <?php endforeach; ?>
    </div>
    <?php endforeach; ?>
</nav>

重要提醒 #

务必使用参数绑定（如代码中?占位符）替代直接拼接$_SESSION['account_type']，避免SQL注入风险。

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