You have an error in your SQL syntax - 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.

try $sqlToUpdate = "your query"; i dont think simultaneous query will work — Lorence Hernandez, Feb 02 '17 at 11:18
http://stackoverflow.com/questions/13980803/executing-multiple-sql-queries-in-one-statement-with-php — Sooraj, Feb 02 '17 at 11:19

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 02 '17 at 11:41

Your Common Sense

156,878
40
214
345

Thanks for your reply. I'm sure this is what I need to do, however after implementing this, it still isn't doing what I would expect it to do. Is there a way of outputting what is being ran by `$stmt->execute();` so I can see what SQL is being executed on the DB? – dankellys Feb 02 '17 at 12:21
1

you can var_dump($key, $value); – Your Common Sense Feb 02 '17 at 12:26

score 1 · Answer 2 · answered Feb 02 '17 at 11:44

1

Your $conn->query($sqlToUpdate) is inside a foreach loop, and your $sqlToUpdate variable is incremented through .= in this loop.

Each time you loop, you are re-executing former queries.

answered Feb 02 '17 at 11:44

Clorichel

1,940
1
13
24

You have an error in your SQL syntax - PHP MYSQL

2 Answers2

Use prepared statements!