您的SQL语法错误 - PHP MYSQL

Question

I have the following code:

$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;

the output from echo $sqlToUpdate; is:

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';

If I copy this output, and run it directly in phpMyAdmin, it executes perfectly, and all 5 rows are updated.

However, when I try to execute it from the PHP page (clicking the update button, hence the "if isset") I receive the following errors:

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

So, the first query within the foreach executes fine and updates the DB, but the remaining 4 queries fail. I have tried everything and can not figure out why this is. I have tried adding backticks, single quotes etc around $value on its own, and around both $value and $key but nothing seems to work.

Answer 1

Use prepared statements!

$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 />";