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

$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...

<?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.

Popular Posts