phppdoinsertprepared-statementbulkinsert

PDO Prepared Inserts multiple rows in single query

I am using this type of SQL on MySQL to insert multiple rows of values in one single query:

INSERT INTO `tbl` (`key1`,`key2`) VALUES ('r1v1','r1v2'),('r2v1','r2v2'),...

On the readings on PDO, prepared statements should give me a better security than static queries.

I would therefore like to know whether it is possible to generate "inserting multiple rows of values by the use of one query" using prepared statements.

If yes, how can I implement it?

Solution

  • Multiple Values Insert with PDO Prepared Statements

    Inserting multiple values in one execute statement. Why because according to this page it is faster than regular inserts.

    $data[] = ['valueA1', 'valueB1'];
$data[] = ['valueA2', 'valueB2'];

    more data values or you probably have a loop that populates data.

    That is basically how we want the insert statement to look like:

    insert into table (fielda, fieldb, ... ) values (?,?...), (?,?...)....

    So with prepared inserts you need to know the number of fields to create a single VALUES part and the number of rows in order to know how many times to repeat it.

    Now, the code:

    // create the ?,? sequence for a single row
$values = str_repeat('?,', count($data[0]) - 1) . '?';
// construct the entire query
$sql = "INSERT INTO table (columnA, columnB) VALUES " .
    // repeat the (?,?) sequence for each row
    str_repeat("($values),", count($data) - 1) . "($values)";    

$stmt = $db->prepare($sql);
// execute with all values from $data
$stmt->execute(array_merge(...$data));

    Note that this approach is 100% secure, as the query is constructed entirely of constant parts explicitly written in the code, especially the column names. And it works for both mysqli and PDO in all supported PHP versions.