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


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 = ?");

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

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: