PHP从多MySQL表拉取数据实现4级分类层级树展示

你现有代码无法正确展示subgenus层级，核心是两个问题：

• 查询结果未按subgenus维度排序聚合，所有species被平级拉取
• 循环输出时没有追踪当前所属的subgenus，遇到subgenus变更时没有插入对应层级的标题，同时存在变量覆盖问题：内层循环重复赋值$genus_name会覆盖外层的属名变量

1. 优化查询逻辑 #

• 不要逐属循环拼接字符串查物种，改用预处理语句绑定参数查询，避免SQL注入风险
• 查物种时增加排序规则：ORDER BY s.subgenus_name IS NULL DESC, s.subgenus_name, s.species_name，该规则会把无亚属归属（亚字段为空）的物种排在最前，同亚属的物种聚合在一起，避免亚属标题重复输出
• 表之间关联用ID字段代替名称字段匹配，查询效率更高，也不会因为重名导致数据错乱

2. 调整循环输出逻辑 #

• 新增变量$current_subgenus，初始值设为空字符串，用于记录上一次输出的亚属名
• 每次遍历物种记录时，先对比当前记录的亚属名和$current_subgenus，如果不一致：
  • 若当前亚属名不为空，输出对应缩进的亚属标题
  • 更新$current_subgenus为当前记录的亚属名
• 根据当前物种是否归属亚属，调整物种行的缩进长度后输出
• 拉取当前物种下的所有亚种，按亚种名排序后输出对应缩进的亚种行
• 所有输出的分类名称用htmlspecialchars()处理，避免名称内的特殊字符破坏HTML结构

// 预处理查询：拉取所有属列表
$genus_stmt = mysqli_prepare($conn, "SELECT genus_id, genus_name FROM Genus ORDER BY genus_name");
mysqli_stmt_execute($genus_stmt);
$genus_res = mysqli_stmt_get_result($genus_stmt);

while($g = mysqli_fetch_assoc($genus_res)) {
    // 顶格加粗输出属名
    echo "<b>Genus: " . htmlspecialchars($g['genus_name']) . "</b><br>";
    // 重置当前亚属标记
    $current_subgenus = '';

    // 预处理查询当前属下所有物种，按规则排序
    $species_stmt = mysqli_prepare($conn, "
        SELECT species_id, species_name, subgenus_name 
        FROM Species 
        WHERE genus_id = ? 
        ORDER BY subgenus_name IS NULL DESC, subgenus_name, species_name
    ");
    mysqli_stmt_bind_param($species_stmt, "i", $g['genus_id']);
    mysqli_stmt_execute($species_stmt);
    $species_res = mysqli_stmt_get_result($species_stmt);

    while($sp = mysqli_fetch_assoc($species_res)) {
        // 亚属变更时输出亚属标题
        if($sp['subgenus_name'] != $current_subgenus) {
            $current_subgenus = $sp['subgenus_name'];
            if($current_subgenus != '') {
                echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Subgenus: " . htmlspecialchars($current_subgenus) . "<br>";
            }
        }

        // 按是否归属亚属调整物种行缩进
        $species_indent = $current_subgenus ? "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" : "&nbsp;&nbsp;&nbsp;";
        echo $species_indent . "Species: " . htmlspecialchars($sp['species_name']) . "<br>";

        // 查询当前物种下所有亚种并输出
        $subsp_stmt = mysqli_prepare($conn, "SELECT subspecies_name FROM Subspecies WHERE species_id = ? ORDER BY subspecies_name");
        mysqli_stmt_bind_param($subsp_stmt, "i", $sp['species_id']);
        mysqli_stmt_execute($subsp_stmt);
        $subsp_res = mysqli_stmt_get_result($subsp_stmt);
        while($subsp = mysqli_fetch_assoc($subsp_res)) {
            echo $species_indent . "&nbsp;&nbsp;&nbsp;&nbsp;Subspecies: " . htmlspecialchars($subsp['subspecies_name']) . "<br>";
        }
        mysqli_stmt_close($subsp_stmt);
    }
    mysqli_stmt_close($species_stmt);
}
mysqli_stmt_close($genus_stmt);

代码输出的缩进完全匹配你给出的预期样例：

• 无亚属归属的物种直接挂在属节点下，缩进3个空格
• 亚属节点缩进5个空格，同亚属的所有物种聚合在对应亚属标题下
• 亚属下的物种缩进7个空格
• 亚种节点比所属物种多缩进4个空格，层级关系清晰

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