10.7. Repeating Queries Efficiently
Problem
You want to run the same query multiple times, substituting in different values each time.
Solution
With PEAR DB, set up the query with DB::prepare( )
and then
run the query with DB::execute( )
. The
placeholders in the query passed to prepare( )
are
replaced with data by execute( )
:
$prh = $dbh->prepare("SELECT sign FROM zodiac WHERE element LIKE ?"); $sth = $dbh->execute($prh,array('fire')); while($sth->fetchInto($row)) { print $row[0]."\n"; } $sth = $dbh->execute($prh,array('water')); while($sth->fetchInto($row)) { print $row[0]."\n"; }
Discussion
In the Solution, the first execute( )
runs the
query:
SELECT sign FROM zodiac WHERE element LIKE 'fire'
The second runs:
SELECT sign FROM zodiac WHERE element LIKE 'water'
Each time, execute( )
substitutes the value in its second argument for the
?
placeholder. If there is more than one
placeholder, put the arguments in the array in the order they should
appear in the query:
$prh = $dbh->prepare( "SELECT sign FROM zodiac WHERE element LIKE ? OR planet LIKE ?"); // SELECT sign FROM zodiac WHERE element LIKE 'earth' OR planet LIKE 'Mars' $sth = $dbh->execute($prh,array('earth','Mars'));
Values that replace a ?
placeholder are
appropriately quoted. To insert the contents of a file instead, use
the &
placeholder and pass
execute( )
the filename:
/* The structure of the pictures table is: CREATE TABLE pictures ( mime_type CHAR(20), data LONGBLOB ) */ $prh = $dbh->prepare('INSERT INTO pictures (mime_type,data) ...
Get PHP Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.