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 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.
Related articles:
- SELECT query with PDO
- INSERT query using PDO
- UPDATE query using PDO
- How to connect to MySQL using PDO
- PDO Examples
- Authenticating a user using PDO and password_verify()
- How to check if email exists in the database?
- Select the number of rows using PDO
- How to create a WHERE clause for PDO dynamically
- DELETE query using PDO
- How to create a prepared statement for UPDATE query
- Getting a nested array when multiple rows are linked to a single entry
- Adding a field name in the ORDER BY clause based on the user's choice
- INSERT helper function for PDO Mysql
- PDO Examples
- PDO Examples
Add a comment
Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator's review.
Markdown is now supported:
>
before and an empty line after for a quote