PDO Prepared Inserts multiple rows in single query

Question

Hello, I am currently using this type of SQL on MySQL to insert multiple rows of values in one single query:

INSERT INTO tbl (key1,key2) VALUES ('r1v1','r1v2'),('r2v1','r2v2'),...

On the readings on PDO PDO, the use prepared statements should give me a better security than static queries.

I would therefore like to know whether it is possible to generate "inserting multiple rows of values by the use of one query" using Prepared Statements.

If yes, may I know how can I implement it?

Thank you!

Ho

Answer 1

That's simply not the way you use prepared statements.

It is perfectly okay to insert one row per query because you can execute one prepared statement multiple times with different parameters. In fact that is one of the greatest advantages as it allows you to insert you a great number of rows in an efficient, secure and comfortable manner.

So it maybe possible to implement the scheme you proposing, at least for a fixed number of rows, but it is almost guaranteed that this is not really what you want.

Answer 2

Two possible approaches:

$stmt = $pdo->prepare('INSERT INTO foo VALUES(:v1_1, :v1_2, :v1_3),
    (:v2_1, :v2_2, :v2_3),
    (:v2_1, :v2_2, :v2_3)');
$stmt->bindValue(':v1_1', $data[0][0]);
$stmt->bindValue(':v1_2', $data[0][1]);
$stmt->bindValue(':v1_3', $data[0][2]);
// etc...
$stmt->execute();

Or:

$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();
}

If the data for all the rows are in a single array, I would use the second solution.

Answer 3

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

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.