PHP MySQL Insert Multiple

Inserting rows one at a time in a loop works, but running a separate query for every single row is slow when you have many rows to add at once -- like importing a CSV of a hundred products. MySQL supports inserting multiple rows in a single INSERT statement, which is dramatically faster than looping.

In this page:

Basic Multi-Row INSERT Syntax
Building a Multi-Row INSERT Dynamically
Inserting Multiple Rows Safely with Prepared Statements
Comparing Performance: Multi-Row vs Looped Single Inserts
Batching Very Large Inserts

Basic Multi-Row INSERT Syntax

A single INSERT statement can include several parenthesized value groups separated by commas -- INSERT INTO users (name, email) VALUES (Sam, '[email protected]'), (Alex, '[email protected]') inserts two rows in one call to the database.

Note: Use multi-row INSERT syntax whenever you have several related rows ready to insert at once, rather than looping single-row inserts.

Warning: Every value group in a multi-row INSERT must supply values in the same column order -- mixing up the order in just one group silently inserts data into the wrong columns for that row.

Example: Basic Multi-Row INSERT Syntax

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT, email TEXT)");
$db->exec("INSERT INTO users (name, email) VALUES ('Sam', '[email protected]'), ('Alex', '[email protected]')");
echo $db->changes() . " rows inserted";
?>

Building a Multi-Row INSERT Dynamically

When the rows to insert come from an array of data (like parsed CSV rows), you can build the VALUES clause dynamically by looping over the array and joining the resulting value groups with commas, producing one complete multi-row INSERT statement.

Note: Escape or prepare each value individually while building a dynamic multi-row INSERT, since the risk of SQL injection is identical to a single-row insert.

Warning: Building a huge dynamic INSERT statement for an extremely large dataset can hit MySQL's max_allowed_packet size limit -- very large imports are often better done in smaller batches.

Example: Building a Multi-Row INSERT Dynamically

php

<?php
$rows = [['Sam', '[email protected]'], ['Alex', '[email protected]']];
$values = array_map(function ($r) {
    return "('" . $r[0] . "', '" . $r[1] . "')";
}, $rows);
$sql = "INSERT INTO users (name, email) VALUES " . implode(", ", $values);
echo $sql;
?>

Inserting Multiple Rows Safely with Prepared Statements

For untrusted or user-supplied bulk data, executing the same prepared statement repeatedly inside a loop -- once per row -- keeps every value safely bound rather than concatenated, combining the safety of prepared statements with the ability to insert many rows.

Note: Prepare the statement once, outside the loop, and only call bind_param and execute inside the loop -- re-preparing the same statement on every iteration is unnecessary overhead.

Warning: Executing a prepared statement in a tight loop for a very large dataset is safer than dynamic concatenation, but still involves one round trip per row -- true multi-row VALUES syntax is faster for bulk-safe data.

Example: Inserting Multiple Rows Safely with Prepared Statements

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$stmt = $db->prepare("INSERT INTO users (name) VALUES (:name)");
foreach (["Sam", "Alex", "Robert'); DROP TABLE users;"] as $name) {
    $stmt->bindValue(':name', $name, SQLITE3_TEXT);
    $stmt->execute();
}
echo "All rows inserted safely";
?>

Comparing Performance: Multi-Row vs Looped Single Inserts

A single multi-row INSERT statement is dramatically faster than looping individual INSERT statements for the same data, because it involves just one network round-trip to the database server instead of one per row -- the difference becomes very noticeable once you are inserting hundreds or thousands of rows.

Note: For large bulk imports of trusted, already-validated data, prefer building a multi-row VALUES statement (in reasonably sized batches) over looping single-row inserts.

Warning: Looping single-row INSERT calls for a large dataset can turn an operation that should take milliseconds into one that takes many seconds, purely from repeated network round-trips.

Example: Comparing Performance: Multi-Row vs Looped Single Inserts

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$start = microtime(true);
$db->exec("INSERT INTO users (name) VALUES ('A'), ('B'), ('C')"); // one round-trip
echo "Multi-row insert: " . round(microtime(true) - $start, 5) . "s";
?>

Batching Very Large Inserts

For extremely large datasets (tens of thousands of rows or more), inserting everything in one giant multi-row statement can hit MySQL's configured packet size limit -- splitting the data into reasonably sized batches (like 500 or 1000 rows per statement) balances speed against that limit.

Note: A batch size in the hundreds to low thousands is a reasonable starting point for most setups; test and adjust based on your specific server's max_allowed_packet configuration.

Warning: A single multi-row INSERT that exceeds the server's max_allowed_packet setting will fail outright, so extremely large unbached imports can suddenly stop working as data volume grows.

Example: Batching Very Large Inserts

php

<?php
$rows = range(1, 1500);
$batches = array_chunk($rows, 500);
echo count($batches) . " batches of up to 500 rows each";
?>

Common Mistakes

Looping and running a separate mysqli_query() call for every row when a single multi-row INSERT would be far faster and put much less load on the database connection.
Building a multi-row INSERT by concatenating raw, un-escaped values into the SQL string, opening the door to SQL injection just as with a single-row insert.
Forgetting that if any one row in a bulk INSERT violates a constraint, the entire statement can fail depending on the storage engine and settings, potentially rejecting all rows.

Chapter Summary

INSERT INTO table (col1, col2) VALUES (a, b), (c, d), (e, f) inserts three rows in a single statement.
Multi-row inserts are significantly faster than looping single-row inserts, since they involve far fewer round trips to the database server.
Prepared statements can still be used for multi-row inserts by executing the same prepared statement repeatedly with different bound values.

Browser Support

Multi-row INSERT syntax is standard SQL and works identically across all MySQL and MariaDB versions PHP supports.

🔒

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP MySQL Get Last ID Next Lesson → PHP MySQL Prepared Statements