Php pdo insert batch

PDO/PHP Batch Inserting Records to Improve Speed

While making a remittance checker I was running into issues with the huge amount of records coming in that needed to be stored and processed.

A single SQL query/insert was taking something like 100-200ms and a single record import might have several.

For a file of 1m+ rows, this is unacceptable so I started looking for speed improvements.

Below is a function I settled on that took my time from 3000+ minutes down to 12 for a large file.

 function createBatchInsertStatement($table, $columns, $rows) < $processed_data = array(); foreach ($rows as $data) < foreach($data as $val) < $processed_data[] = $val; > > $placeholders = implode(',', array_fill(0, count($rows), '('.str_pad('', (count($columns)*2)-1, '?,').')')); $sql = "INSERT INTO $table> (" . implode(', ', $columns) . ") VALUES " . $placeholders . ";"; return [$sql, $processed_data]; > ?> 

The function takes in 3 parameters: $table , $columns , and $rows .

$columns is a 1-dimensional array of column headers, as in the database

$table is an n-dimensional array of rows to insert

The function will then respond with a 1×2 array containing the statement in $response[0] and the formatted array (compressed to 1-dimensional) in the $response[1].

An example usage would look like this:

 $table = "charges"; $columns = array("order_id", "charge_type", "amount"); // your batched data, limit to 50-100 records at a time for stability // array_chunk can handle automatically chunking a main data array // for you $rows = array( array("10001", "Principal", "32.66"); array("10002", "Tax", "2.21"); array("10003", "GiftWrapping", "0.48"); ); $response = createBatchInsertStatement($table, $columns, $rows); // here $db is a PDO connection object $db->beginTransaction(); $orders = $db->prepare($response[0]); $orders->execute($response[1]); $db->commit(); ?> 

That’s it, use this function to create queries wherever you find yourself batch-inserting large amounts of data!

Источник

PHP Pdo Insert Batch Multiple Rows with Placeholders

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.

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(":", $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(":", $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(":", $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;
>

Источник

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

Solution 2

I’m assuming you are using InnoDB so this answer is only valid for that engine (or any other transaction-capable engine, meaning MyISAM isn’t included).

By default InnoDB runs in auto-commit mode. That means each query is treated as its own contained transaction.

To translate that to something us mortals can understand, it means that every INSERT query you issue will force hard-disk to commit it by confirming it wrote down the query information. Considering how mechanical hard-disks are super slow since their input-output operation per second is low (if I’m not mistaken, the average is 300ish IO’s), it means your 50 000 queries will be — well, super slow.

So what do you do? You commit all of your 50k queries in a single transaction. It might not be the best solution for various purposes but it’ll be fast.

$dbh->beginTransaction(); $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)"); foreach($valuesToInsert as $insertRow) < // now loop through each inner array to match bound values foreach($insertRow as $column =>value) < $stmt->bindParam(":$column", value); $stmt->execute(); > > $dbh->commit(); 

Solution 3

A little modifications in solution provided by N.B
$stmt->execute() should be outside of inner loop because you may have one or more columns that need to bind before calling $stmt->execute() else you ‘ll get exception «Invalid parameter number: number of bound variables does not match number of token».
2nd «value» variable were missing dollar signs.

function batchinsert($sql,$params)< try < db->beginTransaction(); $stmt = db->prepare($sql); foreach($params as $row) < // now loop through each inner array to match bound values foreach($row as $column =>$value) < $stmt->bindParam(":$column", $value); > $stmt->execute(); > db->commit(); > catch(PDOExecption $e) < $db->rollback(); > > 

$sql = "INSERT INTO `test`(`name`, `value`) VALUES (:name, :value)" ; $data = array(); array_push($data, array('name'=>'Name1','value'=>'Value1')); array_push($data, array('name'=>'Name2','value'=>'Value2')); array_push($data, array('name'=>'Name3','value'=>'Value3')); array_push($data, array('name'=>'Name4','value'=>'Value4')); array_push($data, array('name'=>'Name5','value'=>'Value5')); batchinsert($sql,$data); 

Источник