Doing MySQL insert from a PHP loop the right way

Effectively manage PHP MySQL inserts from a large loop using the PDO connection.

The key is to avoid committing data on each loop, this is done using a transaction.

Use a transaction

Using beginTransaction and commit means that auto-commit is turned off and the changes made to the database won’t be committed until commit is called.

This method means that on each loop there won’t be an INSERT and re-index of the table, this gets done once at the end on the commit being called.

Be careful with this method because if one loop has an issue the whole INSERT loop will be rolled back and won’t have been a success.

$db = new PDO("mysql:host=127.0.0.1;dbname=test;charset=utf8mb4", 'USER', 'PASS');

$db->beginTransaction();

$insert = $db->prepare('INSERT INTO `objects` (`color`) VALUES (?);');

$test_array = ['Red', 'Orange', 'Pink', 'Lime', 'Yellow', 'Gold', 'Green', 'Blue', 'Purple', 'Maroon', 'Silver', 'Aqua'];

foreach ($test_array as $c) {
    $insert->execute([$c]);
}

$db->commit();

The array used in the example above is very small, with benefits been seen on loops in the several hundreds and above.

A common method that works but is not efficient:

$db = new PDO("mysql:host=127.0.0.1;dbname=test;charset=utf8mb4", 'USER', 'PASS');

$test_array = ['Red', 'Orange', 'Pink', 'Lime', 'Yellow', 'Gold', 'Green', 'Blue', 'Purple', 'Maroon', 'Silver', 'Aqua'];

foreach ($test_array as $c) {
    $insert = $db->prepare('INSERT INTO `objects` (`color`) VALUES (?);');
    $insert->execute([$c]);
}

This will be inserting on every single loop, with checks on indexes and keys each time.