PHP Cookbook

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