Mysql Multi-Value Inserts with PHP and PDO

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)
(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

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} (

        $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.

                $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 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!

        } while ($pos < ceil($valueCount / $batchSize));

        return true;


//suppose the function is called with fieldList and valueList as below:
$fieldList = [

$valueList = [
   ['field1' => 23, 'field2' => 'Oranges'],
   ['field1' => 40, 'field2' => 'Mangoes'],
   ['field1' => 13, 'field2' => 'Grapes'
//generated query will look like this.
INSERT INTO database.tableName (
(: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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.