MySQL Insert Multiple

PHP Insert Multiple Records Into MySQL

Inserting Multiple Records Efficiently

When you need to insert multiple rows into a MySQL table using PHP, there are several approaches. Modern best practices prioritize security (preventing SQL injection) and efficiency. We’ll explore methods using both MySQLi and PDO extensions.

Key Considerations:

• Security: Always use prepared statements to prevent SQL injection vulnerabilities.
• Efficiency: For a large number of inserts, inserting multiple rows in a single query or using transactions can be significantly faster than executing individual insert statements.
• Atomicity: If all inserts must succeed or fail together (e.g., transferring funds), use database transactions (primarily shown with PDO).

Method 1: MySQLi with Prepared Statements (Loop)

This method prepares an INSERT statement once and executes it multiple times with different data. It’s secure and relatively straightforward.

<?php
$servername = "localhost";
$username = "your_username"; // Replace with your DB username
$password = "your_password"; // Replace with your DB password
$dbname = "myDB";         // Replace with your DB name

// Enable error reporting for mysqli
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    // Create connection (Object-Oriented style)
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Prepare the SQL statement (avoids SQL injection)
    $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");

    // Data to insert
    $guests = [
        ['John', 'Doe', 'john.doe@example.com'],
        ['Mary', 'Moe', 'mary.moe@example.com'],
        ['Julie', 'Dooley', 'julie.dooley@example.com']
    ];

    echo "Attempting to insert records...<br>";

    // Bind parameters and execute for each guest
    foreach ($guests as $guest) {
        $firstname = $guest[0];
        $lastname = $guest[1];
        $email = $guest[2];

        // Bind variables to the prepared statement as parameters
        // 'sss' specifies the types of the parameters (string, string, string)
        $stmt->bind_param("sss", $firstname, $lastname, $email);

        // Execute the statement
        if ($stmt->execute()) {
            echo "New record created successfully for $firstname $lastname.<br>";
        }
        // Error handling is largely covered by mysqli_report setting above
        // but you could add specific checks here if needed.
    }

    echo "Finished inserting records.<br>";

    // Close statement
    $stmt->close();

} catch (mysqli_sql_exception $e) {
    // Catch connection or query errors
    die("Database error: " . $e->getMessage() . " (Error code: " . $e->getCode() . ")");
} finally {
    // Always close the connection if it was opened
    if (isset($conn)) {
        $conn->close();
    }
}
?>

Method 2: PDO with Transactions and Prepared Statements

PDO (PHP Data Objects) provides a consistent interface for accessing various databases. Using transactions ensures that all inserts are treated as a single atomic unit – they either all succeed, or none are saved if an error occurs.

<?php
$servername = "localhost";
$username = "your_username"; // Replace with your DB username
$password = "your_password"; // Replace with your DB password
$dbname = "myDBPDO";      // Replace with your DB name

try {
    // Create PDO connection
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Data to insert
    $guests = [
        ['Peter', 'Griffin', 'peter@example.com'],
        ['Lois', 'Griffin', 'lois@example.com'],
        ['Chris', 'Griffin', 'chris@example.com']
    ];

    // Begin the transaction
    $conn->beginTransaction();
    echo "Transaction started. Attempting inserts...<br>";

    // Prepare the SQL statement (outside the loop for efficiency)
    $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (:firstname, :lastname, :email)");

    // Bind parameters and execute for each guest
    foreach ($guests as $guest) {
        $stmt->bindParam(':firstname', $guest[0]);
        $stmt->bindParam(':lastname', $guest[1]);
        $stmt->bindParam(':email', $guest[2]);
        $stmt->execute();
        echo "Executing insert for " . htmlspecialchars($guest[0]) . "...<br>";
    }

    // Commit the transaction if all inserts were successful
    $conn->commit();
    echo "Transaction committed successfully. All records inserted.<br>";

} catch (PDOException $e) {
    // Roll back the transaction if something failed
    if ($conn->inTransaction()) {
        $conn->rollBack();
        echo "Transaction rolled back due to error.<br>";
    }
    die("Database error: " . $e->getMessage());

} finally {
    // Close connection
    $conn = null;
}
?>

Method 3: Single INSERT with Multiple VALUES (MySQLi/PDO)

For inserting a known, moderate number of rows, constructing a single INSERT statement with multiple value sets can be the most performant method. However, constructing the query string and parameter bindings dynamically requires care.

<?php
// --- Connection setup like in previous examples (MySQLi or PDO) ---
// Using PDO for this example:
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "myDBPDO";

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

    // Data to insert
    $guests = [
        ['Stewie', 'Griffin', 'stewie@example.com'],
        ['Brian', 'Griffin', 'brian@example.com'],
    ];

    if (!empty($guests)) {
        // Start building the query
        $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ";
        $placeholders = [];
        $values = [];

        // Create placeholders for each row (?, ?, ?)
        foreach ($guests as $guest) {
            $placeholders[] = '(?, ?, ?)';
            // Flatten the guest array into the values array
            array_push($values, ...$guest);
        }

        // Combine placeholders: (?, ?, ?), (?, ?, ?), ...
        $sql .= implode(', ', $placeholders);

        // Prepare the statement
        $stmt = $conn->prepare($sql);

        // Execute with the flattened array of values
        $stmt->execute($values);

        $count = count($guests);
        echo "Successfully inserted $count records in a single query.<br>";
    } else {
        echo "No guest data provided.<br>";
    }

} catch (PDOException $e) {
    die("Database error: " . $e->getMessage());
} finally {
    $conn = null;
}
?>

Choosing the Right Method:

• Prepared Statements (Loop): Good general-purpose method, secure, easy to understand.
• PDO Transactions: Best when atomicity is required (all or nothing). Also improves performance for many inserts compared to auto-commit mode.
• Single Multi-Value INSERT: Often the fastest for a known set of rows, but requires more complex query building.

Deprecated Method (Avoid): mysqli_multi_query()

While mysqli_multi_query() exists and allows executing multiple SQL statements concatenated in a single string, it is strongly discouraged for inserting data unless you are absolutely certain all data is properly escaped. It opens significant risks for SQL injection if user input is involved and not handled with extreme care. Prepared statements are the safer and generally preferred approach.