PHP: Insert multiple rows with PDO.

This PHP tutorial will show you how to insert multiple rows using the PDO object.

We will be using prepared statements to carry out a bulk insert. Not only is this method safer, but it is also significantly faster than running multiple queries.

Inserting multiple rows with the PDO object.

A quick and easy way to insert multiple table rows is to use PDO’s positional placeholders:

//Array containing the rows we want to insert
$rows = array(
    array(
        'John',
        '1993-01-04',
    ),
    array(
        'Jane',
        '1987-06-14',
    ),
    array(
        'Joe',
        '1989-09-29',
    )
);

//Create positional placeholders (?)
$numPlaceholders = count($rows[0]) - 1;
$placeholders = str_repeat('?,', $numPlaceholders) . '?';

//Create the INSERT SQL query
$sql = "INSERT INTO people (name, dob) VALUES ";

//Add our placeholders to the SQL
$sql .= str_repeat("($placeholders),", count($rows) - 1) . "($placeholders)";

//It now looks like:
//INSERT INTO people (name, dob) VALUES (?,?,?),(?,?,?),(?,?,?)

//Prepare the SQL statement
$statement = $pdo->prepare($sql);

//Flatten the $rows array
$rows = array_merge(...$rows);

//Execute the statement and insert the rows
$statement->execute($rows);

You can also use the shortened version of this code:

//Create positional placeholders (?)
$placeholders = str_repeat('?,', (count($rows[0]) - 1)) . '?';

//Create the INSERT SQL query
$sql = "INSERT INTO people (name, dob) VALUES " . str_repeat("($placeholders),", count($rows) - 1) . "($placeholders)";

//Prepare and execute the SQL statement
$statement = $pdo->prepare($sql)->execute(array_merge(...$rows));

Note that the examples above will only work if your $rows array has numerical keys, as the array_merge method above will not flatten associative arrays.

If you’re adamant about keeping your associative array, then you may want to use the custom PHP function below.

Inserting rows with an associative PHP array and named placeholders.

For this example, we’ve created a custom function that will “bulk insert” an associative PHP array into a table.

Note that the $data array in this function should never come from an external source. This function presumes that you will construct the $data array yourself or, at the very least, whitelist the key/column names.

/**
 * A custom function that automatically constructs a multi insert statement.
 * 
 * @param string $tableName Name of the table we are inserting into.
 * @param array $data An "array of arrays" containing our row data.
 * @param PDO $pdoObject Our PDO object.
 * @return boolean TRUE on success. FALSE on failure.
 */
function pdoMultiInsert($tableName, $data, $pdoObject){
    
    //Will contain SQL snippets.
    $rowsSQL = array();

    //Will contain the values that we need to bind.
    $toBind = array();
    
    //Get a list of column names to use in the SQL statement.
    $columnNames = array_keys($data[0]);

    //Loop through our $data array.
    foreach($data as $arrayIndex => $row){
        $params = array();
        foreach($row as $columnName => $columnValue){
            $param = ":" . $columnName . $arrayIndex;
            $params[] = $param;
            $toBind[$param] = $columnValue; 
        }
        $rowsSQL[] = "(" . implode(", ", $params) . ")";
    }

    //Construct our SQL statement
    $sql = "INSERT INTO `$tableName` (" . implode(", ", $columnNames) . ") VALUES " . implode(", ", $rowsSQL);

    //Prepare our PDO statement.
    $pdoStatement = $pdoObject->prepare($sql);

    //Bind our values.
    foreach($toBind as $param => $val){
        $pdoStatement->bindValue($param, $val);
    }
    
    //Execute our statement (i.e. insert the data).
    return $pdoStatement->execute();
}

The function above takes in three parameters:

$tableName: The name of the database table.
$data: The rows that you are inserting. In this case, it is a multi-dimensional array, with each array representing a row.
$pdoObject: Your PDO object.

Below is an example of this function being used:

//An array of arrays, containing the rows that we want to insert.
$rowsToInsert = array(
    array(
        'name' => 'John Doe',
        'dob' => '1993-01-04',
    ),
    array(
        'name' => 'Jane Doe',
        'dob' => '1987-06-14',
    ),
    array(
        'name' => 'Joe Bloggs',
        'dob' => '1989-09-29',
    )
);

//An example of adding to our "rows" array on the fly.
$rowsToInsert[] = array(
    'name' => 'Patrick Simmons',
    'dob' => '1972-11-12'
);

//Call our custom function.
pdoMultiInsert('people', $rowsToInsert, $pdo);

As you can see, we create a multidimensional array that contains associative arrays representing our table rows. We then supply that array to our custom function, which carries out the bulk insert.