pdoMultiInsert

/**
 * 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]);
    
    
    
    
    <?php
/**
 * Class mPDO
 *
 * This class extends the main PDO class by providing just one additional method
 * in order to prepare for adding multiple records at a time
 *
 * @param string $dsn as for PDO, e.g. 'mysql:host=localhost;dbname=mydb'
 * @param string optional $username as for PDO, e.g. 'root'
 * @param string optional $password as for PDO
 * @param array optional $options as for PDO
 *
 * @return mPDO object on success
 */
class mPDO extends PDO
{
   public function __construct($dsn, $username, $password, $options=null)
    {
        parent::__construct($dsn, $username, $password, $options);
        $this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('mPDOStatement', array($this)));
    }
    public function multiPrepare($sql, $data)
    {
        $rows = count($data);
        $cols = count($data[0]);
        $rowString = '(' . rtrim(str_repeat('?,', $cols), ',') . '),';
        $valString = rtrim(str_repeat($rowString, $rows), ',');
        return $this->prepare($sql . ' VALUES ' . $valString);
    }
}
/**
 * Class mPDOStatement
 *
 * This class extends the main PDOStatement class by providing just one additional method
 * in order to bind multiple records to a prepared statement in a single execution
 *
 * @param mPDO (PDO) object $dbh
 *
 * @return mPDOStatement object on success
 */
class mPDOStatement extends PDOStatement
{
    public $dbh;
    protected function __construct($dbh) {
        $this->dbh = $dbh;
    }
    public function multiExecute($data)
    {
        $bindArray = array();
        array_walk_recursive($data, function($item) use (&$bindArray) { $bindArray[] = $item; });
        $this->execute($bindArray);
    }
}

    //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();
}


<?php

//Connect to MySQL with PDO.
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');

//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);