php - 您的SQL语法有误-PHP MYSQL

Question

我有以下代码:

$combined = array_combine($idArray, $sumsArray);
    //print_r($combined);

foreach ($combined as $key => $value) {

        $sqlToUpdate .= "UPDATE tbl_test SET ing_ml='".$value."' WHERE ing_id=".$key.";";

    if(isset($_POST['update'])){

        if ($conn->query($sqlToUpdate) === TRUE) {
            echo "Record updated successfully<br /><br />";
        } else {
            echo "Error updating record: " . $conn->error . "<br /><br />";
        }
    }
}
echo $sqlToUpdate;

echo $sqlToUpdate;的输出是:

UPDATE tbl_test SET ing_ml='-5' WHERE ing_id='22';UPDATE tbl_test SET ing_ml='-1' WHERE ing_id='19';UPDATE tbl_test SET ing_ml='9' WHERE ing_id='13';UPDATE tbl_test SET ing_ml='0' WHERE ing_id='11';UPDATE tbl_test SET ing_ml='5' WHERE ing_id='4';

如果我复制此输出，然后直接在phpMyAdmin中运行，它将完美执行，并且所有5行都将更新。

但是，当我尝试从PHP页面执行它(单击更新按钮，因此单击“if isset”)时，出现以下错误:

Record updated successfully

Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tbl_test SET ing_ml='-1' WHERE ing_id='19'' at line 1

Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tbl_test SET ing_ml='-1' WHERE ing_id='19';UPDATE tbl_test SET ing_ml='9'' at line 1

Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tbl_test SET ing_ml='-1' WHERE ing_id='19';UPDATE tbl_test SET ing_ml='9'' at line 1

Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tbl_test SET ing_ml='-1' WHERE ing_id='19';UPDATE tbl_test SET ing_ml='9'' at line 1

因此，foreach中的第一个查询可以正常执行并更新数据库，但是其余4个查询将失败。我已经尝试了一切，但不知道为什么会这样。我试过在$ value上加上反引号，单引号等，以及在$ value和$ key周围加上反引号，单引号，但似乎没有任何效果。

Answer 1

使用准备好的语句!

$combined = array_combine($idArray, $sumsArray);

$stmt = $conn->prepare("UPDATE tbl_test SET ing_ml=? WHERE ing_id=?");
$stmt->bind_param("ss", $value, $key);
foreach ($combined as $key => $value) {
    $stmt->execute();
}
echo "Record updated successfully<br /><br />";