My SQL request work in SQL but not in PHP

I’m trying to make an unique request to insert multiple element in my database to not make multiple request.

Here is my “generated” request, which work in SQL (tested in PHPMYADMIN).

INSERT INTO my_table (id, user) VALUES (1,2), (3,4)

Generated by this code.

$query = 'INSERT INTO my_table (id, user) VALUES ';

$data = array(
    array(1, 2),
    array(3, 4)
);

$request = '';

foreach($data as $data)
{
    $request .= '(' . implode(',', $data) . '), ';
}
$request = substr($request, 0, -2);

//Insert in DB
$req = $db->prepare($request);
$req->execute();

But I get this error in PHP

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 '1,2), (3,4)' at line 1 in testfonction.php:47 Stack trace: #0 testfonction.php(47): PDO->exec('(1,2), (3,4)') #1 structure_gc.php(32): require('/v...') #2 index.php(22): require('/v...') #3 {main} thrown in testfonction.php on line 47

Do you know why this request does not work when I try to use it with PHP ? Thanks !

Answer

You have two variables: $request and $query. I propose:

$query = 'INSERT INTO my_table (id, user) VALUES ';

$data = [[1, 2],
         [3, 4]];

foreach($data as $data)
{
    $query .= '(' . implode(',', $data) . '), ';
}

$query = substr($query, 0, -2);

$stmt = $db->prepare($query);
$stmt->execute();

That is; Just one variable. This way you also include the first bit in the query you execute.

Note that in future queries, where data could potentially come from user input, it would be wise to use parameter binding.