PHP MySQL: Insert Multiple Records

Welcome to The Coding College! In this tutorial, we’ll explore how to insert multiple records into a MySQL database using PHP. Inserting multiple rows in one query is a great way to optimize your database operations and improve performance.

Why Insert Multiple Records at Once?

Inserting multiple records in a single query has several benefits:

Performance Boost: Fewer queries mean less overhead and faster execution.
Code Simplification: Instead of writing separate INSERT statements, you can handle everything with one query.
Efficient Database Interactions: Minimizes network latency and improves database performance.

Syntax for Inserting Multiple Records in MySQL

Here’s the syntax for inserting multiple rows in one INSERT statement:

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

Now, let’s implement this in PHP using MySQLi and PDO.

Example: Insert Multiple Records Using PHP

1. Using MySQLi (Procedural)

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

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

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// SQL to insert multiple records
$sql = "INSERT INTO Users (username, email, age) VALUES 
    ('JohnDoe', '[email protected]', 25),
    ('JaneDoe', '[email protected]', 30),
    ('AliceDoe', '[email protected]', 22)";

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

// Close connection
mysqli_close($conn);
?>

2. Using MySQLi (Object-Oriented)

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

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

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

// SQL to insert multiple records
$sql = "INSERT INTO Users (username, email, age) VALUES 
    ('MarkSmith', '[email protected]', 35),
    ('EmilyJones', '[email protected]', 29),
    ('DavidBrown', '[email protected]', 40)";

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

// Close connection
$conn->close();
?>

3. Using PDO

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

try {
    // Create a PDO instance
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL to insert multiple records
    $sql = "INSERT INTO Users (username, email, age) VALUES 
        ('ChrisGreen', '[email protected]', 28),
        ('SarahWhite', '[email protected]', 34),
        ('MichaelBlack', '[email protected]', 31)";

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

// Close connection
$conn = null;
?>

Using Prepared Statements for Security

When working with user input, it’s crucial to use prepared statements to avoid SQL injection. Below is an example of inserting multiple records with prepared statements:

MySQLi (Prepared Statements)

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

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

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Prepare statement
$stmt = $conn->prepare("INSERT INTO Users (username, email, age) VALUES (?, ?, ?)");
$stmt->bind_param("ssi", $username, $email, $age);

// Insert multiple records
$users = [
    ['ChrisRed', '[email protected]', 27],
    ['EmmaStone', '[email protected]', 32],
    ['LukeSky', '[email protected]', 35]
];

foreach ($users as $user) {
    $username = $user[0];
    $email = $user[1];
    $age = $user[2];
    $stmt->execute();
}

echo "Multiple records inserted successfully!";

// Close statement and connection
$stmt->close();
$conn->close();
?>

PDO (Prepared Statements)

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

try {
    // Create a PDO instance
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Prepare statement
    $stmt = $conn->prepare("INSERT INTO Users (username, email, age) VALUES (:username, :email, :age)");

    // Insert multiple records
    $users = [
        ['username' => 'JohnBlue', 'email' => '[email protected]', 'age' => 29],
        ['username' => 'SarahGreen', 'email' => '[email protected]', 'age' => 31],
        ['username' => 'TomBrown', 'email' => '[email protected]', 'age' => 33]
    ];

    foreach ($users as $user) {
        $stmt->bindParam(':username', $user['username']);
        $stmt->bindParam(':email', $user['email']);
        $stmt->bindParam(':age', $user['age']);
        $stmt->execute();
    }

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

// Close connection
$conn = null;
?>

Advantages of Prepared Statements for Multiple Records

Security: Protects against SQL injection attacks.
Reusability: The prepared statement is reused for each record.
Performance: The database only parses the query once, improving efficiency.

Common Errors and Solutions

Duplicate Entries:
- Ensure unique columns like primary keys or UNIQUE fields are not being duplicated.
Incorrect SQL Syntax:
- Verify that commas between value sets in the VALUES clause are placed correctly.
Connection Issues:
- Always check for connection errors before executing SQL queries.

Conclusion

Inserting multiple records in PHP is a simple yet powerful way to optimize your database interactions. Whether you use plain SQL or prepared statements, you can improve performance and ensure security.

For more tutorials on PHP and MySQL, visit The Coding College and enhance your coding skills with us.