PHP while循环中explode生成SQL条件重复历史数据问题

开发者阿泽

2026-7-13

问题描述

在PHP的while循环内使用explode函数按逗号(,)分隔字符串，生成SQL查询条件时，首次循环输出正常，但第二次循环的结果会包含上一次循环的内容：

原始查询代码：

$queryselectprocesses ="SELECT * FROM laziness_config_processes WHERE busid = $buss ";
$execqueryselectprocesses = sqlsrv_query($con_laziness, $queryselectprocesses);

while($fila = sqlsrv_fetch_array($execqueryselectprocesses,SQLSRV_FETCH_ASSOC)){
    $modalities_select = $fila['modalities'];
    $institutions_select = $fila['institutions'];
}

循环获取的原始数据：

首次循环：$modalities_select = "AB, CD"；$institutions_select = "CAR, MOTORCYCLE"
第二次循环：$modalities_select = "FG, HI"；$institutions_select = "BIKE, HELMET"

处理生成SQL的代码及问题：

$queryselectprocesses ="SELECT * FROM laziness_config_processes WHERE busid = $buss ";
$execqueryselectprocesses = sqlsrv_query($con_laziness, $queryselectprocesses);

while($fila = sqlsrv_fetch_array($execqueryselectprocesses,SQLSRV_FETCH_ASSOC)){
    $modalities_select = $fila['modalities'];
    $institutions_select = $fila['institutions'];

    $mods = explode(',', $modalities_select);
    for ($i = 0; $i < count($mods); $i++){
        $mods[$i] = trim($mods[$i]);
    }
    foreach ($mods as $value) {
        if ($value != '-1' and trim($value) != '') {
            $mod .= "'" . trim($value). "',";
        }
    }
    if (trim($mod) != '') {
       $mod = substr($mod, 0, strlen($mod)-1);
       $mod = " modality in ( $mod ) ";
    }

    $insts = explode(',', $institutions_select);
    for ($i = 0; $i < count($insts); $i++){
        $insts[$i] = trim($insts[$i]);
    }
    foreach ($insts as $value) {
            if ($value != '-1' and trim($value) != '') {
             $inst .= "'" . trim($value). "',";
        }
    }
    if (trim($inst) != '') {
          $inst = substr($inst, 0, strlen($inst)-1);
          $inst = " institution in ( $inst ) ";
    }

    $query = " select * from tb_model where $mod and $inst ";
    echo $query;
}

实际输出：

首次循环$query：select * from tb_study where modality in ( 'AB','CD' ) and institution in ( 'CAR','MOTORCYCLE' )
第二次循环$query：select * from tb_study where modality in ( modality in ( 'AB','CD' ) 'FG','HI' ) and institution in ( institution in ( 'CAR','MOTORCYCLE' ) 'BIKE','HELMET' )

期望输出：

第二次循环$query：select * from tb_study where modality in ( 'FG','HI' ) and institution in ( 'BIKE','HELMET' )

解决方案

问题根源是$mod和$inst变量在循环中未重置，PHP中变量会保留上一次循环的取值，导致每次循环都在旧内容基础上拼接新数据。

核心解决步骤：在每次while循环开始时，将$mod和$inst重置为空字符串。

修改后的完整代码：

$queryselectprocesses ="SELECT * FROM laziness_config_processes WHERE busid = $buss ";
$execqueryselectprocesses = sqlsrv_query($con_laziness, $queryselectprocesses);

while($fila = sqlsrv_fetch_array($execqueryselectprocesses,SQLSRV_FETCH_ASSOC)){
    // 关键：每次循环初始化变量，清空旧值
    $mod = '';
    $inst = '';

    $modalities_select = $fila['modalities'];
    $institutions_select = $fila['institutions'];

    $mods = explode(',', $modalities_select);
    foreach ($mods as $value) {
        $trimmedVal = trim($value);
        if ($trimmedVal != '-1' && $trimmedVal != '') {
            $mod .= "'" . $trimmedVal . "',";
        }
    }
    if (trim($mod) != '') {
       $mod = substr($mod, 0, -1); // 替代strlen写法更简洁
       $mod = " modality in ( $mod ) ";
    }

    $insts = explode(',', $institutions_select);
    foreach ($insts as $value) {
        $trimmedVal = trim($value);
        if ($trimmedVal != '-1' && $trimmedVal != '') {
            $inst .= "'" . $trimmedVal . "',";
        }
    }
    if (trim($inst) != '') {
          $inst = substr($inst, 0, -1);
          $inst = " institution in ( $inst ) ";
    }

    $query = " select * from tb_model where $mod and $inst ";
    echo $query;
}

额外优化点：

移除了多余的for循环，直接在foreach中处理字符串修剪，减少代码冗余
用substr($mod, 0, -1)替代substr($mod, 0, strlen($mod)-1)，写法更简洁高效

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