mysqli中含SET的查询在PHP表格代码中无法运行的问题排查

我编写的包含SET语句的MySQL查询在数据库编辑器中可以正常运行，但移植到PHP表格代码后却无法工作。此前从未在MySQL查询中使用过SET语法，推测问题与此相关，尝试拆分SET语句但仍未解决，想确认是否存在遗漏的细节。

相关代码 #

<table data-role="table" id="table-column" data-theme="f" data-mode="table" class="ui-responsive table-stroke table-stripe">
            <thead>
                <tr>
                    <th style="text-align: center" data-priority="persist">Date Recorded</th>
                    <th style="text-align: center" data-priority="1">Max Temp C°</th>
                    <th style="text-align: center" data-priority="1">rH%</th>
                    <th style="text-align: center" data-priority="2">Hours</th>
                    <th style="text-align: center" data-priority="2">Hours Running Total</th>
                    <th style="text-align: center" data-priority="persist">pH</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <?php
                        if (mysqli_connect_errno())
                          {
                          echo "Failed to connect to MySQL: " . mysqli_connect_error();
                          }

                         $result = mysqli_query($link, "SET @runtot:= 0;
SELECT   q1.dated, max_temp, max_rh, q1.c, (@runtot := @runtot + q1.c) AS rt
FROM
(SELECT datestamp AS dated,
   max_temp,
   max_rh,
   sum(hours) AS c,
   ph
FROM hours
WHERE hours > 0 AND username = '$_SESSION[USERNAME]' AND batch_no = '$_SESSION[batch_no]'
GROUP BY dated
ORDER BY dated) AS q1");


                        if (!$result) {
                            die("Query to show fields from table failed");
                        }
                        $fields_num = mysqli_num_fields($result);
                        for($i=0; $i<$fields_num; $i++)
                        {
                            $field = mysqli_fetch_field($result);
                        }
                        while($row = mysqli_fetch_row($result))
                        {
                    ?>
                    <td style="text-align: center; vertical-align: middle"><?php echo "$row[0]"?></td>
                    <td style="text-align: center; vertical-align: middle"><?php echo "$row[1]"?></td>
                    <td style="text-align: center; vertical-align: middle"><?php echo "$row[2]"?></td>
                    <td style="text-align: center; vertical-align: middle"><?php echo "$row[3]"?></td>
                    <td style="text-align: center; vertical-align: middle"><?php echo "$row[4]"?></td>
                    <td style="text-align: center; vertical-align: middle"><?php echo "$row[5]"?></td>
                </tr>
                <?php
                    }
                    mysqli_free_result($result)
                ?>
            </tbody>
        </table>

问题原因 #

核心问题是mysqli_query() 默认不支持执行多条SQL语句。你将SET @runtot:=0;和SELECT语句放在同一个mysqli_query调用中，数据库编辑器允许批量执行多条语句，但PHP的mysqli_query默认仅支持单语句执行，因此导致查询失败。

解决方案 #

方法一：拆分语句分别执行

将SET语句和SELECT语句分开，用两次mysqli_query调用执行：

// 先执行SET语句初始化变量
mysqli_query($link, "SET @runtot:= 0;");
// 再执行SELECT查询
$result = mysqli_query($link, "SELECT   q1.dated, max_temp, max_rh, q1.c, (@runtot := @runtot + q1.c) AS rt
FROM
(SELECT datestamp AS dated,
   max_temp,
   max_rh,
   sum(hours) AS c,
   ph
FROM hours
WHERE hours > 0 AND username = '$_SESSION[USERNAME]' AND batch_no = '$_SESSION[batch_no]'
GROUP BY dated
ORDER BY dated) AS q1");

方法二：在查询中直接初始化变量

无需单独执行SET语句，通过CROSS JOIN在SELECT语句中初始化变量：

$result = mysqli_query($link, "SELECT   q1.dated, max_temp, max_rh, q1.c, (@runtot := @runtot + q1.c) AS rt
FROM
(SELECT datestamp AS dated,
   max_temp,
   max_rh,
   sum(hours) AS c,
   ph
FROM hours
WHERE hours > 0 AND username = '$_SESSION[USERNAME]' AND batch_no = '$_SESSION[batch_no]'
GROUP BY dated
ORDER BY dated) AS q1
CROSS JOIN (SELECT @runtot := 0) AS init");

重要安全提示 #

你的代码存在严重SQL注入风险，直接将$_SESSION变量拼接到SQL语句中会被恶意利用。必须改用预处理语句：

// 初始化变量
mysqli_query($link, "SET @runtot:= 0;");
// 准备预处理语句
$stmt = mysqli_prepare($link, "SELECT q1.dated, max_temp, max_rh, q1.c, (@runtot := @runtot + q1.c) AS rt
FROM
(SELECT datestamp AS dated,
   max_temp,
   max_rh,
   sum(hours) AS c,
   ph
FROM hours
WHERE hours > 0 AND username = ? AND batch_no = ?
GROUP BY dated
ORDER BY dated) AS q1");
// 绑定参数（"ss"表示两个字符串类型参数）
mysqli_stmt_bind_param($stmt, "ss", $_SESSION['USERNAME'], $_SESSION['batch_no']);
// 执行语句
mysqli_stmt_execute($stmt);
// 获取结果集
$result = mysqli_stmt_get_result($stmt);

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