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
– Lorence Hernandez, Commented Feb 2, 2017 at 11:18

Community · Accepted Answer · 2020-06-20 09:12:55Z

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, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 2, 2017 at 11:41

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dankellys Over a year ago

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?

Your Common Sense Over a year ago

you can var_dump($key, $value);

Clorichel · Accepted Answer · 2017-02-02 11:44:38Z

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 2, 2017 at 11:44

Clorichel

2,1001 gold badge15 silver badges25 bronze badges

Collectives™ on Stack Overflow

You have an error in your SQL syntax - PHP MYSQL

2 Answers 2

Use prepared statements!

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Use prepared statements!

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related