SQL查询在Sequel Ace正常运行但PHP页面返回NULL问题排查

连接MariaDB数据库时，以下SQL查询在Sequel Ace中可正常执行并返回预期结果，但在PHP页面执行时所有字段均返回NULL。页面数据库连接正常，其他查询可正常返回结果。

Sequel Ace中的查询语句 #

SELECT Teacher, Period, AssessPeriod,
        ROUND(SUM(`Diagnostic Gain`) / COUNT(*), 2) AS Avg_Student_Growth_MOY,
        ROUND(SUM(`Annual Typical Growth Measure`) / COUNT(*), 2) AS Avg_Typical_Growth_Measure,
        ROUND((SUM(`Diagnostic Gain`) / SUM(`Annual Typical Growth Measure`)) * 100, 2) AS Percent_of_Typical_Growth,
        ROUND(SUM(`Annual Stretch Growth Measure`) / COUNT(*), 2) AS Avg_Stretch_Growth_Measure,
        ROUND((SUM(`Diagnostic Gain`) / SUM(`Annual Stretch Growth Measure`)) * 100, 2) AS Percent_of_Stretch_Growth
    FROM `iReadyResultsMath`
    JOIN `AllRosters2324Spring` ON `iReadyMSID` = `Student-ID`
    WHERE `Teacher` = 'TeacherName' AND `Period` = '0' AND `AssessPeriod` = 'MOY'

Sequel Ace执行结果 #

PHP代码 #

<?php
    $stmt = $pdo->query("SELECT
        ROUND(SUM(`Diagnostic Gain`) / COUNT(*), 2) AS `Avg_Student_Growth_MOY`,
        ROUND(SUM(`Annual Typical Growth Measure`) / COUNT(*), 2) AS `Avg_Typical_Growth_Measure`,
        ROUND((SUM(`Diagnostic Gain`) / SUM(`Annual Typical Growth Measure`)) * 100, 2) AS Percent_of_Typical_Growth,
        ROUND(SUM(`Annual Stretch Growth Measure`) / COUNT(*), 2) AS `Avg_Stretch_Growth_Measure`,
        ROUND((SUM(`Diagnostic Gain`) / SUM(`Annual Stretch Growth Measure`)) * 100, 2) AS `Percent_of_Stretch_Growth`
    FROM `iReadyResultsMath`
    JOIN `AllRosters2324Spring` ON `iReadyMSID` = `Student-ID`
    WHERE `Teacher` = '$teach' AND `Period` = '$per' AND `AssessPeriod` = 'MOY' ");
   
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    
    while ($row = $stmt->fetch())
    { 
        $asgm = $row["Avg_Student_Growth_MOY"];
        $atgm = $row['Avg_Typical_Growth_Measure'];
        $ptg = $row['Percent_of_Typical_Growth'];
    }

    var_dump($result);
?>

PHP执行结果（var_dump返回） #

array(1) { [0]=> array(5) { ["Avg_Student_Growth_MOY"]=> NULL ["Avg_Typical_Growth_Measure"]=> NULL ["Percent_of_Typical_Growth"]=> NULL ["Avg_Stretch_Growth_Measure"]=> NULL ["Percent_of_Stretch_Growth"]=> NULL } }

• 验证变量值匹配度：确认PHP中$teach和$per变量的实际值，与Sequel Ace中使用的'TeacherName'、'0'完全一致（包括大小写、空格、特殊字符）。可以先将SQL中的变量替换为硬编码值测试，看是否能返回正常结果。
• 改用预处理语句：直接拼接变量到SQL存在注入风险，也可能因特殊字符导致逻辑错误。替换为预处理语句：

  <?php
  $sql = "SELECT
      ROUND(SUM(`Diagnostic Gain`) / COUNT(*), 2) AS `Avg_Student_Growth_MOY`,
      ROUND(SUM(`Annual Typical Growth Measure`) / COUNT(*), 2) AS `Avg_Typical_Growth_Measure`,
      ROUND((SUM(`Diagnostic Gain`) / NULLIF(SUM(`Annual Typical Growth Measure`), 0)) * 100, 2) AS Percent_of_Typical_Growth,
      ROUND(SUM(`Annual Stretch Growth Measure`) / COUNT(*), 2) AS `Avg_Stretch_Growth_Measure`,
      ROUND((SUM(`Diagnostic Gain`) / NULLIF(SUM(`Annual Stretch Growth Measure`), 0)) * 100, 2) AS `Percent_of_Stretch_Growth`
  FROM `iReadyResultsMath`
  JOIN `AllRosters2324Spring` ON `iReadyMSID` = `Student-ID`
  WHERE `Teacher` = ? AND `Period` = ? AND `AssessPeriod` = 'MOY'";
  
  $stmt = $pdo->prepare($sql);
  $stmt->execute([$teach, $per]);
  $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
  
  var_dump($result);
  ?>
  

• 处理分母为0的情况：如果SUM(Annual Typical Growth Measure)或SUM(Annual Stretch Growth Measure)为0，除法运算会返回NULL。使用NULLIF函数避免除以0的问题，同时在Sequel Ace中执行带变量值的查询，确认是否存在分母为0的场景。
• 移除冗余的fetch操作：原代码中先调用fetchAll获取全部结果，之后又用while($stmt->fetch())读取结果，会导致游标移动到结果集末尾，但不影响$result的值，建议删除该冗余循环。

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