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. http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html
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.
That is basically how we want the insert statement to look like.
Now the code...
Although in my test, there was only a 1 sec difference when using multiple inserts and regular prepared inserts with single value.
$datafields = array('fielda' => '', 'fieldb' => '' ... );more data values or you probably have a loop that populates data.
$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);
$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);
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...
<?php
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(",", array_keys($datafield) ) . ") VALUES " . implode(',', $question_marks);
$stmt = $pdo->prepare ($sql);
try {
$stmt->execute($insert_values);
} catch (PDOException $e){
echo $e->getMessage();
}
$pdo->commit();
Although in my test, there was only a 1 sec difference when using multiple inserts and regular prepared inserts with single value.
Comments
Post a Comment