How to execute 1000s INSERT/UPDATE queries with PDO?

  1. Comments

The key to have your multiple inserts fast and reliable is to use transactions and a prepared statement.

A transaction will ensure that all operations either complete in their entirety or have no effect whatsoever. Another important advantage of using a transaction is that it makes multiple inserts dramatically faster, eliminating all possible delays that could be caused by separate query execution.

A prepared statement will add some little boost to the speed and of course ensure that there will be no errors or injections.

First of all, make sure you have a proper mysqli connection, which, among other things, tells mysqli to throw an exception in case of error. Then just prepare your query, start a transaction, execute the queries and commit the transaction

include 'pdo.php';

// a sample array of data
$data = [
    [
'col1' => 'foo1''col2' => 'bar1'],
    [
'col1' => 'foo2''col2' => 'bar2'],
];
// prepare the SQL query once
$stmt $pdo->prepare("INSERT INTO table SET col1 = ?, col2 = ?");

$pdo->beginTransaction();
// loop over the data array
foreach ($data as $row) {
    
$stmt->execute([$row['col1'], $row['col2']]);
}
$pdo->commit();

note that it could be not just a single query but multiple different queries but the principle remains the same: just wrap all queries in a transaction and use prepared statements.


Related articles: