php - mysql 5.7 中的 UPDATE 语法

Question

我正在尝试运行一个简单的 UPDATE 查询:

UPDATE students SET enterTime = '".$enterTime."' WHERE s_id = '".$s_id."'; UPDATE timeLimit  SET listed = listed + 1  WHERE enterTime = '".$enterTime."' AND building = '".$building."';"

它在 MySql 版本 5.1 中工作正常，我的服务器已升级到 MySql 版本 5.7，但出现以下错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE students SET enterTime = '09:00' WHERE ' at line 1 BEGIN; UPDATE students SET enterTime = '09:00' WHERE s_id = '312433931' ; UPDATE timeLimit SET listed = listed + 1 WHERE enterTime = '09:00' AND building = '2'; UPDATE timeLimit SET listed = listed - 1 WHERE enterTime = '08:00' AND building = '2'; COMMIT;

PHP 代码:

function update_time_for_student($student,$enterTime,$oldTime){
    $s_id = $student['s_id'];
    $building = $student['building'];
    $query = "BEGIN; UPDATE students 
                SET enterTime = '".$enterTime."' 
                WHERE s_id = '".$s_id."' ;              
               UPDATE timeLimit 
                SET listed = listed + 1 
                WHERE enterTime = '".$enterTime."' AND building = '".$building."';
              UPDATE timeLimit 
                SET listed = listed - 1 
                WHERE enterTime = '".$oldTime."' AND building = '".$building."'; COMMIT;";
    if (mysql_query($query)){
        $fullName = $student['fname']." ".$student['lname'];
        send_email($student['email'],$enterTime,$fullName,$s_id);
        header("Location:/index.php?d=1"); 
    } else {
        echo mysql_error();
        echo "<br/>";
        echo $query;
        //header("Location:/index.php?d=3");
    }  
}

非常感谢您的帮助!

Answer 1

选项1

尝试将这些行分成多个 php 语句:

$query = "BEGIN";
mysql_query($query) or die (mysql_error());

$query = "UPDATE students SET enterTime = '".$enterTime."' WHERE s_id = '".$s_id."';";
mysql_query($query) or die (mysql_error());

$query = "UPDATE timeLimit  SET listed = listed + 1  WHERE enterTime = '".$enterTime."' AND building = '".$building."';";
mysql_query($query) or die (mysql_error());

$query = "COMMIT";
mysql_query($query) or die (mysql_error());

选项2

您需要使用multi_query。文档 here

注意: mysql_* 命令在 PhP7 中已弃用并删除。请改用 mysqli 或 PDO。引用已接受的答案 here