mysqli query inside foreach loop- syntax problem

Question

I have the following mysqli query:

$conn = new mysqli("localhost", "xx", "xx", "xx");

foreach ($response as $class) {

    $sqle= "REPLACE INTO `items`
                    (`item_ids`)
                VALUES
                    ('{$class['id']}'";

    if ($conn->query($sqle) === TRUE) {
       //
    } else {
       echo "Problem";
    }
}

This is my database table "items":

item_ids  |  item_class  |  item_subclass
-------------------------------------------
          |       4       |        5

$response is an API array from where I get the value for the item_ids .

I get the following error:

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 '' at line 4

I don´t understand what is wrong with my syntax here?

If I echo $class['id'] (without the query) in the foreach loop I get the expected values. So it´s a problem with the query inside of it.

Have you tested your conn? See if you have your parameters correct? — , Sep 26 '20 at 02:00
Also there seems to be code missing. You seem to be passing some parameters that don’t exist from your example. (Items_ids) etc — , Sep 26 '20 at 02:01
Connection is ok, i´ve checked it. I´ll edit my question to make it more clear. — Retros, Sep 26 '20 at 02:09
I have placed it outside of the foreach loop, i hope thats right. Now this error appears: `string(108) "REPLACE INTO `items` (`item_ids`) VALUES ('84444'" 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 '' at line 4` — Retros, Sep 26 '20 at 02:22

score 2 · Accepted Answer · answered Sep 26 '20 at 04:33

Your SQL query is missing a closing parenthesis.

$sqle= "REPLACE INTO `items`
                    (`item_ids`)
                VALUES
                    ('{$class['id']}'";

should be:

$sqle= "REPLACE INTO `items`
                    (`item_ids`)
                VALUES
                    ('{$class['id']}')";
                                     ^ here

You should learn to use query parameters. Then it would be easier to spot this type of punctuation mistake, without getting visually confused with all the different quotes and curly braces.

prepare() this query:

$sqle= "REPLACE INTO `items`
                    (`item_ids`)
                VALUES
                    (?)";

Then bind the parameter, and execute.

Parameterized queries make it easier to write code without making mistakes.

mysqli query inside foreach loop- syntax problem

1 Answers1

Linked