Programming PHP, 2nd Edition

Advanced Database Techniques

PEAR DB goes beyond the database primitives shown earlier; it provides several shortcut functions for fetching result rows, as well as a unique row ID system and separate prepare/execute steps that can improve the performance of repeated queries.

Placeholders

Just as printf( ) builds a string by inserting values into a template, the PEAR DB can build a query by inserting values into a template. Pass the query( ) function SQL with ? in place of specific values, and add a second parameter consisting of the array of values to insert into the SQL:

$result = $db->query(SQL, values);

For example, this code inserts three entries into the books table:

$movies = array(array('Foundation', 1951),
                array('Second Foundation', 1953),
                array('Foundation and Empire', 1952));
foreach ($books as $book) {
  $db->query('INSERT INTO books (title,pub_year) VALUES (?,?)', $book);
}

There are three characters that you can use as placeholder values in an SQL query:

?

A string or number, which will be quoted if necessary (recommended)

|

A string or number, which will never be quoted

&

A filename, the contents of which will be included in the statement (e.g., for storing an image file in a BLOB field)

Prepare/Execute

When issuing the same query repeatedly, it can be more efficient to compile the query once and then execute it multiple times using the prepare( ), execute( ), and executeMultiple( ) methods.

The first step is to call prepare( ) on the query:

$compiled = $db->prepare(SQL);

This returns ...