Inserting Multiple Records into a MySQL Database using PHP

Inserting records one at a time means one round trip to the database server per row — slow and wasteful when you have hundreds or thousands of rows to load. Batching them into a single INSERT statement lets MySQL parse, plan, and commit the whole batch once, which is dramatically faster.

This chapter shows three ways to insert multiple rows with PHP's mysqli extension:

1. A single INSERT ... VALUES (...), (...) statement — the simplest, fastest bulk insert.
2. A prepared statement looped over your data — safe against SQL injection, ideal when the values come from users.
3. mysqli_multi_query() — running several distinct statements stacked in one string.

If you have not connected to a database yet, start with Connecting to MySQL with PHP. For inserting a single row, see Insert Data Into MySQL.

Establishing a Connection

Every example below assumes an open mysqli connection in $conn. We create one with mysqli_connect() and stop early if it fails:

<?php
$servername = "localhost";
$username   = "username";
$password   = "password";
$dbname     = "database_name";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

Method 1: One INSERT with Multiple Value Sets

The fastest way to add many rows is a single INSERT INTO statement that lists several comma-separated value sets. MySQL inserts them all in one operation:

<?php
$sql = "INSERT INTO users (firstname, lastname, email)
        VALUES ('John', 'Doe', '[email protected]'),
               ('Mary', 'Moe', '[email protected]'),
               ('Julie', 'Dooley', '[email protected]')";

if (mysqli_query($conn, $sql)) {
    // mysqli_affected_rows() reports how many rows were inserted
    $count = mysqli_affected_rows($conn);
    echo "$count records inserted successfully.";
} else {
    echo "Error inserting records: " . mysqli_error($conn);
}
?>

mysqli_query() takes two arguments — the connection and the SQL string — and returns true on success or false on failure. After a successful insert, mysqli_affected_rows() tells you how many rows were actually written (here, 3).

Use this when the data is trusted (hard-coded or already sanitized). Because the values are concatenated straight into the SQL string, it is not safe for raw user input — that is what Method 2 fixes.

Method 2: Prepared Statement in a Loop

When the values come from a form, an API, or any untrusted source, build the query with a prepared statement so the data is sent separately from the SQL. This blocks SQL injection and lets you reuse the same compiled statement for every row:

<?php
$users = [
    ['John',  'Doe',    '[email protected]'],
    ['Mary',  'Moe',    '[email protected]'],
    ['Julie', 'Dooley', '[email protected]'],
];

// Prepare once with placeholders
$stmt = mysqli_prepare($conn, "INSERT INTO users (firstname, lastname, email) VALUES (?, ?, ?)");

// Bind PHP variables to the placeholders ("sss" = three strings)
mysqli_stmt_bind_param($stmt, "sss", $firstname, $lastname, $email);

foreach ($users as [$firstname, $lastname, $email]) {
    mysqli_stmt_execute($stmt);   // runs with the current variable values
}

echo count($users) . " records inserted safely.";
mysqli_stmt_close($stmt);
?>

The "sss" type string tells MySQL the three bound parameters are strings. Use i for integers, d for doubles/floats, and b for blobs. For a full walkthrough, see PHP MySQL Prepared Statements.

For top speed with thousands of rows, wrap the loop in a transaction so MySQL commits once instead of after every execute:

<?php
mysqli_begin_transaction($conn);
foreach ($users as [$firstname, $lastname, $email]) {
    mysqli_stmt_execute($stmt);
}
mysqli_commit($conn);
?>

Method 3: mysqli_multi_query()

The methods above insert into one table with one statement. When you need to run several different statements at once — for example inserting into two tables — use mysqli_multi_query(), which executes multiple SQL statements separated by semicolons from a single string:

<?php
$sql  = "INSERT INTO users (firstname, lastname) VALUES ('John', 'Doe');";
$sql .= "INSERT INTO users (firstname, lastname) VALUES ('Mary', 'Moe');";
$sql .= "INSERT INTO logs (action) VALUES ('bulk import')";

if (mysqli_multi_query($conn, $sql)) {
    echo "Multiple statements executed successfully.";
} else {
    echo "Error: " . mysqli_error($conn);
}
?>

Gotcha: mysqli_multi_query() accepts arbitrary stacked statements, so it is risky with any user-supplied input — never build its string from untrusted data. For plain bulk inserts, prefer Method 1 or Method 2.

Getting the Inserted IDs

After inserting, mysqli_insert_id() returns the AUTO_INCREMENT id generated by the last inserted row. With a multi-row insert it gives you the id of the first row of the batch; subsequent rows follow sequentially. See Get the ID of the Last Inserted Record for details.

Closing the Connection

mysqli closes the connection automatically when the script ends, but it is good practice to release it explicitly once you are done:

<?php
mysqli_close($conn);
?>

Which Method Should I Use?

Conclusion

Batching inserts turns many slow round trips into one fast operation. Reach for a single multi-value INSERT when the data is trusted, a looped prepared statement (wrapped in a transaction for large batches) when it comes from users, and mysqli_multi_query() only when you genuinely need to run distinct statements together. Whatever the method, validate your input and check mysqli_error() so failures never pass silently.

Practice