Programming PHP

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 movies table:

$movies = array(array('Dr No', 1962),
                array('Goldfinger', 1965),
                array('Thunderball', 1965));
foreach ($movies as $movie) {
  $db->query('INSERT INTO movies (title,year) VALUES (?,?)', $movie);
}

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