PHP MySQL Insert Multiple Records – Complete Beginner Tutorial

In real-world applications, you often need to insert multiple records into a MySQL table at once. Examples include bulk user registration, importing data, or saving multiple form entries.

This tutorial explains how to insert multiple records in MySQL using PHP, using different methods with clear examples and output.

What Does Insert Multiple Records Mean in PHP MySQL?

Inserting multiple records means adding more than one row into a MySQL table using a single or multiple SQL queries.

There are two common approaches:

• Multi-row INSERT query
• Loop-based INSERT using PHP

Prerequisites for Inserting Multiple Records

Before starting, ensure you have:

• PHP installed
• MySQL database created
• A working MySQL database connection
• A table with appropriate columns

Sample MySQL Table Used in Examples

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT
);

PHP MySQL Database Connection Code

<?php
$conn = mysqli_connect("localhost", "root", "", "testdb");

if (!$conn) {
    die("Database connection failed");
}
?>

Insert Multiple Records Using Single SQL Query

<?php
$query = "INSERT INTO users (name, email, age) VALUES
('Rahul', 'rahul@example.com', 25),
('Amit', 'amit@gmail.com', 30),
('Neha', 'neha@gmail.com', 22)";

if (mysqli_query($conn, $query)) {
    echo "Multiple records inserted successfully";
}
?>

Output:

Multiple records inserted successfully

This method is fast and efficient when data is static.

Insert Multiple Records Using PHP Loop

<?php
$users = [
    ['Suresh', 'suresh@gmail.com', 28],
    ['Pooja', 'pooja@gmail.com', 24],
    ['Ravi', 'ravi@gmail.com', 31]
];

foreach ($users as $user) {
    $query = "INSERT INTO users (name, email, age)
              VALUES ('$user[0]', '$user[1]', $user[2])";
    mysqli_query($conn, $query);
}

echo "Records inserted using loop";
?>

Output:

Records inserted using loop

Insert Multiple Form Records Using PHP

This method is useful when submitting dynamic form data.

<?php
$names = $_POST['name'];
$emails = $_POST['email'];
$ages = $_POST['age'];

for ($i = 0; $i < count($names); $i++) {
    $query = "INSERT INTO users (name, email, age)
              VALUES ('$names[$i]', '$emails[$i]', $ages[$i])";
    mysqli_query($conn, $query);
}

echo "Form records inserted";
?>

Output:

Form records inserted

Secure Insert Multiple Records Using Prepared Statements

Prepared statements are recommended for security.

<?php
$stmt = mysqli_prepare($conn,
    "INSERT INTO users (name, email, age) VALUES (?, ?, ?)");

mysqli_stmt_bind_param($stmt, "ssi", $name, $email, $age);

$records = [
    ['Anil', 'anil@gmail.com', 29],
    ['Kiran', 'kiran@gmail.com', 26]
];

foreach ($records as $rec) {
    $name = $rec[0];
    $email = $rec[1];
    $age = $rec[2];
    mysqli_stmt_execute($stmt);
}

echo "Multiple records inserted securely";
?>

Output:

Multiple records inserted securely

Insert Multiple Records Using MySQL Transaction

Transactions ensure data consistency.

<?php
mysqli_begin_transaction($conn);

try {
    mysqli_query($conn, "INSERT INTO users (name, email, age)
                         VALUES ('User1','u1@gmail.com',20)");
    mysqli_query($conn, "INSERT INTO users (name, email, age)
                         VALUES ('User2','u2@gmail.com',22)");

    mysqli_commit($conn);
    echo "Transaction completed successfully";
} catch (Exception $e) {
    mysqli_rollback($conn);
    echo "Transaction failed";
}
?>

Output:

Transaction completed successfully

Common Errors When Inserting Multiple Records

• Duplicate entries
• SQL syntax errors
• Missing column values
• Unvalidated user input
• Connection failures

PHP MySQL insert multiple records, PHP insert multiple rows MySQL, PHP MySQLi multiple insert, PHP loop insert MySQL, PHP prepared statement multiple insert, PHP database tutorial

Best Practices for Inserting Multiple Records

• Use prepared statements
• Use transactions for large inserts
• Validate and sanitize data
• Avoid inserting data inside large loops when possible
• Handle errors properly

Related tutorial:

• PHP MySQL Insert Data
• PHP MySQL Select Data
• PHP MySQL Update Data
• PHP MySQL Use WHERE

Frequently Asked Questions (FAQ)

1. Can PHP insert multiple records at once?

Yes, using multi-row INSERT or loops.

2. Which method is fastest?

Single SQL query with multiple values.

3. Is prepared statement safe for bulk insert?

Yes, and it is recommended.

4. Can I insert thousands of records?

Yes, but use batching and transactions.

5. Can I rollback multiple inserts?

Yes, using MySQL transactions.