CODEWITHSUNDEEP
phpmysqlpdosql-insert
user3330840
user3330840

Reputation: 7369

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

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?

Upvotes: 19

Views: 14348

Answers (4)

sonofkrish
sonofkrish

Reputation: 91

/* 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.

Upvotes: 0

Iazel
Iazel

Reputation: 2336

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.

Upvotes: 4

Joel Lubrano
Joel Lubrano

Reputation: 606

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.

Upvotes: 18

Bill Karwin
Bill Karwin

Reputation: 562260

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.

Upvotes: 8

Related Questions