Table of Content
Inserting multiple data rows using PHP is a basic task. However when you start working with MySQL databases, So that can save time and resources, especially when dealing with large datasets. Instead of executing individual queries for each row, you can use optimized techniques to insert multiple rows in a single operation. This not only improves performance but also reduces server overhead.
In the sections below, you will learn various ways to perform multi-row inserts using PHP.
Single Query with Multiple Value Sets
The general way of inserting multiple rows within a MySQL table is by using only one INSERT query with many value sets. Here is an example:
$sql = "INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com')";
// Execute the query
if (mysqli_query($conn, $sql)) {
echo "Records inserted successfully.";
} else {
echo "Error: " . mysqli_error($conn);
}
This method works for little datasets but has a lot of limitations when dealing with big datasets.
Let’s move on to the next section to see how to insert multiple rows using MySQLi.
Using MySQLi to Insert Multiple Rows
Example of how you could implement it:
// Create connection
$conn = new mysqli('localhost', 'username', 'password', 'database');
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// insert multiple rows
$sql = "INSERT INTO products (name, price) VALUES
('Product1', 10.50),
('Product2', 20.00),
('Product3', 15.75)";
if ($conn->query($sql) === TRUE) {
echo "Multiple rows inserted successfully.";
} else {
echo "Error: " . $conn->error;
}
// close the connection
$conn->close();
However, you could also use PDO for more complex storage requirements, which provides more options. Let’s move on to the next part to see how to use it.
Using PDO for Multi-row Inserts
PHP Data Objects (PDO) Another popular choice to interact with MySQL. You can execute the query for more rows:
try {
$pdo = new PDO("mysql:host=localhost;dbname=testdb", "username", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO orders (order_id, amount) VALUES (?, ?)";
$stmt = $pdo->prepare($sql);
$data = [
[1, 100.50],
[2, 200.00],
[3, 150.25]
];
foreach ($data as $row) {
$stmt->execute($row);
}
echo "Multiple rows inserted successfully.";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
You can iterate through an array of data to dynamically build your SQL query. This is particularly useful if you are dealing with a great deal of data. Here is another example:
$data = [
['John', 'john@example.com'],
['Doe', 'doe@example.com'],
['Smith', 'smith@example.com']
];
$values = [];
foreach ($data as $row) {
$values[] = "('" . implode("', '", $row) . "')";
}
$sql = "INSERT INTO users (name, email) VALUES " . implode(", ", $values);
if (mysqli_query($conn, $sql)) {
echo "Bulk insert successful.";
} else {
echo "Error: " . mysqli_error($conn);
}
Let’s see how multi-row inserts are handled in prepared statements.
Prepared Statements for Secure Multi-row Inserts
Prepared statements to ensure data security by preventing SQL injection. Here is an example using MySQL:
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$data = [
['John', 'john@example.com'],
['Doe', 'doe@example.com'],
['Smith', 'smith@example.com']
];
foreach ($data as $row) {
$stmt->bind_param("ss", $row[0], $row[1]);
$stmt->execute();
}
$stmt->close();
$conn->close();
Let’s see the difference between a single query and multiple queries.
Comparing Single vs Multi-row Insert Performance
Single-row inserts can be slower when a large number of records is involved because each insert requires an explicit round-trip to the database. Multi-row inserts are faster because they reduce the number of queries executed.
Benchmark Example:
- Insert 10,000 rows with individual queries: 2.5 seconds.
- Insert 10,000 rows with a single query: 0.8 seconds.
Always benchmark based on your specific use case to determine the optimal way.
Wrapping Up
Inserting multiple data rows into MySQL is an important skill for any PHP developer, ranging from simple queries to prepared statements and the handling of large data sets.
Similar Reads
You need the array_is_list function to know if an array works as a list or not in PHP. What is…
Escape characters appeared in PHP because some symbols in strings serve special purposes. For example, a quote can end a…
PHP array_intersect_assoc compares values and keys in arrays. It returns matches with the same key and value from all arrays…
Sometimes the string you expect is there, but strpos function gives a surprise in PHP. It may show the result…
The PHP range function helps you make sequences. Without it, you would write loops every time you need numbers or…
If you are new to the PHP Spaceship Operator, then let me tell you, you're in for a treat. This…
The mb_strtolower() function in PHP turns every letter in a string into lowercase. It works with multibyte encodings like UTF-8.…
In this article, You will understand everything you need to know about how to use PHP to select data from…
Ever notice how some websites just seem to "know" you? That’s thanks to cookies! When you’re working with PHP, $_COOKIE becomes a…
A destructor in a PHP class runs when an object is no longer needed. It frees resources and cleans up…