Inserting multiple records into a database is a common task in PHP web development. This guide will show you how to efficiently insert multiple rows of data into a MySQL database using PHP.

Why Insert Multiple Records?

Inserting multiple records at once can significantly improve performance when dealing with large datasets. It reduces the number of database connections and queries, resulting in faster execution times.

Basic Syntax

To insert multiple records, we'll use a prepared statement with multiple value sets. Here's the general structure:


$sql = "INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->execute([value1, value2, value3, value4, value5, value6, value7, value8, value9]);

Practical Example

Let's look at a concrete example of inserting multiple user records into a 'users' table:


connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare SQL statement
$sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)";

// Prepare and bind
$stmt = $conn->prepare($sql);
$stmt->bind_param("ssisssisi", $name1, $email1, $age1, $name2, $email2, $age2, $name3, $email3, $age3);

// Set parameters and execute
$name1 = "John Doe";
$email1 = "john@example.com";
$age1 = 30;

$name2 = "Jane Smith";
$email2 = "jane@example.com";
$age2 = 25;

$name3 = "Mike Johnson";
$email3 = "mike@example.com";
$age3 = 35;

$stmt->execute();

echo "New records created successfully";

$stmt->close();
$conn->close();
?>

Using a Loop for Dynamic Insertion

For more flexibility, you can use a loop to insert a variable number of records:


prepare($sql);

foreach ($users as $user) {
    $stmt->bind_param("ssi", $user[0], $user[1], $user[2]);
    $stmt->execute();
}

echo "All records inserted successfully";

$stmt->close();
$conn->close();
?>

Best Practices

Use prepared statements to prevent SQL injection attacks.
Group multiple inserts into a single query for better performance.
Consider using transactions for large datasets to ensure data integrity.
Handle errors appropriately to maintain data consistency.

Performance Considerations

When inserting large amounts of data, consider breaking it into smaller batches. This approach helps manage memory usage and improves overall performance. For extremely large datasets, you might want to explore bulk insert methods specific to your database system.

Related Concepts

To further enhance your PHP database skills, explore these related topics:

By mastering the technique of inserting multiple records, you'll significantly improve your database management capabilities in PHP. This skill is crucial for developing efficient and scalable web applications.