How to Insert Multiple Rows in MySQL with PHP?

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', '[email protected]'),
        ('Bob', '[email protected]'),
        ('Charlie', '[email protected]')";

// 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', '[email protected]'],
    ['Doe', '[email protected]'],
    ['Smith', '[email protected]']
];

$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', '[email protected]'],
    ['Doe', '[email protected]'],
    ['Smith', '[email protected]']
];

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.