Adapting multiple insert technique with PDO
Posted: Thu Nov 12, 2009 10:07 am
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):
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:
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?
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());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();
}Anyone know of a good approach to deal with this?