What is the best way to insert multiple rows in PHP PDO MYSQL?

Question

Say, we have multiple rows to be inserted in a table:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ] //[ array of values ];

Using PDO:

$sql = "insert into `table_name` (col1, col2, col3) values (?, ?, ?)" ;

Now, how should you proceed in inserting the rows? Like this?

$stmt = $db->prepare($sql);

foreach($rows as $row){
  $stmt->execute($row);
}

or, like this?

$sql = "insert into `table_name` (col1, col2, col3) values ";
$sql .= //not sure the best way to concatenate all the values, use implode?
$db->prepare($sql)->execute();

Which way would be faster and safer? What is the best way to insert multiple rows?

Answer 1

You have at least these two options:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values (?,?,?)";

$stmt = $db->prepare($sql);

foreach($rows as $row)
{
    $stmt->execute($row);
}

OR:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values ";

$paramArray = array();

$sqlArray = array();

foreach($rows as $row)
{
    $sqlArray[] = '(' . implode(',', array_fill(0, count($row), '?')) . ')';

    foreach($row as $element)
    {
        $paramArray[] = $element;
    }
}

// $sqlArray will look like: ["(?,?,?)", "(?,?,?)", ... ]

// Your $paramArray will basically be a flattened version of $rows.

$sql .= implode(',', $sqlArray);

$stmt = $db->prepare($sql);

$stmt->execute($paramArray);

As you can see the first version features a lot simpler code; however the second version does execute a batch insert. The batch insert should be faster, but I agree with @BillKarwin that the performance difference will not be noticed in the vast majority of implementations.

Answer 2

I would do it the first way, prepare the statement with one row of parameter placeholders, and insert one row at a time with execute.

$stmt = $db->prepare($sql);

foreach($rows as $row){
    $stmt-> execute($row);
}

It's not quite as fast as doing multiple rows in a single insert, but it's close enough that you will probably never notice the difference.

And this has the advantage that it's very easy to work with the code. That's why you're using PHP anyway, for the developer efficiency, not the runtime efficiency.

If you have many rows (hundreds or thousands), and performance is a priority, you should consider using LOAD DATA INFILE.

Answer 3

You can also go this way:

<?php
$qmarks = '(?,?,?)'. str_repeat(',(?,?,?)', count($rows)-1);

$sql = "INSERT INTO `table`(col1,col2,col3) VALUES $qmarks";
$vals = array();
foreach($rows as $row)
    $vals = array_merge($vals, $row);
$db->prepare($sql)->execute($vals);

To be honest, I don't know which one will be faster, all depends on the delay between mysql and the php server.

Answer 4

/* test.php */

<?php
require_once('Database.php');

$obj = new Database();
$table = "test";

$rows = array(
    array(
    'name' => 'balasubramani',
    'status' => 1
    ),
    array(
    'name' => 'balakumar',
    'status' => 1
    ),
    array(
    'name' => 'mani',
    'status' => 1
    )
);

var_dump($obj->insertMultiple($table,$rows));
?>

/* Database.php */
<?php
class Database 
{

    /* Initializing Database Information */

    var $host = 'localhost';
    var $user = 'root';
    var $pass = '';
    var $database = "database";
    var $dbh;

    /* Connecting Datbase */

    public function __construct(){
        try {
            $this->dbh = new PDO('mysql:host='.$this->host.';dbname='.$this->database.'', $this->user, $this->pass);
            //print "Connected Successfully";
        } 
        catch (PDOException $e) {
            print "Error!: " . $e->getMessage() . "<br/>";
            die();
        }
    }
/* Insert Multiple Rows in a table */

    public function insertMultiple($table,$rows){

        $this->dbh->beginTransaction(); // also helps speed up your inserts.
        $insert_values = array();
        foreach($rows as $d){
            $question_marks[] = '('  . $this->placeholders('?', sizeof($d)) . ')';
            $insert_values = array_merge($insert_values, array_values($d));
            $datafields = array_keys($d);
        }

        $sql = "INSERT INTO $table (" . implode(",", $datafields ) . ") VALUES " . implode(',', $question_marks);

        $stmt = $this->dbh->prepare ($sql);
        try {
            $stmt->execute($insert_values);
        } catch (PDOException $e){
            echo $e->getMessage();
        }
        return $this->dbh->commit();
    }

    /*  placeholders for prepared statements like (?,?,?)  */

    function placeholders($text, $count=0, $separator=","){
        $result = array();
        if($count > 0){
            for($x=0; $x<$count; $x++){
                $result[] = $text;
            }
        }

        return implode($separator, $result);
    }

}
?>

The above code should be good solution for Inserting Multiple Records using PDO.