PHP : Database Access - [10.1] Introduction

Databases are central to many web applications. A database can hold almost any collection of information you may want to search and update, such as a user list, a product catalog, or recent headlines. One reason why PHP is such a great web programming language is its extensive database support. PHP can interact with (at last count) 17 different databases, some relational and some not. The relational databases it can talk to are DB++, FrontBase, Informix, Interbase, Ingres II, Microsoft SQL Server, mSQL, MySQL, Oracle, Ovrimos SQL Server, PostgreSQL, SESAM, and Sybase. The nonrelational databases it can talk to are dBase, filePro, HyperWave, and the DBM family of flat-file databases. It also has ODBC support, so even if your favorite database isn't in the list, as long as it supports ODBC, you can use it with PHP.

If your data storage needs are simple and you don't need to serve many users, you may be able to use a plaintext file as a makeshift database. This is discussed in Section 10.2. Text files require no special database software but are appropriate only for lightly used, basic applications. A text file can't handle structured data well; if your data changes a lot, it's inefficient to store it in a plain file instead of a database.

DBM flat-file databases, discussed in Section 10.3, offer more robustness and efficiency than flat files but still limit the structure of your data to key/value pairs. They scale better than plaintext files, especially for read-only (or read-almost-always) data.

PHP really shines, though, when paired with a SQL database. This combination is used for most of the recipes in this chapter. SQL databases can be complicated, but they are extremely powerful. To use PHP with a particular SQL database, PHP must be explicitly told to include support for that database when it is compiled. If PHP is built to support dynamic module loading, the database support can also be built as a dynamic module.

Many SQL examples in this chapter use a table of information about Zodiac signs. The table's structure is:

CREATE TABLE zodiac (
  id INT UNSIGNED NOT NULL,
  sign CHAR(11),
  symbol CHAR(13),
  planet CHAR(7),
  element CHAR(5),
  start_month TINYINT,
  start_day TINYINT,
  end_month TINYINT,
  end_day TINYINT,
  PRIMARY KEY(id)
);

And the data in the table is:

INSERT INTO zodiac VALUES (1,'Aries','Ram','Mars','fire',3,21,4,19);
INSERT INTO zodiac VALUES (2,'Taurus','Bull','Venus','earth',4,20,5,20);
INSERT INTO zodiac VALUES (3,'Gemini','Twins','Mercury','air',5,21,6,21);
INSERT INTO zodiac VALUES (4,'Cancer','Crab','Moon','water',6,22,7,22);
INSERT INTO zodiac VALUES (5,'Leo','Lion','Sun','fire',7,23,8,22);
INSERT INTO zodiac VALUES (6,'Virgo','Virgin','Mercury','earth',8,23,9,22);
INSERT INTO zodiac VALUES (7,'Libra','Scales','Venus','air',9,23,10,23);
INSERT INTO zodiac VALUES (8,'Scorpio','Scorpion','Mars','water',20,24,11,21);
INSERT INTO zodiac VALUES (9,'Sagittarius','Archer','Jupiter','fire',11,22,12,21);
INSERT INTO zodiac VALUES (10,'Capricorn','Goat','Saturn','earth',12,22,1,19);
INSERT INTO zodiac VALUES (11,'Aquarius','Water Carrier','Uranus','air',1,20,2,18);
INSERT INTO zodiac VALUES (12,'Pisces','Fishes','Neptune','water',2,19,3,20);

The specific functions required to talk to the database differ with each database, but each follows a similar pattern. Connecting to the database returns a database connection handle. You use the connection handle to create statement handles, which are associated with particular queries. A query statement handle then gets the results of that query.

This example retrieves all the rows from the zodiac table with Oracle, using the OCI8 interface:

if (! $dbh = OCILogon('david', 'foo!bar','ORAINST')) {
    die("Can't connect: ".OCIError());
}

if (! $sth = OCIParse($dbh,'SELECT * FROM zodiac')) {
    die("Can't parse query: ".OCIError());
}

if (! OCIExecute($sth)) {
    die("Can't execute query: ".OCIError());
}

PHP : Database Access - [10.2] Using Text-File Databases

10.2.1 Problem

You want a lightweight way to store information between requests.

10.2.2 Solution

Use a text file with advisory locking to prevent conflicts. You can store data in the text file in any useful format (CSV, pipe-delimited, etc.) One convenient way is to put all the data you want to store in one variable (a big associative array) and then store the output of calling serialize( ) on the variable:

$data_file = '/tmp/data';

// open the file for reading and writing
$fh = fopen($data_file,'a+') or die($php_errormsg);
rewind($fh)                  or die($php_errormsg);

// get an exclusive lock on the file 
flock($fh,LOCK_EX)           or die($php_errormsg);

// read in and unserialize the data
$serialized_data = fread($fh,filesize($data_file)) or die($php_errormsg);
$data = unserialize($serialized_data);

/*
 * do whatever you need to with $data ...
 */

// reserialize the data 
$serialized_data = serialize($data);

// clear out the file
rewind($fh)                  or die($php_errormsg);
ftruncate($fp,0)             or die($php_errormsg);

// write the data back to the file and release the lock 
if (-1 == (fwrite($fh,$serialized_data))) { die($php_errormsg); }
fflush($fh)                  or die($php_errormsg);
flock($fh,LOCK_UN)           or die($php_errormsg);
fclose($fh)                  or die($php_errormsg);

10.2.3 Discussion

Storing your data in a text file doesn't require any additional database software to be installed, but that's pretty much its only advantage. Its main disadvantages are clumsiness and inefficiency. At the beginning of a request, you've got to lock your text file and haul out all your data from it, even if you're only using a little bit of the data. Until you unlock the file at the end of the request, all other processes have to wait around, doing nothing, which means all your users are waiting too. One of the great assets of databases is that they give you structured access to your data, so you only lock (and load into memory) the data you actually care about. The text file solution doesn't do that.

What's worse, the locking you can do with a text file isn't nearly as robust as what you can do with a database. Because flock( ) provides a kind of file locking called advisory locking, the only thing that prevents multiple processes from stepping on each other and trashing your data is politeness and diligent programming. There's no guarantee your data is safe from an innocently incompetent or intentionally malicious program.

PHP : Database Access - [10.3] Using DBM Databases

10.3.1 Problem

You want a more stable and scalable way to store simple data than what text files offer.

10.3.2 Solution

Use the DBA abstraction layer to access a DBM-style database:

$dbh = dba_open('fish.db','c','gdbm') or die($php_errormsg);

// retrieve and change values
if (dba_exists('flounder',$dbh)) {
  $flounder_count = dba_fetch('flounder',$dbh);
  $flounder_count++;
  dba_replace('flounder',$flounder_count);
  print "Updated the flounder count.";
} else {
  dba_insert('flounder',1);
  print "Started the flounder count.";
}

// no more tilapia
dba_delete('tilapia',$dbh);

// what fish do we have?
for ($key = dba_firstkey($dbh);  $key !== false; $key = dba_nextkey($dbh)) {
   $value = dba_fetch($key);
   print "$key: $value\n";
}

dba_close($dbh);

10.3.3 Discussion

PHP can support a few different kinds of DBM backends: GDBM, NDBM, DB2, DB3, DBM, and CDB. The DBA abstraction layer lets you use the same functions on any DBM backend. All these backends store key/value pairs. You can iterate through all the keys in a database, retrieve the value associated with a particular key, and find if a particular key exists. Both the keys and the values are strings.

The following program maintains a list of usernames and passwords in a DBM database. The username is the first command-line argument, and the password is the second argument. If the given username already exists in the database, the password is changed to the given password; otherwise the user and password combination are added to the database:

$user = $_SERVER['argv'][1];
$password = $_SERVER['argv'][2];

$data_file = '/tmp/users.db';

$dbh = dba_open($data_file,'c','gdbm') or die("Can't open db $data_file");

if (dba_exists($user,$dbh)) {
    print "User $user exists. Changing password.";
} else {
    print "Adding user $user.";
}

PHP : Database Access - [10.4] Connecting to a SQL Database

10.4.1 Problem

You want access to a SQL database.

10.4.2 Solution

Use the connect( ) method of PEAR DB:

require 'DB.php';

$dsn = 'mysql://david:foo!bar@localhost/test';

$dbh = DB::connect($dsn);
if (DB::isError($dbh)) { die ($dbh->getMessage()); }

10.4.3 Discussion

To use PEAR DB, you must download it from PEAR at:

http://pear.php.net/package-info.php?package=DB

After loading the DB functions from DB.php, connect to the database with DB::connect( ), execute the query with $dbh->query( ) , and retrieve each row with $sth->fetchRow( ). The Solution example connects to MySQL. To connect to Oracle instead, you just need to change $dsn. This variable holds the data source name (DSN), a string that specifies which database to connect to and how to connect to it. Here's the value for Oracle:

$dsn = 'oci8://david:foo!bar@ORAINST';

For PostgreSQL, $dsn is:

$dsn = 'pgsql://david:foo!bar@unix(/tmp/.s.PGSQL.5432)/test';

The PostgreSQL DSN is a little more complicated because it specifies that the connection should be made using a local Unix socket (whose pathname is /tmp/.s.PGSQL.5432) instead of a TCP/IP connection. In general, the form of a data source name is:

database_interface://user:password@hostname/database

The database_interface part of the DSN is the kind of database you're using, such as Oracle, MySQL, etc. Currently, PEAR supports 10 database backends, as listed in Table 10-1.

Table 10-1. PEAR DB backends

Name	Database
fbsql	FrontBase
ibase	Interbase
ifx	Informix
msql	Mini-SQL
mssql	Microsoft SQL Server
mysql	MySQL
oci8	Oracle (using the OCI8 interface)
odbc	ODBC
pgsql	PostgreSQL
sybase	Sybase

To use a particular PEAR DB backend, PHP must be built with support for the database that corresponds to the backend. Note that to use the Oracle OCI8 backend, PHP must have the OCI8 extension (--with-oci8 when building). The older PHP oracle extension (--with-oracle) isn't compatible with PEAR DB.

user and password are the username and password to use to connect to the database. hostname is usually the hostname that the database is running on, but it can also be the name of an instance (for Oracle) or the special syntax used previously to indicate a local socket. database is the name of the logical database to use, such as what you'd specify with the dbname parameter in pg_connect( ) or the argument to mysql_select_db( ).

PEAR DB is by no means the only database abstraction layer available for PHP. We've chosen to focus on it because it's easy to use and widely available. Other database abstraction layers include ADOdb (http://php.weblogs.com/ADODB), Metabase (http://en.static.phpclasses.org/browse.html/package/20.html), the DB_Sql class in PHPLib (http://phplib.sourceforge.net/), and MDB (http://pear.php.net/package-info.php?package=MDB).

PHP : Database Access - [10.5] Querying a SQL Database

10.5.1 Problem

You want to retrieve some data from your database.

10.5.2 Solution

Use DB::query( ) from PEAR DB to send the SQL query to the database, and then DB_Result::fetchRow( ) or DB_Result::fetchInto( ) to retrieve each row of the result:

// using fetchRow()
$sth = $dbh->query("SELECT sign FROM zodiac WHERE element LIKE 'fire'");
if (DB::isError($sth)) { die($sth->getMessage()); }

while($row = $sth->fetchRow()) {
    print $row[0]."\n";
}

// using fetchInto()
$sth = $dbh->query("SELECT sign FROM zodiac WHERE element LIKE 'fire'");
if (DB::isError($sth)) { die($sth->getMessage()); }

while($sth->fetchInto($row)) {
    print $row[0]."\n";
}

10.5.3 Discussion

The fetchRow( ) method returns data, while fetchInto( ) puts the data into a variable you pass it. Both fetchRow( ) and fetchInto( ) return NULL when no more rows are available. If either encounter an error when retrieving a row, they return a DB_Error object, just as the DB::connect( ) and DB::query( ) methods do. You can insert a check for this inside your loop:

while($row = $sth->fetchRow()) {
    if (DB::isError($row)) { die($row->getMessage()); }
    print $row[0]."\n";
}

If magic_quotes_gpc is on, you can use form variables directly in your queries:

$sth = $dbh->query(
    "SELECT sign FROM zodiac WHERE element LIKE '" . $_REQUEST['element'] . "'");

If not, escape the value with DB::quote( ) , or use a placeholder in the query:

$sth = $dbh->query("SELECT sign FROM zodiac WHERE element LIKE " .
                   $dbh->quote($_REQUEST['element']));

$sth = $dbh->query('SELECT sign FROM zodiac WHERE element LIKE ?',
                   array($_REQUEST['element']));

Section 10.10 goes into detail about when you need to quote values and how to do it.

By default, fetchRow( ) and fetchInto( ) put data in numeric arrays. You can tell them to use associative arrays or objects by passing an additional parameter to either method. For associative arrays, use DB_FETCHMODE_ASSOC:

while($row = $sth->fetchRow(DB_FETCHMODE_ASSOC)) {
    print $row['sign']."\n";
}

while($sth->fetchInto($row,DB_FETCHMODE_ASSOC)) {
    print $row['sign']."\n";
}

For objects, use DB_FETCHMODE_OBJECT:

while($row = $sth->fetchRow(DB_FETCHMODE_OBJECT)) {
    print $row->sign."\n";
}

while($sth->fetchInto($row,DB_FETCHMODE_OBJECT)) {
    print $row->sign."\n";
}

Whatever the fetch mode, the methods still return NULL when there is no more data to retrieve and a DB_Error object on error. The default numeric array behavior can be specified with DB_FETCHMODE_ORDERED. You can set a fetch mode to be used in all subsequent calls to fetchRow( ) or fetchInto( ) with DB::setFetchMode( ) :

$dbh->setFetchMode(DB_FETCHMODE_OBJECT);

while($row = $sth->fetchRow()) {
    print $row->sign."\n";
}

// subsequent queries and calls to fetchRow() also return objects

PHP : Database Access - [10.6] Retrieving Rows Without a Loop

10.6.1 Problem

You want a concise way to execute a query and retrieve the data it returns.

10.6.2 Solution

With PEAR DB, use DB::getRow( ) to retrieve the first (or only) row from a query:

$row = $dbh->getRow("SELECT planet,symbol FROM zodiac WHERE sign LIKE 'Pisces'");

Use DB::getAll( ) to retrieve all rows from a query:

$rows = $dbh->getAll("SELECT planet,symbol FROM zodiac WHERE element LIKE 'fire'");

Use DB::getOne( ) to retrieve just one column from one row:

$col = $dbh->getOne("SELECT symbol FROM zodiac WHERE sign = 'Libra'");

Use DB::getCol( ) to retrieve a column from all rows:

$cols = $dbh->getCol('SELECT symbol FROM zodiac');

Use DB::getAssoc( ) to retrieve all rows from a query into an associative array indexed by the first column of the query:

$assoc = $dbh->getAssoc(
    "SELECT sign,symbol,planet FROM zodiac WHERE element LIKE 'water'");

10.6.3 Discussion

All these functions return a DB_Error object if an error occurs in executing a query or retrieving the results. If the query returns no results, getRow( ) and getOne( ) return NULL; getAll( ), getCol( ), and getAssoc( ) return an empty array.

When returning results, getRow( ) returns an array or object, depending on the current fetch mode. The getAll( ) method returns an array of arrays or array of objects, also depending on the fetch mode. The single result getOne( ) returns is usually a string, because PHP database drivers generally cast retrieved results into strings. Similarly, getCol( ) returns an array of results whose values are usually strings. The results from getAssoc( ) are returned as an array. The type of elements of that array are controlled by the fetch mode.

Like DB::query( ), you can pass these functions a query with placeholders in it and an array of parameters to fill the placeholders. The parameters are properly quoted when they replace the placeholders in the query:

PHP : Database Access - [10.7] Modifying Data in a SQL Database

10.7.1 Problem

You want to add, remove, or change data in a SQL database.

10.7.2 Solution

With PEAR DB, use DB::query( ) to send an INSERT, DELETE, or UPDATE command:

$dbh->query("INSERT INTO family (id,name) VALUES (1,'Vito')");

$dbh->query("DELETE FROM family WHERE name LIKE 'Fredo'");

$dbh->query("UPDATE family SET is_naive = 1 WHERE name LIKE 'Kay'");

You can also prepare a query with DB::prepare( ) and execute it with DB::execute( ):

$prh = $dbh->prepare('INSERT INTO family (id,name) VALUES (?,?)');
$dbh->execute($prh,array(1,'Vito'));

$prh = $dbh->prepare('DELETE FROM family WHERE name LIKE ?');
$dbh->execute($prh,array('Fredo'));

$prh = $dbh->prepare('UPDATE family SET is_naive = ? WHERE name LIKE ?');
$dbh->execute($prh,array(1,'Kay');

10.7.3 Discussion

The query( ) method sends to the database whatever it's passed, so it can be used for queries that retrieve data or queries that modify data.

The prepare( ) and execute( ) methods are especially useful for queries that you want to execute multiple times. Once you've prepared a query, you can execute it with new values without re-preparing it:

$prh = $dbh->prepare('DELETE FROM family WHERE name LIKE ?');
$dbh->execute($prh,array('Fredo'));
$dbh->execute($prh,array('Sonny'));
$dbh->execute($prh,array('Luca Brasi'));

PHP : Database Access - [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
   )
*/

PHP : Database Access - [10.9] Finding the Number of Rows Returned by a Query

10.9.1 Problem

You want to know how many rows a SELECT query returned, or you want to know how many rows were changed by an INSERT, UPDATE, or DELETE query.

10.9.2 Solution

To find the number of rows returned by a SELECT query, use PEAR DB's DB_Result::numRows( ) :

// query
$sth = $dbh->query('SELECT * FROM zodiac WHERE element LIKE ?', array('water'));
$water_rows = $sth->numRows();

// prepare and execute
$prh = $dbh->prepare('SELECT * FROM zodiac WHERE element LIKE ?');
$sth = $dbh->execute($prh,array('fire'));
$fire_rows = $sth->numRows();

To find the number of rows changed by an INSERT , UPDATE, or DELETE query, use DB::affectedRows( ) :

$sth = $dbh->query('DELETE FROM zodiac WHERE element LIKE ?',array('fire'));
$deleted_rows = $dbh->affectedRows();

$prh = $dbh->prepare('INSERT INTO zodiac (sign,symbol) VALUES (?,?)',
                     array('Leap Day','Kangaroo'));
$dbh->execute($prh,$sth);
$inserted_rows = $dbh->affectedRows();

$dbh->query('UPDATE zodiac SET planet = ? WHERE sign LIKE ?',
            array('Trantor','Leap Day'));
$updated_rows = $dbh->affectedRows();

10.9.3 Discussion

The number of rows in a result set is a property of that result set, so that numRows( ) is called on the statement handle and not the database handle. The number of rows affected by a data manipulation query, however, can't be a property of a result set, because those queries don't return result sets. As a result, affectedRows( ) is a method of the database handle.

PHP : Database Access - [10.10] Escaping Quotes

10.10.1 Problem

You need to make text or binary data safe for queries.

10.10.2 Solution

Write all your queries with placeholders and pass values to fill the placeholders in an array:

$sth = $dbh->query('UPDATE zodiac SET planet = ? WHERE id = 2',
                   array('Melmac'));

$rows = $dbh->getAll('SELECT * FROM zodiac WHERE planet LIKE ?',
                     array('M%'));

You can also use PEAR DB's DB::quote( ) to escape special characters and make sure strings are appropriately marked (usually with single quotes around them):

$planet = $dbh->quote($planet);
$dbh->query("UPDATE zodiac SET planet = $planet WHERE id = 2");

If $planet is Melmac, $dbh->quote($planet) if you are using MySQL returns 'Melmac'. If $planet is Ork's Moon, $dbh->quote($planet) returns 'Ork\'s Moon'.

10.10.3 Discussion

The DB::quote( ) method makes sure that text or binary data is appropriately quoted, but you also need to quote the SQL wildcard characters % and _ to ensure that SELECT statements return the right results. If $planet is set to Melm%, this query returns rows with planet set to Melmac, Melmacko, Melmacedonia, or anything else beginning with Melm:

$planet = $dbh->quote($planet);
$dbh->query("SELECT * FROM zodiac WHERE planet LIKE $planet");

Because % is the SQL wildcard meaning "match any number of characters" (like * in shell globbing) and _ is the SQL wildcard meaning "match one character" (like ? in shell globbing), those need to be backslash-escaped as well. Use strtr( ) to escape them:

$planet = $dbh->quote($planet);
$planet = strtr($planet,array('_' => '\_', '%' => '\%'));
$dbh->query("SELECT * FROM zodiac WHERE planet LIKE $planet");

strtr( ) must be called after DB::quote( ). Otherwise, DB::quote( ) would backslash-escape the backslashes strtr( ) adds. With DB::quote( ) first, Melm_ is turned into Melm\_, which is interpreted by the database to mean "the string M e l m followed by a literal underscore character." With DB::quote( ) after strtr( ), Melm_ is turned into Melm\\_, which is interpreted by the database to mean "the string Melm followed by a literal backslash character, followed by the underscore wildcard."

A quote method is defined in the DB base class, but some of the database-specific subclasses override that method to provide appropriate quoting behavior for the particular database in use. By using DB::quote( ) instead of replacing specific characters, your program is more portable.

Quoting of placeholder values happens even if magic_quotes_gpc or magic_quotes_runtime is turned on. Similarly, if you call DB:quote( ) on a value when magic quotes are active, the value gets quoted anyway. For maximum portability, remove the magic quotes-supplied backslashes before you use a query with placeholders or call DB::quote( ):

$fruit = ini_get('magic_quotes_gpc') ? stripslashes($_REQUEST['fruit']) : 
    $_REQUEST['fruit'];

$dbh->query('UPDATE orchard SET trees = trees - 1 WHERE fruit LIKE ?',
            array($fruit));

PHP : Database Access - [10.11] Logging Debugging Information and Errors

10.11.1 Problem

You want access to information to help you debug database problems. For example, when a query fails, you want to see what error message the database returns.

10.11.2 Solution

Use DB::isError( ) to investigate the results of a single query:

$sth = $dbh->query("SELECT aroma FROM zodiac WHERE element LIKE 'fire'");
DB::isError($sth) and print 'Database Error: '.$sth->getMessage();

Use DB::setErrorHandling( ) to automatically take action on any database error:

$dbh->setErrorHandling(PEAR_ERROR_PRINT);
$sth = $dbh->query("SELECT aroma FROM zodiac WHERE element LIKE 'fire'");

10.11.3 Discussion

When they encounter an error, most PEAR DB methods return an DB_Error object. The DB::isError( ) method returns true if it's passed a DB_Error object, so you can use that to test the results of individual queries. The DB_Error class is a subclass of PEAR::Error, so you can use methods such as getMessage( ) to display information about the error. If you want to display everything in the error object, use print_r( ):

$sth = $dbh->query('SELECT aroma FROM zodiac WHERE element LIKE 'fire'");
if (DB::isError($sth)) {
    print_r($sth);
}

Since there is no aroma column in the zodiac table, this prints:

db_error Object
(
    [error_message_prefix] => 
    [mode] => 1
    [level] => 1024
    [code] => -19
    [message] => DB Error: no such field
    [userinfo] => SELECT aroma FROM zodiac WHERE element LIKE 'fire' \
[nativecode=1054 ** Unknown column 'aroma' in 'field list']
    [callback] => 
)

Using setErrorHandling( ) lets you define a behavior that's invoked automatically whenever there's a database error. Tell setErrorHandling( ) what to do by passing it a PEAR_ERROR constant. The PEAR_ERROR_PRINT constant prints the error message, but program execution continues:

$dbh->setErrorHandling(PEAR_ERROR_PRINT);
$sth = $dbh->query("SELECT aroma FROM zodiac WHERE element LIKE 'fire'");

This prints:

DB Error: no such field

To print out an error message and then quit, use PEAR_ERROR_DIE. You can also use the PEAR_ERROR_CALLBACK constant to run a custom function when an error is raised. This custom function can print out even more detailed information:

function pc_log_error($error_obj) {
    error_log(sprintf("%s (%s)",$error_obj->message,$error_obj->userinfo));
}

$dbh->setErrorHandling(PEAR_ERROR_CALLBACK,'pc_log_error');
$sth = $dbh->query("SELECT aroma FROM zodiac WHERE element LIKE 'fire'");

When the incorrect SQL in the $dbh->query( ) method raises an error, pc_log_error( ) is called with the DB_Error object passed to it. The pc_log_error( ) callback uses the properties of the DB_Error object to print a more complete message to the error log:

DB Error: no such field (SELECT aroma FROM zodiac WHERE element 
LIKE 'fire' [nativecode=Unknown column 'aroma' in 'field list'])

To capture all the data in the error object and write it to the error log, use print_r( ) with output buffering in the error callback:

function pc_log_error($error_obj) {
    ob_start();
    print_r($error_obj);
    $dump = ob_get_contents();

PHP : Database Access - [10.12] Assigning Unique ID Values Automatically

10.12.1 Problem

You want to use an incrementing sequence of integers for unique IDs. For example, you want to assign unique IDs to users, articles, or other objects as you add them to your database.

10.12.2 Solution

With PEAR DB, use DB::nextId( ) with a sequence name to get the next integer in a sequence:

$id = $dbh->nextId('user_ids');

10.12.3 Discussion

By default, the sequence is created if it doesn't already exist, and the first ID in the sequence is 1. You can use the integer returned from nextId( ) in subsequent INSERT statements:

$id = $dbh->nextId('user_ids');
$dbh->query("INSERT INTO users (id,name) VALUES ($id,'david')");

This inserts a record into the users table with an id of 1 and a name of david. To prevent a sequence from being created if it doesn't already exist, pass false as a second argument to nextId( ):

$id = $dbh->nextId('user_ids',false);
$dbh->query("INSERT INTO users (id,name) VALUES ($id,'david')");

To create a sequence, use createSequence( ); to drop a sequence, use dropSequence( ):

$dbh->createSequence('flowers');
$id = $dbh->nextId('flowers');
$dbh->dropSequence('flowers');

A DB_Error object is returned if you try to create a sequence that already exists or drop a sequence that doesn't.

PHP : Database Access - [10.13] Building Queries Programmatically

10.13.1 Problem

You want to construct an INSERT or UPDATE query from an array of field names. For example, you want to insert a new user into your database. Instead of hardcoding each field of user information (such as username, email address, postal address, birthdate, etc.), you put the field names in an array and use the array to build the query. This is easier to maintain, especially if you need to conditionally INSERT or UPDATE with the same set of fields.

10.13.2 Solution

To construct an UPDATE query, build an array of field/value pairs and then join( ) together each element of that array:

$fields = array('symbol','planet','element');

$update_fields = array();
foreach ($fields as $field) {
    $update_fields[] = "$field = " . $dbh->quote($GLOBALS[$field]);
}
$sql = 'UPDATE zodiac SET ' . join(',',$update_fields) 
    . ' WHERE sign = ' . $dbh->quote($sign);

For an INSERT query, construct an array of values in the same order as the fields, and build the query by applying join( ) to each array:

$fields = array('symbol','planet','element');

$insert_values = array();
foreach ($fields as $field) {
    $insert_values[] = $dbh->quote($GLOBALS[$field]);
}
$sql = 'INSERT INTO zodiac (' . join(',',$fields) . ') VALUES ('
       . join(',',$insert_values) . ')';

If you have PEAR DB Version 1.3 or later, use the DB::autoPrepare( ) method:

$fields = array('symbol','planet','element');

// UPDATE: specify the WHERE clause
$update_prh = $dbh->autoPrepare('zodiac',$fields,DB_AUTOQUERY_UPDATE,
                                'sign = ?');
$update_values = array();
foreach ($fields as $field) { $update_values[] = $GLOBALS[$field]; }
$update_values[] = $GLOBALS['sign'];
$dbh->execute($update_prh,$update_values);

// INSERT: no WHERE clause
$insert_prh = $dbh->autoPrepare('zodiac',$fields,DB_AUTOQUERY_INSERT);
$insert_values = array();
foreach ($fields as $field) { $insert_values[] = $GLOBALS[$field]; }
$dbh->execute($insert_prh,$insert_values);

10.13.3 Discussion

The DB::autoPrepare( ) method is concise and easy to use if you have a recent version of DB. PHP 4.2.2 comes with DB 1.2. Newer versions of DB can be downloaded from PEAR. Use method_exists( ) to check whether your version of DB supports autoPrepare( ):

if (method_exists($dbh,'autoPrepare')) {
    $prh = $dbh->autoPrepare('zodiac',$fields,DB_AUTOQUERY_UPDATE','sign = ?');
    // ...
} else {
    error_log("Can't use autoPrepare");
    exit;
}

PHP : Database Access - [10.14] Making Paginated Links for a Series of Records

10.14.1 Problem

You want to display a large dataset a page at a time and provide links that move through the dataset.

10.14.2 Solution

Use the PEAR DB_Pager class:

require 'DB/Pager.php';

$offset = intval($_REQUEST['offset']);
$per_page = 3;

$sth = $dbh->query('SELECT * FROM zodiac ORDER BY id');
$pager = new DB_Pager($sth, $offset, $per_page);
$data = $pager->build();

// display each row on this page  
while ($v = $pager->fetchRow()) {
    print "$v->sign, $v->symbol ($v->id)<br>";
}

// a link to the previous page
printf('<a href="%s?offset=%d">&lt;&lt;Prev</a> |',
       $_SERVER['PHP_SELF'],$data['prev']);

// direct links to each page
foreach ($data['pages'] as $page => $start) {
    printf(' <a href="%s?offset=%d">%d</a> |',$_SERVER['PHP_SELF'],$start,$page);
}

// a link to the next page
printf(' <a href="%s?offset=%d">Next&gt;&gt;</a>',
           $_SERVER['PHP_SELF'],$data['next']);

// display which records are on this page
printf("<br>(Displaying %d - %d of %d)",
       $data['from'],$data['to'],$data['numrows']);

If you don't have DB_Pager or you do but don't want to use it, you can roll your own indexed link display using the pc_indexed_links( ) and pc_print_link( ) functions shown in the Discussion in Examples 10-2 and 10-3.

$offset = intval($_REQUEST['offset']);
if (! $offset) { $offset = 1; }
$per_page = 5;
$total = $dbh->getOne('SELECT COUNT(*) FROM zodiac');

$sql = $dbh->modifyLimitQuery('SELECT * FROM zodiac ORDER BY id',
                              $offset - 1,$per_page);
$ar = $dbh->getAll($sql);
foreach ($ar as $k => $v) {
    print "$v->sign, $v->symbol ($v->id)<br>";
}

pc_indexed_links($total,$offset,$per_page);
printf("<br>(Displaying %d - %d of %d)",$offset,$offset+$k,$total);

10.14.3 Discussion

DB_Pager is designed specifically to paginate results that come from a PEAR DB query. To use it, create a DB_Pager object and tell it what query to use, what offset into the result set to start at, and how many items belong on each page. It calculates the correct pagination.

The $pager->build( ) method calculates the appropriate rows to return and other page-specific variables. DB_Pager provides a fetchRow( ) method to retrieve the results in the same way the DB class operates. (You can also use fetchInto( ) with DB_Pager). However, while it provides all the data you need to build appropriate links, it also leaves it up to you to build those links. The offset the previous page starts at is in $data['prev'], and $data['next'] is the offset of the next page. The $data['pages'] array contains page numbers and their starting offsets. The output when $offset is is shown in Figure 10-3.

Figure 10-3. Paginated results with DB_Pager

All the page numbers, "<<Prev" and "Next>>," are links. "<<Prev" and "1" point to the current page; the others point to their corresponding pages. On page 4, the "Next>>" link points back to page 1. (But on page 1, the "<<Prev" link doesn't point to page 4.) The numbers in the links refer to page numbers, not element numbers.

If DB_Pager isn't available, you can use the pc_print_link( ) and pc_indexed_links( ) functions shown in Examples 10-2 and 10-3 to produce properly formatted links.

PHP : Database Access - [10.15] Caching Queries and Results

10.15.1 Problem

You don't want to rerun potentially expensive database queries when the results haven't changed.

10.15.2 Solution

Use PEAR's Cache_DB package. It wraps the DB database abstraction layer with an object that has similar methods and that automatically caches the results of SELECT queries:

require 'Cache/DB.php';

$cache = new Cache_DB;
$cache->connect('mysql://test:@localhost/test');

$sth = $cache->query("SELECT sign FROM zodiac WHERE element LIKE 'fire'");

while($row = $sth->fetchRow()) {
    print $row['sign']."\n";
}

10.15.3 Discussion

Using Cache_DB is almost the same as using DB, but there are some crucial differences. First, Cache/DB.php is required instead of DB.php. The Cache/DB.php file then loads the appropriate DB classes. Instead of creating a database handle with the DB::connect( ) method, you instantiate a Cache_DB object with the new operator and then call the object's connect( ) method. The syntax of $cache->connect( ) is the same, however, so you just pass it the DSN that identifies the database. The query( ) method of Cache_DB works just like that of DB, however there are no prepare( ) and execute( ) methods in Cache_DB. query( ) returns a statement handle that supports fetchRow( ) and fetchInto( ), but the default fetch mode is DB_FETCH_ASSOC, not DB_FETCH_ORDERED.

The first time a particular SELECT statement is passed to $cache->query( ), Cache_DB executes the statement and returns the results, just like DB, but it also saves the results in a file whose name is a hash of the query. If the same SELECT statement is passed to $cache->query( ) again, Cache_DB retrieves the results from the file instead of running the query in the database.

By default, Cache_DB creates its cache files in a subdirectory of the current directory called db_query. You can change this by passing a directory name as part of an options array as a second argument to the Cache_DB constructor. This sets the cache directory to /tmp/db_query:

$cache = new Cache_DB('file',array('cache_dir' => '/tmp/'));

The first argument, file, tells Cache_DB what container to use to store the cached data. file is the default, but you need to include it here to specify the container options in the second argument. The relevant container option is cache_dir, which tells Cache_DB where to create the db_query subdirectory. Including a trailing slash is required.

By default, entries stay in the cache for one hour. You can adjust this by passing a different value (in seconds) when creating a new Cache_DB object. Here's how to keep entries in the cache for one day, 86,400 seconds:

$cache = new Cache_DB('file',array('cache_dir' => '.',
                                   'filename_prefix' => 'query_'),86400);

Because the expiration time is the third argument, you have to pass the defaults for the first two arguments as well.

The cache isn't altered if you change the database with an INSERT, UPDATE, or DELETE query. If there are cached SELECT statements that refer to data no longer in the database, you need to explicitly remove everything from the cache with the $cache->flush( ) method:

$cache->flush('db_cache');

It's very important to include the db_cache argument to flush( ). The PEAR Cache system supports dividing up the cached items into different groups, and the Cache_DB object puts everything it's keeping track of in the db_cache group. Leaving out the group argument results in deleting the files in the base cache directory (which is probably the directory you're running your script from).

The file container stores each result in a file whose name is based on an MD5 hash of the query that generated the particular result. Because MD5 is case-sensitive, the file container is case-sensitive, too. This means that if the results of SELECT * FROM zodiac are in the cache, and you run the query SELECT * from zodiac, the results aren't found in the cache, and the query is run again. Maintaining consistent capitalization, spacing, and field ordering when constructing your SQL queries results in more efficient cache usage.

Although this recipe focuses on the file container, the PEAR Cache system supports a number of other containers that hold cached data, such as shared memory, PHPLib sessions, databases via the dbx library, and msession sessions. To use a different container, pass the appropriate container name as the first argument when creating a new Cache_DB object:

$cache = new Cache_DB('shm');

PHP : Database Access - [10.16] Program: Storing a Threaded Message Board

Storing and retrieving threaded messages requires extra care to display the threads in the correct order. Finding the children of each message and building the tree of message relationships can easily lead to a recursive web of queries. Users generally look at a list of messages and read individual messages far more often then they post messages. With a little extra processing when saving a new message to the database, the query that retrieves a list of messages to display is simpler and much more efficient.

Store messages in a table structured like this:

CREATE TABLE pc_message (
  id INT UNSIGNED NOT NULL,
  posted_on DATETIME NOT NULL,
  author CHAR(255),
  subject CHAR(255),
  body MEDIUMTEXT,
  thread_id INT UNSIGNED NOT NULL,
  parent_id INT UNSIGNED NOT NULL,
  level INT UNSIGNED NOT NULL,
  thread_pos INT UNSIGNED NOT NULL,
  PRIMARY KEY(id)
);

The primary key, id, is a unique integer that identifies a particular message. The time and date that a message is posted is stored in posted_on, and author, subject, and body are (surprise!) a message's author, subject, and body. The remaining four fields keep track of the threading relationships between messages. The integer thread_id identifies each thread. All messages in a particular thread have the same thread_id. If a message is a reply to another message, parent_id is the id of the replied-to message. level is how many replies into a thread a message is. The first message in a thread has level 0. A reply to that level message has level 1, and a reply to that level 1 message has level 2. Multiple messages in a thread can have the same level and the same parent_id. For example, if someone starts off a thread with a message about the merits of BeOS over CP/M, the angry replies to that message from CP/M's legions of fans all have level 1 and a parent_id equal to the id of the original message.

The last field, thread_pos, is what makes the easy display of messages possible. When displayed, all messages in a thread are ordered by their thread_pos value.

Here are the rules for calculating thread_pos:

• The first message in a thread has thread_pos = 0.
• For a new message N, if there are no messages in the thread with the same parent as N, N's thread_pos is one greater than its parent's thread_pos.
• For a new message N, if there are messages in the thread with the same parent as N, N's thread_pos is one greater than the biggest thread_pos of all the messages with the same parent as N.
• After new message N's thread_pos is determined, all messages in the same thread with a thread_pos value greater than or equal to N's have their thread_pos value incremented by 1 (to make room for N).

The message board program, message.php, shown in Example 10-4 saves messages and properly calculates thread_pos. Sample output is shown in Figure 10-5.

Figure 10-5. A threaded message board

Example 10-4. message.php