MySQL Insert Multiple Records
Learn to insert multiple rows efficiently in MySQL
📊 What is Insert Multiple?
Insert Multiple allows you to add several records to a MySQL database table in a single query. This method is faster and more efficient than inserting rows one by one.
<?php
// Insert multiple records at once
$sql = "INSERT INTO users (name, email) VALUES
('John', '[email protected]'),
('Jane', '[email protected]'),
('Bob', '[email protected]')";
?>
Key Concepts
Efficiency
Insert many rows with one query
// One query, multiple rows
INSERT INTO table VALUES (...), (...);Batch Insert
Group data for faster processing
// Batch processing
foreach($data as $row) {
// Collect rows
}Validation
Check all data before inserting
// Validate first
if($conn->query($sql)) {
echo "Success!";
}Error Handling
Manage insertion failures
// Handle errors
if(!$conn->query($sql)) {
echo $conn->error;
}🔹 Basic Multiple Insert
Insert multiple records into a table using a single INSERT statement. This approach reduces database round trips and improves performance significantly.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Insert multiple records
$sql = "INSERT INTO users (name, email, age) VALUES
('Alice', '[email protected]', 25),
('Charlie', '[email protected]', 30),
('Diana', '[email protected]', 28)";
if ($conn->query($sql) === TRUE) {
echo "Multiple records inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>Output:
Multiple records inserted successfully
🔹 Insert Multiple with Array
Use PHP arrays to dynamically build multiple insert queries. This method is useful when working with form data or API responses.
<?php
$conn = new mysqli("localhost", "root", "", "myDB");
// Array of data to insert
$users = [
['name' => 'Tom', 'email' => '[email protected]', 'age' => 22],
['name' => 'Sara', 'email' => '[email protected]', 'age' => 27],
['name' => 'Mike', 'email' => '[email protected]', 'age' => 35]
];
// Build values string
$values = [];
foreach ($users as $user) {
$values[] = "('{$user['name']}', '{$user['email']}', {$user['age']})";
}
$sql = "INSERT INTO users (name, email, age) VALUES " . implode(', ', $values);
if ($conn->query($sql) === TRUE) {
echo "All records inserted successfully";
} else {
echo "Error: " . $conn->error;
}
$conn->close();
?>Output:
All records inserted successfully
🔹 Insert Multiple with Loop
Process multiple inserts using a loop for better control and error handling per record.
<?php
$conn = new mysqli("localhost", "root", "", "myDB");
$users = [
['Emma', '[email protected]', 29],
['Liam', '[email protected]', 31],
['Olivia', '[email protected]', 26]
];
$success_count = 0;
foreach ($users as $user) {
$sql = "INSERT INTO users (name, email, age)
VALUES ('{$user[0]}', '{$user[1]}', {$user[2]})";
if ($conn->query($sql) === TRUE) {
$success_count++;
}
}
echo "$success_count records inserted successfully";
$conn->close();
?>Output:
3 records inserted successfully
💡 Best Practices
- Use prepared statements for security (see MySQL Prepared lesson)
- Limit batch size to avoid memory issues (500-1000 rows per query)
- Always validate data before inserting
- Use transactions for critical data
- Handle errors gracefully with try-catch blocks