PHP CookBook

Recipe 10.8 Repeating Queries Efficiently

10.8.1 Problem

You want to run the same query multiple times, substituting in different values each time.

10.8.2 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";
}

10.8.3 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) VALUES (?,&)');
$sth = $dbh->execute($prh,array('image/jpeg','test.jpeg'));

To tell execute( ) not to quote a value, use the ! parameter. This is dangerous when applied to user input; it's useful, however, when one of the values is not a scalar, but a database function. For example, this query uses the NOW( ) function to insert the current date and time in a DATETIME column:

$prh = $dbh->prepare("INSERT INTO warnings (message,message_time) VALUES (?,!)");
$dbh->execute($prh,array("Don't cross the streams!",NOW()));

To execute a prepared statement many times with different arguments each time, use executeMultiple( ). Instead of just passing it one array of arguments as with execute( ), you pass it an array of argument arrays:

$prh = $dbh->prepare('INSERT INTO pictures (mime_type,data) VALUES (?,&)');

$ar = array(array('image/jpeg','earth.jpeg'),
            array('image/gif','wind.gif'),
            array('image/jpeg','fire.jpeg'));

$sth = $dbh->executeMultiple($prh,$ar);

You must declare the array first and then pass it to executeMultiple( ), or PHP gives an error that says you are passing executeMultiple( ) a parameter by reference. Although executeMultiple( ) loops through each argument in the array, if it encounters an error part-way through, it doesn't continue on with the rest of the arguments. If all queries succeed, executeMultiple( ) returns the constant DB_OK. Because executeMultiple( ) never returns a result object, you can't use it for queries that return data.

The Interbase and OCI8 DB backends take advantage of native database features so that prepare( )/execute( ) is more efficient than query( ) for INSERT/UPDATE/DELETE queries. The Interbase backend uses the ibase_prepare( ) and ibase_execute( ) functions, and the OCI8 backend uses the OCIParse( ) , OCIBindByName( ), and OCIExecute( ) functions. Other database backends construct queries to execute by interpolating the supplied values for the placeholders.

10.8.4 See Also

Documentation on DB::prepare( ) at http://pear.php.net/manual/en/core.db.prepare.php, DB::execute( ) at http://pear.php.net/manual/en/core.db.execute.php, and DB::executeMultiple( ) at http://pear.php.net/manual/en/core.db.executemultiple.php; an overview of executing queries is at http://pear.php.net/manual/en/core.db.tut_execute.php.