Mysql allows you to issue a single INSERT query with multiple records at once. This can be more efficient than single row inserts in a loop, especially when the database server is several milliseconds of round-trip-time away.
INSERT INTO database.tablename
(field1, field2)
VALUES
(val1_1, val1_2),
(val2_1, val2_2),
(val_3_1, val3_2)
...It’s not so obvious how to accomplish a similar kind of query when using PDO in php while maintaining save query practices. Here I share a solution I had to devise recently to solve this problem. It is presented as a function that takes 5 arguments:
//This block is not valid php. I've written it this way to better illustrate the
//variable types.
PDO $db; //the PDO database handle to use.
// the name of the table into which you wish to batch-insert records
string $tableName;
string[] $fieldList; //the list of fields you will be setting.
/**
a two-dimensional array of records. Each entry in the array is itself an associative array which represents a table row. The keys of the rows must match the entries in the $fieldList you supplied above.
*/
array<array<string,string>> $valueList;
/**
how many rows you wish to insert for each query execution. Dial this up or down to improve efficiency at the cost of a bigger query. The maximum number will depend on your system parameters.
*/
int $batchSize = 50; Here is the code
<?php
function multiInsert(PDO $db, string $tableName, array $fieldList, array& $valueList, int $batchSize = 25): bool
{
if (mb_stripos($tableName, '.') === false) {
throw new Exception('You must supply a fully qualified table name.');
}
if ($batchSize <= 1) {
throw new Exception('batchSize must be at least 2');
}
//generate the INSERT query
$insertFieldClause = implode(",\n", $fieldList);
$queryPrefix = "INSERT INTO {$tableName} (
$insertFieldClause
)";
$fieldCount = count($fieldList);
$valueCount = count($valueList);
if ($valueCount === 0) {
throw new Exception('valueList cannot be empty');
}
$pos = 0;
do {
$offset = $pos * $batchSize;
$paramPlaceholders = []; //hold the PDO named parameter placeholders
$paramValues = []; // hold the PDO parameters needed to execute the query
for ($i = $offset; $i < ($offset + $batchSize); $i++) {
$row = $valueList[$i];
if ($i >= $valueCount) { //stop once you've exhausted the values list.
break;
}
$singleRow = [];
foreach ($fieldList as $field) {
if (!is_string($field)){
throw new Exception('Field names must be strings');
}
if (is_numeric($field[0])) {
throw new Exception('Field names must not start with a number');
}
if (!array_key_exists($field, $row)) {
throw new Exception("row $i of valueList does not contain the key: $field");
}
$p = ":{$field}_{$i}"; //generate the placeholder
/*
each indexed placeholder goes into an array until we have
count($fieldList) of them.
*/
$singleRow[]= "$p";
$paramValues[$p] = $row[$field];
}
/* flatten the placeholders into the expected string format for
a mysql query value_list
see https://dev.mysql.com/doc/refman/8.0/en/insert.html for
guidance on the syntax.*/
$iv = "\n(" . implode(",\n", $singleRow) . ")";
/* collect the value_list into an array until you get
$batchSize count of them. */
$paramPlaceholders[] = $iv;
}
/*
now convert the mysql value_list into a flat string of the
form: (:val1_1, :val1_2), (val2_1, val2_2) ...
implode() is a handy way of doing this.
*/
$valuesClause = implode(",\n", $paramPlaceholders);
//concatenate the query prefix with the value_list we just constructed.
$query = $queryPrefix . ' VALUES ' . $valuesClause;
//echo $query; //uncomment this if you want to preview the query
//prepare and execute!
$db->prepare($query);
$db->execute($paramValues);
$pos++;
} while ($pos < ceil($valueCount / $batchSize));
return true;
}Usage/Illustration
//suppose the function is called with fieldList and valueList as below:
$fieldList = [
'field1',
'field2'
];
$valueList = [
['field1' => 23, 'field2' => 'Oranges'],
['field1' => 40, 'field2' => 'Mangoes'],
['field1' => 13, 'field2' => 'Grapes'
];
//generated query will look like this.
INSERT INTO database.tableName (
field1,
field2
) VALUES
(:field1_0, :field2_0),
(:field1_1, :field2_1),
(:field1_2, :field2_2)
//also parameters list will be of the form
$paramValues = [
':field1_0' => 23,
':field2_0' => 'Oranges',
':field1_1' => 40,
':field2_1' => 'Mangoes',
':field1_2' => 13,
':field2_2' => 'Grapes'
];I hope you find this a useful source of inspiration when faced with a similar task. Let me know in the comments if you spot a mistake.