PHP Pdo Insert Batch Multiple Rows with Placeholders

PDO Prepared Inserts multiple rows in single query

Multiple Values Insert with PDO Prepared Statements

Inserting multiple values in one execute statement. Why because according to this page it is faster than regular inserts.

$datafields = array('fielda', 'fieldb', ... );

$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);
$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);

more data values or you probably have a loop that populates data.

With prepared inserts you need to know the fields you're inserting to, and the number of fields to create the ? placeholders to bind your parameters.

insert into table (fielda, fieldb, ... ) values (?,?...), (?,?...)....

That is basically how we want the insert statement to look like.

Now, the code:

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

    return implode($separator, $result);
}

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

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

$stmt = $pdo->prepare ($sql);
$stmt->execute($insert_values);
$pdo->commit();

Although in my test, there was only a 1 sec difference when using multiple inserts and regular prepared inserts with single value.

php PDO insert batch multiple rows with placeholders

First of all, ? symbols are real place-holders (most drivers allow to use both syntaxes, positional and named place-holders). Secondly, prepared statements are nothing but a tool to inject raw input into SQL statements—the syntax of the SQL statement itself is unaffected. You already have all the elements you need:

• How to insert multiple rows with a single query
• How to generate SQL dynamically
• How to use prepared statements with named place-holders.

It's fairly trivial to combine them all:

$sql = 'INSERT INTO table (memberID, programID) VALUES ';
$insertQuery = [];
$insertData = [];
$n = 0;
foreach ($data as $row) {
    $insertQuery[] = '(:memberID' . $n . ', :programID' . $n . ')';
    $insertData['memberID' . $n] = $memberid;
    $insertData['programID' . $n] = $row;
    $n++;
}

if (!empty($insertQuery)) {
    $sql .= implode(', ', $insertQuery);
    $stmt = $db->prepare($sql);
    $stmt->execute($insertData);
}

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

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.

PDO MySQL: Insert multiple rows in one query

An easy way for this avoiding the complications would be something like this

$stmt = $pdo->prepare('INSERT INTO foo VALUES(:a, :b, :c)');
foreach($data as $item)
{
    $stmt->bindValue(':a', $item[0]);
    $stmt->bindValue(':b', $item[1]);
    $stmt->bindValue(':c', $item[2]);
    $stmt->execute();
}

However, this executes the statement multiple times. So, it is better if we create a long single query in order to do this.

Here is an example of how we can do this.

$query = "INSERT INTO foo (key1, key2) VALUES "; //Prequery
$qPart = array_fill(0, count($data), "(?, ?)");
$query .=  implode(",",$qPart);
$stmt = $dbh -> prepare($query); 
$i = 1;
foreach($data as $item) { //bind the values one by one
   $stmt->bindValue($i++, $item['key1']);
   $stmt->bindValue($i++, $item['key2']);
}
$stmt -> execute(); //execute

Bulk insert using PDO and PHP variable containing all database values

If you have 13k records to insert, it is good for performance to do not use prepared SQL statement. Just generate SQL query in format like this:

INSERT INTO IPXTools.MSSWireList 
(ID, Record, VlookupNode, HostWireLocation) 
VALUES 
('id1', 'r1', 'node1', 'location1'),
('id2', 'r2', 'node2', 'location2'),
...
('id13000', 'r13000', 'node13000', 'location13000');

What you may do for it - use maner of your legacy code. Your try block will looks loke this:

try 
{
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);    
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $conn->exec($insert);
}

PHP PDO batch insert from multi dimensional array failing to insert into MySQL

I believe you almost had it with this example:

$db->beginTransaction();

$stmt = $db->prepare("INSERT INTO mytable (column1, column2) VALUES (:blah, :whatever)");

foreach($test as $insertRow){

   // now loop through each inner array to match binded values
   foreach($insertRow as $column => $value){
      $stmt->bindParam(":{$column}", $value);
      $stmt->execute();
   }
}

$db->commit();

The problem you are running into is you are calling execute() before you have bound the proper number of parameters. Instead, you need to bind all of your parameters first, then call execute().

$db->beginTransaction();

$stmt = $db->prepare("INSERT INTO mytable (column1, column2) VALUES (:blah, :whatever)");

foreach($test as $insertRow){

   // now loop through each inner array to match binded values
   foreach($insertRow as $column => $value){
      $stmt->bindParam(":{$column}", $value);
   }

}

// NOW DO EXECUTE
$stmt->execute();

$db->commit();

EDIT

In response to your comment, it's hard to tell exactly what you are trying to accomplish, but if you are only receiving one record, then it is because of what Gerald brought up, these are separate queries to all be transacted at once. Take a look at this revision:

// Start Transaction
$db->beginTransaction();

// Insert each record
foreach($test as $insertRow){

   // Prepare statement
   $stmt = $db->prepare("INSERT INTO mytable (column1, column2) VALUES (:blah, :whatever)");

   // now loop through each inner array to match binded values
   foreach($insertRow as $column => $value){
      $stmt->bindValue(":{$column}", $value);
   }

   // Execute statement to add to transaction
   $stmt->execute();

   // Clear statement for next record (not necessary, but good practice)
   $stmt = null;
}

// Commit all inserts
$db->commit();

Split big insert query in batches using php pdo with prepared statements

function addHostBulk($value, $label, $group, $type, $user)
{
    $res = array("type" => intval($type), "value" => $value, "label" => $label, "group_id" => $group, "owner" => intval($user['id']));

    $hosts = $common->cidrToRange($res['value'], $user); //Returns array with all hosts from this CIDR range /array('hosts' => array('1.2.3.4', '1.2.3.5.', ...)/
    $totalHosts = count($hosts['hosts']);
    $batchSize = 1000;

    for ($idx=0; $idx*$batchSize < $totalHosts; $idx++) { 
        $hostsPartial = array_slice($hosts['hosts'], $idx*$batchSize, $batchSize);

        $this->db->beginTransaction();
        $query = ("INSERT INTO hosts (`id`, `type`, `value`, `label`, `group_id`, `owner`) VALUES ");
        $qPart = array_fill(0, count($hostsPartial), "(?, ?, ?, ?, ?, ?)");
        $query .=  implode(",",$qPart);

        $statement = $this->db->prepare($query);

        $i = 1;
        foreach($hostsPartial as $host){
                $statement->bindParam($i++, $common->uuid(), PDO::PARAM_STR);
                $statement->bindParam($i++, $res['type'], PDO::PARAM_STR);
                $statement->bindParam($i++, $host, PDO::PARAM_STR);
                $statement->bindParam($i++, $res['label'], PDO::PARAM_STR);
                $statement->bindParam($i++, $res['group_id'], PDO::PARAM_STR);
                $statement->bindParam($i++, $res['owner'], PDO::PARAM_STR);
                unset($host);
        }

        $statement->execute();
        $this->db->commit();
    }

    return true;
}

---

Related Topics