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
The IF statement in PHP helps to see if a condition is true or not. If it is true, it…
The array_rand() is a helper function in PHP that makes it easier when dealing with arrays; you can just pull…
MySQL Prepared Statements in PHP are an important tool for ensuring security and optimization. They safeguard against SQL injection attacks,…
PHP offers four main functions to include files: require, require_once, include, and include_once. Each one gives you a similar purpose…
PHP-type hinting is something that keeps your code in check and helps your functions receive just the right types of…
PHP introduced traits to solve a problem with code reuse. Before traits, developers used inheritance and interfaces to share functionality…
The PHP ternary operator gives you a way to write short expressions. It reduces lines of code and avoids long…
Actually, PHP has a built-in function that doesn’t get the spotlight—fclose(). It ends the file manipulation code. Let’s break down…
The OOP constructor initializes an object when it is created in PHP. Understand What a Constructor Is in PHP A…
You can use the array_fill function in PHP when you must fill an array with one value. You may want…