• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Insert Multiple

← Previous Next →

When building PHP applications, you often need to insert multiple records into a database at once. Inserting multiple rows in a single query is more efficient than executing separate queries for each record. This improves performance and reduces the load on your server.

This tutorial explains how to insert multiple records into a MySQL table using SQL commands, PHP MySQLi, and PDO, along with prepared statements and best practices.

What is Multiple Inserts?

Inserting multiple rows at once allows you to add several records without repeating SQL commands. Instead of executing multiple INSERT statements, you can write a single query that adds all the rows simultaneously.

For example, consider a students table:

id name age email
1 Neha 16 neha@example.com
2 Anika 17 anika@example.com

To insert multiple students efficiently, you can use a single INSERT query.

Inserting Multiple Rows Using SQL

The basic SQL syntax for inserting multiple rows is:

INSERT INTO students (name, age, email) VALUES
('Riya', 16, 'riya@example.com'),
('Sanya', 18, 'sanya@example.com'),
('Meera', 17, 'meera@example.com');

Explanation:

  • INSERT INTO students (name, age, email) specifies the table and columns.

  • Each set of values (...) represents one row.

  • Multiple sets are separated by commas.

This method reduces the number of queries executed, improving efficiency, especially for large datasets.

Using PHP MySQLi to Insert Multiple Rows

Procedural Style

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "school_db";

$conn = mysqli_connect($servername, $username, $password, $database);
if (!$conn) die("Connection failed: " . mysqli_connect_error());

$sql = "INSERT INTO students (name, age, email) VALUES
('Riya', 16, 'riya@example.com'),
('Sanya', 18, 'sanya@example.com'),
('Meera', 17, 'meera@example.com')";

if (mysqli_query($conn, $sql)) {
    echo "Multiple records inserted successfully!";
} else {
    echo "Error inserting records: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Object-Oriented Style

<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

$sql = "INSERT INTO students (name, age, email) VALUES
('Riya', 16, 'riya@example.com'),
('Sanya', 18, 'sanya@example.com'),
('Meera', 17, 'meera@example.com')";

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

$conn->close();
?>

Using PDO to Insert Multiple Rows

PDO allows multiple inserts using exec() for simple queries or prepared statements for dynamic data.

Static Multiple Insert

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

    $sql = "INSERT INTO students (name, age, email) VALUES
    ('Riya', 16, 'riya@example.com'),
    ('Sanya', 18, 'sanya@example.com'),
    ('Meera', 17, 'meera@example.com')";

    $conn->exec($sql);
    echo "Multiple records inserted successfully!";
} catch(PDOException $e) {
    echo "Error inserting records: " . $e->getMessage();
}

$conn = null;
?>

Using Prepared Statements for Dynamic Multiple Insert

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

    $students = [
        ['Neha', 16, 'neha@example.com'],
        ['Anika', 17, 'anika@example.com'],
        ['Riya', 16, 'riya@example.com']
    ];

    $stmt = $conn->prepare("INSERT INTO students (name, age, email) VALUES (:name, :age, :email)");

    foreach ($students as $student) {
        $stmt->bindParam(':name', $student[0]);
        $stmt->bindParam(':age', $student[1]);
        $stmt->bindParam(':email', $student[2]);
        $stmt->execute();
    }

    echo "Multiple records inserted successfully!";
} catch(PDOException $e) {
    echo "Error inserting records: " . $e->getMessage();
}

$conn = null;
?>

Explanation:

  • Loop through an array of student data.

  • Bind parameters for each student.

  • Execute the statement in each iteration.

  • This approach is safe and prevents SQL injection.

Best Practices

  1. Use prepared statements when dealing with dynamic input.

  2. Validate data before insertion.

  3. Batch inserts are more efficient than multiple single inserts.

  4. Handle exceptions gracefully to catch errors.

  5. Close connections after insertion to free resources.

Common Errors

Error Cause Solution
Syntax error Missing commas or brackets Check SQL syntax carefully
Duplicate entry Primary key conflict Ensure AUTO_INCREMENT or unique values
Data type mismatch Inserting wrong data type Validate data types before insert
Connection failed Wrong credentials or host Check server, username, password

Summary of the Tutorial

  • Inserting multiple records reduces server load and improves performance.

  • Use SQL multiple insert syntax for static data.

  • Use PHP MySQLi or PDO to insert dynamic data safely.

  • Prepared statements protect against SQL injection and are essential for user input.

  • Proper error handling and validation ensure smooth insertion operations.

This method is widely used in applications like bulk uploads, school records, and e-commerce products where multiple records need to be added simultaneously.

Practice Questions

  1. Write a SQL command to insert three students into the students table in a single query.

  2. Write a PHP MySQLi procedural script to insert five records into the students table at once.

  3. Write a PHP MySQLi object-oriented script to insert multiple records into the teachers table.

  4. Write a PDO PHP script to insert multiple rows into the courses table.

  5. Create an array of student data and use a PDO prepared statement to insert all students into the table using a loop.

  6. Explain why using a single SQL query for multiple inserts is more efficient than multiple individual queries.

  7. Modify a PDO script to handle errors while inserting multiple rows dynamically.

  8. Write a PHP script that inserts multiple products into a products table and prints “Records inserted successfully” after completion.

  9. Explain the difference between static multiple insert and dynamic multiple insert using prepared statements.

  10. Write a PHP script that inserts multiple rows into a table but ensures no duplicate email addresses are inserted.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

PHP

online coding class codepractice

PHP Basics

PHP Data

Control Flow

PHP Functions

PHP Arrays

PHP Forms

PHP Advanced

Cookies & Sessions

Filters & JSON

PHP Exceptions

PHP OOP

MySQL with PHP

PHP and XML

AJAX with PHP

codepractice logo
Login
Go Back Top