Introduction: Why Insert Multiple Rows in PHP Matters

When working with data-driven web applications, it's common to insert multiple records at once—whether importing a CSV, submitting a multi-entry form, or logging bulk user actions.

Inserting each record one by one can be inefficient and slow, especially as your data grows. Instead, batch inserting multiple rows in a single query is faster and more resource-efficient.

This guide covers how to insert multiple rows into a MySQL table using PHP, with examples using MySQLi and PDO, plus real-world performance tips.

Prerequisites

Before we begin, make sure you have:

• PHP installed (7.4+ recommended)

• MySQL or MariaDB running

• A database and table created

• A server environment (like XAMPP, WAMP, or MAMP)

Here’s a basic table to use:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

MySQL Syntax for Inserting Multiple Rows

MySQL allows inserting multiple rows like this:

INSERT INTO table_name (column1, column2)
VALUES 
  (value1a, value2a),
  (value1b, value2b),
  (value1c, value2c);

This reduces the number of queries and improves performance.

Insert Multiple Rows Using PHP MySQLi

✅ Basic Example (Non-Prepared)

<?php
$conn = new mysqli("localhost", "root", "", "test_db");

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

// Build multi-row INSERT query
$sql = "INSERT INTO products (name, price) VALUES 
        ('Product A', 19.99),
        ('Product B', 29.99),
        ('Product C', 39.99)";

if ($conn->query($sql) === TRUE) {
    echo "Multiple rows inserted successfully!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

⚠️ Warning: This method works but is vulnerable to SQL injection. Use prepared statements for dynamic user data.

MySQLi with Prepared Statements in a Loop

<?php
$conn = new mysqli("localhost", "root", "", "test_db");

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

$data = [
    ['Product X', 49.99],
    ['Product Y', 59.99],
    ['Product Z', 69.99]
];

$stmt = $conn->prepare("INSERT INTO products (name, price) VALUES (?, ?)");
$stmt->bind_param("sd", $name, $price);

foreach ($data as $row) {
    $name = $row[0];
    $price = $row[1];
    $stmt->execute();
}

echo "All rows inserted securely!";
$stmt->close();
$conn->close();
?>

✅ Use this for user-generated content or dynamic form data.

Insert Multiple Rows Using PHP PDO

✅ Batch Insert with exec()

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "INSERT INTO products (name, price) VALUES 
            ('Item A', 9.99),
            ('Item B', 14.99),
            ('Item C', 19.99)";
    
    $pdo->exec($sql);
    echo "Batch insert with PDO successful!";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Secure PDO Insert with prepare()

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $data = [
        ['Keyboard', 99.99],
        ['Mouse', 49.99],
        ['Monitor', 199.99]
    ];

    $stmt = $pdo->prepare("INSERT INTO products (name, price) VALUES (:name, :price)");

    foreach ($data as $row) {
        $stmt->execute([
            ':name' => $row[0],
            ':price' => $row[1]
        ]);
    }

    echo "Secure multiple row insert complete!";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Complete Functional Example: Insert from an Array

<?php
$products = [
    ['Desk Lamp', 24.99],
    ['Table', 150.00],
    ['Chair', 85.50]
];

$conn = new mysqli("localhost", "root", "", "test_db");

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

$stmt = $conn->prepare("INSERT INTO products (name, price) VALUES (?, ?)");
$stmt->bind_param("sd", $name, $price);

foreach ($products as $item) {
    $name = $item[0];
    $price = $item[1];
    $stmt->execute();
}

echo "Products inserted successfully!";
$stmt->close();
$conn->close();
?>

Tips & Common Pitfalls

✅ Best Practices

• Use prepared statements for dynamic/multiple inputs.

• Validate data before insertion to avoid corrupt records.

• Keep your queries under MySQL’s max_allowed_packet size.

• Use transactions for atomic multi-row inserts (especially in PDO).

❌ Common Pitfalls

Feature Comparison: MySQLi vs PDO for Multi-Row Insert

Conclusion: Final Thoughts & Best Practices

Efficiently inserting multiple rows into a MySQL database using PHP is crucial for performance, scalability, and data integrity. Whether you're working with form data, API imports, or batch jobs—knowing the right method matters.

Key Takeaways:

• Use a single SQL query for small fixed sets of data.

• Use prepared statements in a loop for dynamic or user-submitted data.

• Choose PDO for scalable, multi-database support with transactions.

• Validate, sanitize, and chunk data where necessary.