Page 1 of 1

Adapting multiple insert technique with PDO

Posted: Thu Nov 12, 2009 10:07 am
by tarsus
I have used MySQL with my PHP applications for a long time, but only recently began using PDO.

I want to take advantage of the benefits of prepared statements, but there is one valuable technique I haven't been able to figure out when using them: a multiple insert with an indeterminate number of records.

Where $array is an array that might contain any number of items, here's how I would do it the old way (note I'm just ignoring data sanitization for the purposes of this example):

Code: Select all

 
$inserts = Array();
foreach ($array as $item)
{
     $inserts[] = "('".$item['rank']."', '".$item['title']."', '".$item['notes']."')";
}
$sql = "INSERT INTO content (rank, title, notes) VALUES " . implode(',', $inserts);
$results = mysql_query($sql) or die(mysql_error());
Using PDO with prepared statements, though, there doesn't seem to be a way to do a multiple insert when you don't know the specific number of records to be inserted. The best I can do is execute the prepared statement multiple times:

Code: Select all

$st = $DB->prepare("INSERT INTO content (rank, title, notes) VALUES (:rank, :title, :notes)");
$st->bindParam('rank', $rank);
$st->bindParam('title', $title);
$st->bindParam('notes', $notes);
foreach ($array as $item)
{
     $rank = $item['rank'];
     $title = $item['title'];
     $notes = $item['notes'];
     $st->execute();
}
When I do this with numerous records, the processing time is unacceptable, as the script hits the database over and over. But if I build the SQL the way I've done in the past and just use PDO->exec(), I lose all the benefits of a prepared statement.

Anyone know of a good approach to deal with this?

Re: Adapting multiple insert technique with PDO

Posted: Mon Nov 16, 2009 10:50 am
by tarsus
Can I take the lack of reply to mean there isn't a way to do this with prepared statements?