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

1. Comments (1)

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 PDO connection, which, among other things, tells PDO to throw exceptions 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.