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

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

8.4.2 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 a compiled query object. The execute( ) method fills in any placeholders in the query and sends it to the RDBMS:

$response = $db->execute(compiled, values);

The values array contains the values for the placeholders in the query. The return value is either a query response object, or DB_ERROR if an error occurred.

For example, we could insert multiple values into the movies table like this:

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

The executeMultiple( ) method takes a two-dimensional array of values to insert:

$responses = $db->executeMultiple(compiled, values);

The values array must be numerically indexed from 0 and have values that are arrays of values to insert. The compiled query is executed once for every entry in values, and the query responses are collected in $responses.

A better way to write the movie-insertions code is:

$movies = array(array('Dr No', 1962),                 array('Goldfinger', 1965),                 array('Thunderball', 1965)); $compiled = $q->prepare('INSERT INTO movies (title,year) VALUES (?,?)'); $db->insertMultiple($compiled, $movies);

8.4.3 Shortcuts

PEAR DB provides a number of methods that perform a query and fetch the results in one step: getOne( ) , getRow( ), getCol( ), getAssoc( ), and getAll( ). All of these methods permit placeholders.

The getOne( ) method fetches the first column of the first row of data returned by an SQL query:

$value = $db->getOne(SQL [, values ]);

For example:

$when = $db->getOne("SELECT avg(year) FROM movies"); if (DB::isError($when)) {   die($when->getMessage(  )); } echo "The average James Bond movie was made in $when"; The average James Bond movie was made in 1977

The getRow( ) method returns the first row of data returned by an SQL query:

$row = $db->getRow(SQL [, values ]]);

This is useful if you know only one row will be returned. For example:

list($title, $actor) = $db->getRow(   "SELECT movies.title,actors.name FROM movies,actors     WHERE movies.year=1977 AND movies.actor=actors.id"); echo "($title, starring $actor)"; (The Spy Who Loved Me, starring Roger Moore)

The getCol( ) method returns a single column from the data returned by an SQL query:

$col = $db->getCol(SQL [, column [, values ]]);

The column parameter can be either a number (0, the default, is the first column), or the column name.

For example, this fetches the names of all the Bond movies in the database, ordered by the year they were released:

$titles = $db->getAll("SELECT title FROM movies ORDER BY year ASC"); foreach ($titles as $title) {   echo "$title\n"; } Dr No From Russia With Love Goldfinger ...

The getAll( ) method returns an array of all the rows returned by the query:

$all = $db->getAll(SQL [, values [, fetchmode ]]);

For example, the following code builds a select box containing the names of the movies. The ID of the selected movie is submitted as the parameter value.

$results = $db->getAll("SELECT id,title FROM movies ORDER BY year ASC"); echo "<select name='movie'>\n"; foreach ($results as $result) {   echo "<option value={$result[0]}>{$result[1]}</option>\n"; } echo "</select>";

All the get*( ) methods return DB_ERROR when an error occurs.

8.4.4 Details About a Query Response

Four PEAR DB methods provide you with information on a query result object: numRows( ) , numCols( ), affectedRows( ), and tableInfo( ).

The numRows( ) and numCols( ) methods tell you the number of rows and columns returned from a SELECT query:

$howmany = $response->numRows(  ); $howmany = $response->numCols(  );

The affectedRows( ) method tells you the number of rows affected by an INSERT, DELETE, or UPDATE operation:

$howmany = $response->affectedRows(  );

The tableInfo( ) method returns detailed information on the type and flags of fields returned from a SELECT operation:

$info = $response->tableInfo(  );

The following code dumps the table information into an HTML table:

$info = $response->tableInfo(  ); a_to_table($info);    function a_to_table ($a) {   echo "<table border=1>\n";   foreach ($a as $k => $v) {     echo "<tr valign=top align=left><td>$k</td><td>";     if (is_array($v)) {       a_to_table($v);     } else {       print_r($v);     }     echo "</td></tr>\n";   }   echo "</table>\n"; }

Figure 8-2 shows the output of the table information dumper.

Figure 8-2. The information from tableInfo( )

8.4.5 Sequences

Not every RDBMS has the ability to assign unique row IDs, and those that do have wildly differing ways of returning that information. PEAR DB sequences are an alternative to database-specific ID assignment (for instance, MySQL's AUTO_INCREMENT).

The nextID( ) method returns the next ID for the given sequence:

$id = $db->nextID(sequence);

Normally you'll have one sequence per table for which you want unique IDs. This example inserts values into the movies table, giving a unique identifier to each row:

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

A sequence is really a table in the database that keeps track of the last-assigned ID. You can explicitly create and destroy sequences with the createSequence( ) and dropSequence( ) methods:

$res = $db->createSequence(sequence); $res = $db->dropSequence(sequence);

The result will be the result object from the create or drop query, or DB_ERROR if an error occurred.

8.4.6 Metadata

The getListOf( ) method lets you query the database for information on available databases, users, views, and functions:

$data = $db->getListOf(what);

The what parameter is a string identifying the database feature to list. Most databases support "databases"; some support "users", "views", and "functions".

For example, this stores a list of available databases in $dbs:

$dbs = $db->getListOf("databases");

8.4.7 Transactions

Some RDBMSs support transactions, in which a series of database changes can be committed (all applied at once) or rolled back (discarded, with the changes not applied to the database). For example, when a bank handles a money transfer, the withdrawal from one account and deposit into another must happen together neither should happen without the other, and there should be no time between the two actions. PEAR DB offers the commit ( ) and rollback( ) methods to help with transactions:

$res = $db->commit(  ); $res = $db->rollback(  );

If you call commit( ) or rollback( ) on a database that doesn't support transactions, the methods return DB_ERROR.