Database Abstraction With PHP - Preparing For The Long Haul (
Page 7 of 11 )
In the event that you
need to execute a particular query multiple times with different values - for
example, a series of INSERT statements - the DB class comes with two methods
that can save you a huge amount of time (see, there's that lazy thing again!)
and also reduce overhead. Consider the following example, which demonstrates:
<?php
// include the DB abstraction layer
include("DB.php");
// set up array of track titles
$tracks = array("Track A", "Track B", "Track C", "Track D");
// connect to the database
$dbh = DB::connect("mysql://john:doe@localhost/db287");
// prepare query
$query = $dbh->prepare("INSERT INTO tracks (cd, track) VALUES (12, ?)");
// execute query
// run as many times as there are elements in $tracks
foreach($tracks as $t)
{
$result = $dbh->execute($query, $t);
}
// close database connection
$dbh->disconnect();
?>
The prepare() function, which takes an SQL query as
parameter, readies a query for execution, but does not execute it (kinda like
the priest that walks down the last mile with you to the electric chair).
Instead, prepare() returns a handle to the prepared query, which is stored and
then passed to the execute() method, which actually executes the query
(bzzzt!).
Note the placeholder used in the query string passed to
prepare() - this placeholder is replaced by an actual value each time execute()
runs on the prepared statement. The second argument to execute() contains the
values to be substituted in the query string.
In the event that you have
multiple placeholders within the same query string, you can pass execute() an
array of values instead of a single value - as demonstrated below:
<?php
// include the DB abstraction layer
include("DB.php");
// set up array of CD titles
$cds = array();
// each element of this array is itself an array
$cds[] = array("CD A", "Artist A");
$cds[] = array("CD B", "Artist B");
$cds[] = array("CD C", "Artist C");
// connect to the database
$dbh = DB::connect("mysql://john:doe@localhost/db287");
// prepare query
$query = $dbh->prepare("INSERT INTO cds (title, artist) VALUES (?, ?)");
// execute query
// run as many times as there are elements in $cds
foreach($cds as $c)
{
$result = $dbh->execute($query, $c);
}
// close database connection
$dbh->disconnect();
?>
Although overkill for our simple needs, you should be aware
that prepare() can also read data directly from a file, rather than an array.
I'll leave this to you to experiment with.
/ 57 
