Robert Tamayo | March 12, 2022

Inserting Thousands of Rows into a SQL Database with Prepared Statements in PHP

When you have many rows to insert into a SQL database, you probably want to batch them up into as few insert statements as possible. Adding three thousand rows one by one might cause a strain on the database server. Here's how I recently did that in PHP using PDO.

1. Split the Array of Data into Chunks

Let's say we have a database with animals, and we need to insert a bunch of new animals that were just added to a zoo. Here's an array of data that we want to convert into an insert statement.

$animals_array = [
    ['dog', 'Butler', 100],
    ['dog', 'Warrior', 150],
    ['cat', 'Thief', 20],
    ['seal', 'Smuggler', 200],
    ['hedgehog', 'Thief', 2]
];

The columns in the imaginary database are a primary index, the type of animal, the occupation of the animal, and its weight. If we were inserting thousands of these, we would want to split it up into batches, or "chunks", of a few hundred. We do that with array_chunk().

$chunked = array_chunk($animals_array, 2);

This creates 3 arrays, the first having the two dogs, the second having the cat and the seal, and the third having the hedgehog.

2. Write the Insert Statement

foreach($chunked as $chunk_key => $chunk) {
    $insert = "INSERT INTO animals (type, occupation, weight) VALUES ";
    $i = 0;
    foreach($chunk as $chunk_val) {
        $row_template = [
            ":type_$i",
            ":occupation_$i",
            ":weight_$i",
        ];
        $row_keys = implode(',', $row_template);
        $insert .= " ($row_keys),";
        $i += 1;
    }
}
$insert = trim($insert, ',');

At this point, we have an insert statement with potentially hundreds of elements, each row of which has been given a unique key for the prepared statement key that will be used in the replacement. The statement looks something like this:

INSERT INTO animals (type, occupation, weight) VALUES 
(:type_0, :occupation_0, :weight_0),
(:type_1, :occupation_1, :weight_1)

However many rows there are, each one will have a unique identifier due to the $i index.

3. Prepare the Statement

Now we need to prepare the statement.

$conn = new PDO("mysql:host=$db_host;dbname=$schema_name", $db_username, $db_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare($insert);

$i = 0;
foreach ($chunk as $chunk_val) {
    $stmt->bindParam(":type_$i", $chunk_val[0]);
    $stmt->bindParam(":occupation_$i", $chunk_val[1]);
    $stmt->bindParam(":weight_$i", $chunk_val[2]);

    $i += 1;
}

$stmt->execute();
$conn = null;

A potential readability-improvement would be to replace the "0", "1", and "2" with constants or variables representing the index of the corresponding values in the array. Either way, with this method, it's possible to insert thousands of rows into a database using a prepared statement without needing to use a separate insert statement for each row.

Comments: