PHP中使用MySQL实现嵌套循环出现卡顿问题求助

问题原因 #

你遇到的问题是因为mysqli在单连接下，内层查询会覆盖外层查询的结果集：当执行第二个mysqli_query时，之前的$sqla结果集被自动关闭，导致外层while循环只能读取第一行数据后就无法继续。

解决方案 #

方案一：预读取外层结果到数组 #

先把sector表的所有数据读取到数组中，再循环数组处理内层查询，避免内层查询干扰外层结果集：

<?php
include './include/config.php';

// 预读取所有sector数据到数组
$sectorData = [];
$sqla = mysqli_query($conn, "SELECT * FROM sector");
while ($rowa = $sqla->fetch_assoc()) {
    $sectorData[] = $rowa;
}

// 循环处理每个sector及其关联的cfamilia
foreach ($sectorData as $rowa) {
    $codigo = $rowa['codigo'];
    $sector = $rowa['sector'];
    echo $sector . "<br>";
    
    $sqlb = mysqli_query($conn, "SELECT * FROM cfamilia WHERE sector = '$codigo'");
    while ($rowb = $sqlb->fetch_assoc()) {
        $cfamilia = $rowb['cfamilia'];
        echo $cfamilia . " - ";
    }
}
?>

方案二：使用SQL JOIN优化查询（推荐） #

通过LEFT JOIN一次性关联两个表的所有数据，减少数据库查询次数，同时避免嵌套循环的问题：

<?php
include './include/config.php';

// 关联查询sector和对应的cfamilia数据
$sql = "SELECT s.sector, c.cfamilia 
        FROM sector s 
        LEFT JOIN cfamilia c ON s.codigo = c.sector 
        ORDER BY s.sector, c.cfamilia";

$result = mysqli_query($conn, $sql);

$currentSector = '';
while ($row = $result->fetch_assoc()) {
    // 切换sector时输出新的标题
    if ($row['sector'] !== $currentSector) {
        if ($currentSector !== '') echo "<br>";
        $currentSector = $row['sector'];
        echo $currentSector . "<br>";
    }
    // 输出关联的cfamilia（判断非空避免无数据时输出多余符号）
    if (!empty($row['cfamilia'])) {
        echo $row['cfamilia'] . " - ";
    }
}
?>

重要提醒：修复SQL注入风险 #

你的代码直接将变量$codigo拼接到SQL语句中，存在严重的SQL注入风险。建议使用预处理语句替代，以方案一为例修改：

<?php
include './include/config.php';

// 预读取sector数据
$sectorData = [];
$sqla = mysqli_query($conn, "SELECT * FROM sector");
while ($rowa = $sqla->fetch_assoc()) {
    $sectorData[] = $rowa;
}

// 预处理内层查询，避免SQL注入
$stmt = mysqli_prepare($conn, "SELECT * FROM cfamilia WHERE sector = ?");
mysqli_stmt_bind_param($stmt, "s", $codigo);

foreach ($sectorData as $rowa) {
    $codigo = $rowa['codigo'];
    $sector = $rowa['sector'];
    echo $sector . "<br>";
    
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    while ($rowb = $result->fetch_assoc()) {
        $cfamilia = $rowb['cfamilia'];
        echo $cfamilia . " - ";
    }
}
mysqli_stmt_close($stmt);
?>

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