PHP Cookbook/Database Access

From WikiContent

< PHP Cookbook(Difference between revisions)
Jump to: navigation, search
(Initial conversion from Docbook)
Current revision (13:25, 17 August 2009) (edit) (undo)
 
(16 intermediate revisions not shown.)

Current revision

PHP Cookbook


Contents

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 Recipe 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 Recipe 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',10,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());
}

$cols = OCINumCols($sth);
while (OCIFetch($sth)) {
    for ($i = 1; $i <= $cols; $i++) {
        print OCIResult($sth,$i);
        print " ";
    }
    print "\n";
}

The OCILogin( ) function connects to a given Oracle instance with a username and password. You can leave out the third argument (the instance) if the environment variable ORACLE_SID is set to the desired Oracle instance. A statement handle is returned from OCIParse( ) , and OCIExecute( ) runs the query. Each time OCIFetch( ) is called, the next row in the result is retrieved into a result buffer. The value of a particular column of the current row in the result buffer is retrieved by OCIResult( ) .

Here's the same example using PostgreSQL:

if (! $dbh = pg_connect('dbname=test user=david password=foo!bar')) {
    die("Can't connect: ".pg_errormessage());
}

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

for ($i = 0, $j = pg_numrows($sth); $i < $j; $i++) {
    $ar = pg_fetch_row($sth,$i);
    foreach ($ar as $col) {
        print "$col ";
    }
    print "\n";
}

In this case, pg_connect( ) connects to PostgreSQL using the provided database name, user, and password. The query is run by pg_exec( ) . There's no need for a separate parse and execute step as with Oracle. Because pg_fetch_row( ) retrieves a specific row from the result set into an array, you loop over all the rows (using pg_numrows( ) to get the total number of rows) and print out each element in the array.

Here's the same exercise with MySQL:

if (! $dbh = mysql_connect('localhost','david','foo!bar')) {
    die("Can't connect: ".mysql_error());
}

mysql_select_db('test');

if (! $sth = mysql_query('SELECT * FROM zodiac')) {
    die("Can't execute query: ".mysql_error());
}

while ($ar = mysql_fetch_row($sth)) {
    foreach ($ar as $col) {
        print "$col ";
    }
    print "\n";
}

First, mysql_connect( ) returns a database handle using the provided hostname, username, and password. You then use mysql_select_db( ) to indicate which database to use. The query is executed by mysql_query( ) . The mysql_fetch_row( ) function retrieves the next row in the result set and NULL when there are no more rows; use a while loop to retrieve all the rows.

Each example prints out all the data in the zodiac table, one row per line, with spaces between each field, as shown here:

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

Recipe 10.5 through Recipe 10.9 cover the basics of sending queries to the database and getting the results back, as well as using queries that change the data in the database.

There are a number of options and optimizations for each database PHP supports. Most database interfaces support persistent connections with separate connection functions. In the previous three examples, you would use OCIPLogon( ) , pg_pconnect( ), and mysql_pconnect( ) for persistent instead of single-request connections.

If you require a database-specific set of functions, the PHP online manual section for each database has many useful tips for proper configuration and use. If you can, use a database abstraction layer instead. Starting with Recipe 10.4, all the SQL examples use the PEAR DB database abstraction layer, which minimizes the amount of code that has to change to make the examples work on different databases. Here's code that can display all the rows in the zodiac table using DB and MySQL:

require 'DB.php';
$dbh = DB::connect('mysql://david:foo!bar@localhost/test');
$sth = $dbh->query('SELECT * FROM zodiac');
while ($row = $sth->fetchRow()) {
    print join(' ',$row)."\n";
}

The only thing that needs to change to make this code work on another database is the argument passed to DB::connect( ) , which specifies what database to connect to. However, a database abstraction layer doesn't make SQL completely portable. Each database vendor generally has custom SQL extensions that enable handy features on one database and don't work at all on another database.

While it's possible to write SQL that works on different databases with a minimum of changes, tuning a database for speed and efficiency is not portable. Having portable database interactions can be a useful goal, but it needs to be balanced with the likelihood of your code being used with multiple databases. If you're writing code for wide distribution, working with many databases is a plus. If your code is an internal project, however, you probably don't need to be as concerned with database independence.

Whatever database you're using, you're probably going to be capturing information from HTML form fields and storing that information in the database. Some characters, such as the apostrophe and backslash, have special meaning in SQL, so you have to be careful if your form data contains those characters. PHP has a feature called "magic quotes" to make this easier. When the configuration setting magic_quotes_gpc is on, variables coming from GET requests, POST requests, and cookies have single quotes, double quotes, backslashes, and nulls escaped with a backslash. You can also turn on magic_quotes_runtime to automatically escape quotes, backslashes, and nulls from external sources such as database queries or text files. For example, if magic_quotes_runtime is on, and you read a file into an array with file( ), the special characters in that array are backslash-escaped.

For example, if $_REQUESTS['excuse'] is "Ferris wasn't sick," and magic_quotes_gpc is on, this query executes successfully:

$dbh->query("INSERT INTO excuses (truth) VALUES ('" . $_REQUESTS['excuse'] . ')');

Without the magic quotes, the apostrophe in "wasn't" signals the end of the string to the database, and the query produces a syntax error. To instruct magic_quotes_gpc and magic_quotes_runtime to escape single quotes with another single quote instead of a backslash, set magic_quotes_sybase to on. Recipe 10.10 discusses escaping special characters in queries. General debugging techniques you can use to handle errors resulting from database queries are covered in Recipe 10.11.

The remaining recipes cover database tasks that are more involved than just simple queries. Recipe 10.12 shows how to automatically generate unique ID values you can use as record identifiers. Recipe 10.13 covers building queries at runtime from a list of fields. This makes it easier to manage INSERT and UPDATE queries that involve a lot of columns. Recipe 10.14 demonstrates how to display links that let you page through a result set, displaying a few records on each page. To speed up your database access, you can cache queries and their results, as explained in Recipe 10.15.

Using Text-File Databases

Problem

You want a lightweight way to store information between requests.

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

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.

See Also

Recipe 5.8 discusses serializing data; Recipe 18.25 goes into the details of file locking; documentation on flock( ) at http://www.php.net/flock, serialize( ) at http://www.php.net/serialize, and unserialize( ) at http://www.php.net/unserialize.

Using DBM Databases

Problem

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

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, $dbh);
  print "Updated the flounder count.";
} else {
  dba_insert('flounder',1, $dbh);
  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, $dbh);
   print "$key: $value\n";
}

dba_close($dbh);

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

dba_replace($user,$password,$dbh) or die("Can't write to database $data_file");

dba_close($dbh);

The dba_open( ) function returns a handle to a DBM file (or false on error). It takes three arguments. The first is the filename of the DBM file. The second argument is the mode for opening the file. A mode of 'r' opens an existing database for read-only access, and 'w' opens an existing database for read-write access. The 'c' mode opens a database for read-write access and creates the database if it doesn't already exist. Last, 'n' does the same thing as 'c', but if the database already exists, 'n' empties it. The third argument to dba_open( ) is which DBM handler to use; this example uses 'gdbm'. To find what DBM handlers are compiled into your PHP installation, look at the "DBA" section of the output from phpinfo( ). The "Supported handlers" line gives you your choices.

To find if a key has been set in a DBM database, use dba_exists( ) . It takes two arguments: a string key and a DBM file handle. It looks for the key in the DBM file and returns true if it finds the key (or false if it doesn't). The dba_replace( ) function takes three arguments: a string key, a string value, and a DBM file handle. It puts the key/value pair into the DBM file. If an entry already exists with the given key, it overwrites that entry with the new value.

To close a database, call dba_close( ) . A DBM file opened with dba_open( ) is automatically closed at the end of a request, but you need to call dba_close( ) explicitly to close persistent connections created with dba_popen( ).

You can use dba_firstkey( ) and dba_nextkey( ) to iterate through all the keys in a DBM file and dba_fetch( ) to retrieve the values associated with each key. This program calculates the total length of all passwords in a DBM file:

$data_file = '/tmp/users.db';
$total_length = 0;
if (! ($dbh = dba_open($data_file,'r','gdbm'))) {
    die("Can't open database $data_file");
}

$k = dba_firstkey($dbh);
while ($k) {
    $total_length += strlen(dba_fetch($k,$dbh));
    $k = dba_nextkey($dbh);
}

print "Total length of all passwords is $total_length characters.";

dba_close($dbh);

The dba_firstkey( ) function initializes $k to the first key in the DBM file. Each time through the while loop, dba_fetch( ) retrieves the value associated with key $k and $total_length is incremented by the length of the value (calculated with strlen( )). With dba_nextkey( ), $k is set to the next key in the file.

You can use serialize( ) to store complex data in a DBM file, just like in a text file. However, the data in the DBM file can be indexed by a key:

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

// read in and unserialize the data
if ($exists = dba_exists($_REQUEST['username'], $dbh)) {
    $serialized_data = dba_fetch($_REQUEST['username'], $dbh) or die($php_errormsg);
    $data = unserialize($serialized_data);
} else {
    $data = array();
}

// update values 
if ($_REQUEST['new_password']) {
    $data['password'] = $_REQUEST['new_password'];
}
$data['last_access'] = time();

// write data back to file
if ($exists) {
    dba_replace($_REQUEST['username'],serialize($data), $dbh);
} else {
    dba_insert($_REQUEST['username'],serialize($data), $dbh);
}

dba_close($dbh);

While this example can store multiple users' data in the same file, you can't search, for example, a user's last access time, without looping through each key in the file. Structured data like this belongs in a SQL database.

Each DBM handler has different behavior in some areas. For example, GDBM provides internal locking. If one process has opened a GDBM file in read-write mode, other calls to dba_open( ) to open the same file in read-write mode will fail. The DB3 handler, however, provides no such internal locking; you need to do that with additional code, as discussed for text files in Recipe 18.25. Two DBA functions are also database-specific: dba_optimize( ) and dba_sync( ). The dba_optimize( ) function calls a handler-specific DBM file-optimization function. Currently, this is implemented only for GDBM, for which its gdbm_reorganize( ) function is called. The dba_sync( ) function calls a handler-specific DBM file synchronizing function. For DB2 and DB3, their sync( ) function is called. For GDBM, its gdbm_sync( ) function is called. Nothing happens for other DBM handlers.

Using a DBM database is a step up from a text file but it lacks most features of a SQL database. Your data structure is limited to key/value pairs, and locking robustness varies greatly depending on the DBM handler. Still, DBM handlers can be a good choice for heavily accessed read-only data; for example, the Internet Movie Database uses DBM databases.

See Also

Recipe 5.8 discusses serializing data; Recipe 18.25 studies the details of file locking; documentation on the DBA functions at http://www.php.net/dba; for more information on the DB2 and DB3 DBM handlers, see http://www.sleepycat.com/faq.html#program; for GDBM, check out http://www.gnu.org/directory/gdbm.html or http://www.mit.edu:8001/afs/athena.mit.edu/project/gnu/doc/html/gdbm_toc.html; CDB info is at http://cr.yp.to/cdb.html; the Internet Movie Database's technical specifications are at http://us.imdb.com/Help/Classes/Master/tech-info.

Connecting to a SQL Database

Problem

You want access to a SQL database.

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()); }

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

See Also

Recipe 10.5 for querying a SQL database; Recipe 10.7 for modifying a SQL database; Pear DB at http://pear.php.net/package-info.php?package=DB; documentation on DB::connect( ) at http://pear.php.net/manual/en/core.db.tut_connect.php and http://pear.php.net/manual/en/core.db.connect.php; information on DSNs at http://pear.php.net/manual/en/core.db.tut_dsn.php.

Querying a SQL Database

Problem

You want to retrieve some data from your database.

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

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']));

Recipe 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

See Also

Recipe 10.4 for connecting to a SQL database; Recipe 10.7 for modifying a SQL database; Recipe 10.10 details how to quote data for safe inclusion in queries; documentation on DB::query( ) at http://pear.php.net/manual/en/core.db.tut_query.php and http://pear.php.net/manual/en/core.db.query.php, fetching at http://pear.php.net/manual/en/core.db.tut_fetch.php, DB_Result::fetchRow( ) at http://pear.php.net/manual/en/core.db.fetchrow.php, DB_Result::fetchInto( ) at http://pear.php.net/manual/en/core.db.fetchinto.php, and DB::setFetchMode( ) at http://pear.php.net/manual/en/core.db.setfetchmode.php.

Retrieving Rows Without a Loop

Problem

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

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'");

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:

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

The parameter array is the second argument to each of these functions, except getCol( ) and getAssoc( ). For these two functions, the parameter array is the third argument. The second argument to getCol( ) is a column number to return if you don't want the first column (column number 0). For example, this returns the values of the planet column:

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

The second argument to getAssoc( ) is a boolean that tells the function whether to force the values in the associative array it returns to be arrays themselves even if they could be scalars. Take this query for example:

$assoc = $dbh->getAssoc(
    "SELECT sign,symbol FROM zodiac WHERE element LIKE 'water'");
print_r($assoc);
Array
               (
                   [Cancer] => Crab
                   [Scorpio] => Scorpion
                   [Pisces] => Fishes
               )
            

Because the query passed to getAssoc( ) asks only for two columns, the first column is the array key, and the second column is the scalar array value. Here's how to force the array values to be one-element arrays:

$assoc = $dbh->getAssoc(
    "SELECT sign,symbol FROM zodiac WHERE element LIKE 'water'",true);
print_r($assoc);
Array
               (
                   [Cancer] => Array
                       (
                           [0] => Crab
                       )
                   [Scorpio] => Array
                       (
                           [0] => Scorpion
                       )
                   [Pisces] => Array
                       (
                           [0] => Fishes
                       )
               )
            

Just as fetchRow( ) and fetchInto( ) do, getRow( ), getAssoc( ), and getAll( ) put data in numeric arrays by default. You can pass them a fetch mode (the third argument to getRow( ) or getAll( ), the fourth argument to getAssoc( )). They also respect the fetch mode set by DB::setFetchMode( ).

See Also

Recipe 10.5 for more on the fetch mode; documentation on fetching at http://pear.php.net/manual/en/core.db.tut_fetch.php, DB::getRow( ) at http://pear.php.net/manual/en/core.db.getrow.php, DB::getAll( ) at http://pear.php.net/manual/en/core.db.getall.php, DB::getOne( ) at http://pear.php.net/manual/en/core.db.getone.php, DB::getCol( ) at http://pear.php.net/manual/en/core.db.getcol.php, and DB::getAssoc( ) at http://pear.php.net/manual/en/core.db.getassoc.php.

Modifying Data in a SQL Database

Problem

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

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');

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'));

See Also

Recipe 10.4 for connecting to a SQL database; Recipe 10.5 for querying a SQL database; Recipe 10.8 discusses prepare( ) and execute( ) in detail; documentation on DB::query( ) at http://pear.php.net/manual/en/core.db.query.php, DB::prepare( ) at http://pear.php.net/manual/en/core.db.prepare.php, and DB::execute( ) at http://pear.php.net/manual/en/core.db.execute.php.

Repeating Queries Efficiently

Problem

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

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

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.

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.

Finding the Number of Rows Returned by a Query

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.

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();

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.

See Also

Documentation on DB_Result::numRows( ) at http://pear.php.net/manual/en/core.db.numrows.php and DB::affectedRows( ) at http://pear.php.net/manual/en/core.db.affectedrows.php.

Escaping Quotes

Problem

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

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

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

See Also

Documentation on DB::quote( ) at http://pear.php.net/manual/en/core.db.quote.php and magic quotes at http://www.php.net/manual/en/ref.info.php#ini.magic-quotes-gpc.

Logging Debugging Information and Errors

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.

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'");

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();
    ob_end_clean();
    error_log('Database Error: '.$dump);
}

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

This includes all of the error object's fields in the error log message:

Database Error: db_error Object
(
    [error_message_prefix] => 
    [mode] => 16
    [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] => pc_log_error
)

You can also have a DB_Error generate an internal PHP error with PEAR_ERROR_TRIGGER:

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

With the PEAR_ERROR_TRIGGER constant, setErrorHandling( ) uses PHP's trigger_error( ) function to generate an internal error. This error is handled by PHP's default error handler or a user-defined error handler set by set_error_handler( ). By default, the internal error is an E_USER_NOTICE :

<br />
<b>Notice</b>:  DB Error: no such field in <b>/usr/local/lib/php/PEAR.php</b> \
on line <b>593</b><br />

Make the error an E_USER_WARNING or E_USER_ERROR by passing a second argument to setErrorHandling( ):

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

If the error is an E_USER_ERROR , program execution terminates after displaying the error message:

<br />
<b>Fatal error</b>:  DB Error: no such field in <b>/usr/local/lib/php/PEAR.php</b> 
on line <b>593</b><br />

See Also

Recipe 8.13 for a discussion of output buffering; Recipe 8.16 through Recipe 8.18 for discussions on error handling and writing a custom error handler; documentation on DB::isError( ) at http://pear.php.net/manual/en/core.db.iserror.php, the PEAR_Error class at http://pear.php.net/manual/en/class.pear-error.php, trigger_error( ) at http://www.php.net/trigger-error, and set_error_handler( ) at http://www.php.net/set-error-handler.

Assigning Unique ID Values Automatically

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.

Solution

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

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

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.

See Also

Documentation on DB::nextId( ) at http://pear.php.net/manual/en/core.db.nextid.php, DB::createSequence( ) at http://pear.php.net/manual/en/core.db.createsequence.php, and DB::dropSequence( ) at http://pear.php.net/manual/en/core.db.dropsequence.php.

Building Queries Programmatically

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.

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

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

If you can't use DB::autoPrepare( ), the array-manipulation techniques shown in the Solution accomplish the same thing. If you use sequence-generated integers as primary keys, you can combine the two query-construction techniques into one function. That function determines whether a record exists and then generates the correct query, including a new ID, as shown in the pc_build_query( ) function in Example 10-1.

Example 10-1. pc_build_query( )

function pc_build_query($dbh,$key_field,$fields,$table) {

    if (! empty($_REQUEST[$key_field])) {
        $update_fields = array();
        foreach ($fields as $field) {
            $update_fields[] = "$field = ".$dbh->quote($_REQUEST[$field]);
        }
        return "UPDATE $table SET " . join(',',$update_fields) .
               " WHERE $key_field = ".$_REQUEST[$key_field];
    } else {
        $insert_values = array();
        foreach ($fields as $field) {
            $insert_values[] = $dbh->quote($_REQUEST[$field]);
        }
        $next_id = $dbh->nextId($table);
        return "INSERT INTO $table ($key_field," . join(',',$fields) . 
               ") VALUES ($next_id," . join(',',$insert_values) . ')';
    }
}

Using this function, you can make a simple page to edit all the information in the zodiac table:

require 'DB.php';

$dbh = DB::connect('mysql://test:@localhost/test');
$dbh->setFetchMode(DB_FETCHMODE_OBJECT);

$fields = array('sign','symbol','planet','element',
                'start_month','start_day','end_month','end_day');

switch ($_REQUEST['cmd']) {
 case 'edit':
     $row = $dbh->getRow('SELECT ' . join(',',$fields) . 
                         " FROM zodiac WHERE id = ?",array($_REQUEST['id']));
 case 'add':
     print '<form method="post" action="'.$_SERVER['PHP_SELF'].'">';
     print '<input type="hidden" name="cmd" value="save">';
     print '<table>';
     if ('edit' == $_REQUEST['cmd']) {
         printf('<input type="hidden" name="id" value="%d">',
                $_REQUEST['id']);
     }
     foreach ($fields as $field) {
         if ('edit' == $_REQUEST['cmd']) {
             $value = htmlspecialchars($row->$field);
         } else {
             $value = '';
         }
         printf('<tr><td>%s: </td><td><input type="text" name="%s" value="%s">',
                $field,$field,$value);
         printf('</td></tr>');
     }
     print '<tr><td></td><td><input type="submit" value="Save"></td></tr>';
     print '</table></form>';
     break;
 case 'save':
     $sql = pc_build_query($dbh,'id',$fields,'zodiac');
     if (DB::isError($sth = $dbh->query($sql))) {
         print "Couldn't add info: ".$sth->getMessage();
     } else {
         print "Added info.";
     }
     print '<hr>';
 default:
     $sth = $dbh->query('SELECT id,sign FROM zodiac');
     print '<ul>';
     while ($row = $sth->fetchRow()) {
         printf('<li> <a href="%s?cmd=edit&id=%s">%s</a>',
                $_SERVER['PHP_SELF'],$row->id,$row->sign);
     }
     print '<hr><li> <a href="'.$_SERVER['PHP_SELF'].'?cmd=add">Add New</a>';
     print '</ul>';
     break;
}

The switch statement controls what action the program takes based on the value of $_REQUEST['cmd']. If $_REQUEST['cmd'] is add or edit, the program displays a form with textboxes for each field in the $fields array, as shown in Figure 10-1. If $_REQUEST['cmd'] is edit, values for the row with the supplied $id are loaded from the database and displayed as defaults. If $_REQUEST['cmd'] is save, the program uses pc_build_query( ) to generate an appropriate query to either INSERT or UPDATE the data in the database. After saving (or if no $_REQUEST['cmd'] is specified), the program displays a list of all zodiac signs, as shown in Figure 10-2.

Figure 10-1. Adding and editing a record

Adding and editing a record

Figure 10-2. Listing records

Listing records

Whether pc_build_query( ) builds an INSERT or UPDATE statement is based on the presence of the request variable $_REQUEST['id'] (because id is passed in $key_field). If $_REQUEST['id'] is not empty, the function builds an UPDATE query to change the row with that ID. If $_REQUEST['id'] is empty (or it hasn't been set at all), the function generates a new ID with nextId( ) and uses that new ID in an INSERT query that adds a row to the table.

See Also

Documentation on DB::autoPrepare( ) at http://pear.php.net/manual/en/core.db.autoprepare.php; new versions of PEAR DB are available at http://pear.php.net/package-info.php?package=DB.

Making Paginated Links for a Series of Records

Problem

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

Solution

Use the PEAR DB_Pager class:

require 'DB/Pager.php';

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

$sth = $dbh->limitQuery('SELECT * FROM zodiac ORDER BY id',$offset,
$per_page);
// display each row on this page  
while ($v = $sth->fetchRow()) {
    print "$v->sign, $v->symbol ($v->id)<br>";
}
$data = DB_Pager::getData($offset, $per_page, $sth->numRows());

// 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);

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

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.

Example 10-2. pc_print_link( )

function pc_print_link($inactive,$text,$offset='') {

    if ($inactive) {
        printf('<font color="#666666">%s</font>',$text);
    } else {
        printf('<a href="%s?offset=%d">%s</a>',$_SERVER['PHP_SELF'],$offset,$text);
    }
}

Example 10-3. pc_indexed_links( )

function pc_indexed_links($total,$offset,$per_page) {
    $separator = ' | ';
    
    // print "<<Prev" link
    pc_print_link($offset == 1, '&lt;&lt;Prev', $offset - $per_page);


    // print all groupings except last one
    for ($start = 1, $end = $per_page;
         $end < $total;
         $start += $per_page, $end += $per_page) {

        print $separator;
        pc_print_link($offset == $start, "$start-$end", $start);
    }

    /* print the last grouping -
     * at this point, $start points to the element at the beginning
     * of the last grouping
     */
    
    /* the text should only contain a range if there's more than
     * one element on the last page. For example, the last grouping
     * of 11 elements with 5 per page should just say "11", not "11-11"
     */
    $end = ($total > $start) ? "-$total" : '';

    print $separator;
    pc_print_link($offset == $start, "$start$end", $start);
    
    // print "Next>>" link
    print $separator;
    pc_print_link($offset == $start, 'Next&gt;&gt;',$offset + $per_page);
}

To use these functions, retrieve the correct subset of the data using DB::modifyLimitQuery( ) and then print it out. Call pc_indexed_links( ) to display the indexed links:

$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);

After connecting to the database, you need to make sure $offset has an appropriate value. $offset is the beginning record in the result set that should be displayed. To start at the beginning of the result set, $offset should be 1. The variable $per_page is set to how many records to display on each page, and $total is the total number of records in the entire result set. For this example, all the Zodiac records are displayed, so $total is set to the count of all the rows in the entire table.

The SQL query that retrieves information in the proper order is:

SELECT * FROM zodiac ORDER BY id

Use modifyLimitQuery( ) to restrict the rows being retrieved. You'll want to retrieve $per_page rows, starting at $offset - 1, because the first row is 0, not 1, to the database. The modifyLimitQuery( ) method applies the correct database-specific logic to restrict what rows are returned by the query.

The relevant rows are retrieved by $dbh->getAll($sql), and then information is displayed from each row. After the rows, pc_indexed_links( ) provides navigation links. The output when $offset is not set (or is 1) is shown in Figure 10-4.

Figure 10-4. Paginated results with pc_indexed_links( )

Paginated results with pc_indexed_links( )

In Figure 10-4, "6-10", "11-12", and "Next>>" are links to the same page with adjusted $offset arguments, while "<<Prev" and "1-5" are greyed out, because what they would link to is what's currently displayed.

See Also

Information on DB_Pager at http://pear.php.net/package-info.php?package=DB_Pager.

Caching Queries and Results

Problem

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

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

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');

See Also

Information about the PEAR Cache system and the different containers at http://pear.php.net/package-info.php?package=Cache.

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

A threaded message board

Example 10-4. message.php

require 'DB.php';

// a helpful database debugging function
function log_die($ob) { print '<pre>'; print_r($ob); print '</pre>'; }

// connect to the database
$dbh = DB::connect('mysql://test:@localhost/test') or die("Can't connect");
if (DB::isError($dbh)) { log_die($dbh); }
$dbh->setFetchMode(DB_FETCHMODE_OBJECT);
PEAR::setErrorHandling(PEAR_ERROR_CALLBACK,'log_die');

// The value of $_REQUEST['cmd'] tells us what to do
switch ($_REQUEST['cmd']) {
case 'read':                      // read an individual message
     pc_message_read();
     break;
case 'post':                      // display the form to post a message
     pc_message_post();
     break;
case 'save':                      // save a posted message
     if (pc_message_validate()) { // if the message is valid,
         pc_message_save();       // then save it
         pc_message_list();       // and display the message list
     } else {
         pc_message_post();       // otherwise, redisplay the posting form
     }
     break;
case 'list':                      // display a message list by default
default:
     pc_message_list();
     break;
}

// pc_message_save() saves the message to the database
function pc_message_save() {
    global $dbh;

    $parent_id = intval($_REQUEST['parent_id']);

    /* MySQL syntax for making sure pc_message doesn't change while
     * we're working with it. We also have to lock the tables that
     * hold the thread and pc_message sequences
     */
    $dbh->query('LOCK TABLES pc_message WRITE, thread_seq WRITE, pc_message_seq WRITE');

    // is this message a reply?
    if ($parent_id) {

        // get the thread, level, and thread_pos of the parent message 
        $parent = $dbh->getRow("SELECT thread_id,level,thread_pos
                                FROM pc_message 
                                WHERE id = $parent_id");

        // a reply's level is one greater than its parents 
        $level = $parent->level + 1;

        /* what's the biggest thread_pos in this thread among messages
           with the same parent? */
        $thread_pos = $dbh->getOne("SELECT MAX(thread_pos) FROM pc_message 
            WHERE thread_id = $parent->thread_id AND parent_id = $parent_id");

        // are there existing replies to this parent?
        if ($thread_pos) {
            // this thread_pos goes after the biggest existing one
            $thread_pos++;
        } else {
            // this is the first reply, so put it right after the parent 
            $thread_pos = $parent->thread_pos + 1;
        }

        /* increment the thread_pos of all messages in the thread that
           come after this one */
        $dbh->query("UPDATE pc_message SET thread_pos = thread_pos + 1 
            WHERE thread_id = $parent->thread_id AND thread_pos >= $thread_pos");

        // the new message should be saved with the parent's thread_id 
        $thread_id = $parent->thread_id;
    } else {
        // the message is not a reply, so it's the start of a new thread 
        $thread_id = $dbh->nextId('thread');
        $level = 0;
        $thread_pos = 0;
    }
    
    // get a new id for this message 
    $id = $dbh->nextId('pc_message');

    /* insert the message into the database. Using prepare() and execute()
       makes sure that all fields are properly quoted */
    $prh = 
        $dbh->prepare("INSERT INTO pc_message (id,thread_id,parent_id,
                       thread_pos,posted_on,level,author,subject,body) 
                       VALUES (?,?,?,?,NOW(),?,?,?,?)");

    $dbh->execute($prh,array($id,$thread_id,$parent_id,$thread_pos,$level,
                             $_REQUEST['author'],$_REQUEST['subject'],
                             $_REQUEST['body']));


    // Tell MySQL that others can use the pc_message table now
    $dbh->query('UNLOCK TABLES');
}

// pc_message_list() displays a list of all messages
function pc_message_list() {
    global $dbh;

    print '<h2>Message List</h2><p>';

    /* order the messages by their thread (thread_id) and their position
       within the thread (thread_pos) */
    $sth = $dbh->query("SELECT id,author,subject,LENGTH(body) AS body_length,
                       posted_on,level FROM pc_message
                       ORDER BY thread_id,thread_pos");
    while ($row = $sth->fetchRow()) {
        // indent messages with level > 0
        print str_repeat('&nbsp;,4 * $row->level);
        // print out information about the message with a link to read it
        print<<<_HTML_
<a href="$_SERVER[PHP_SELF]?cmd=read&id=$row->id">$row->subject</a> by 
$row->author @ $row->posted_on ($row->body_length bytes)
<br>
_HTML_;
    }

    // provide a way to post a non-reply message
    printf('<hr><a href="%s?cmd=post">Start a New Thread</a>',
           $_SERVER['PHP_SELF']);
}

// pc_message_read() displays an individual message
function pc_message_read() {
    global $dbh;
    
    /* make sure the message id we're passed is an integer and really
       represents a message */
    $id = intval($_REQUEST['id']) or die("Bad message id");
    if (! ($msg = $dbh->getRow(
        "SELECT author,subject,body,posted_on FROM pc_message WHERE id = $id"))) {
        die("Bad message id");
    }

    /* don't display user-entered HTML, but display newlines as
       HTML line breaks */
    $body = nl2br(strip_tags($msg->body));

    // display the message with links to reply and return to the message list
    print<<<_HTML_
<h2>$msg->subject</h2>
<h3>by $msg->author</h3>
<p>
$body
<hr>
<a href="$_SERVER[PHP_SELF]?cmd=post&parent_id=$id">Reply</a>
<br>
<a href="$_SERVER[PHP_SELF]?cmd=list">List Messages</a>
_HTML_;
}

// pc_message_post() displays the form for posting a message
function pc_message_post() {
    global $dbh,$form_errors;
    
    foreach (array('author','subject','body') as $field) {
        // escape characters in default field values
        $$field = htmlspecialchars($_REQUEST[$field]);
        // make the error messages display in red
        if ($form_errors[$field]) {
            $form_errors[$field] = '<font color="red">' . 
                $form_errors[$field] . '</font><br>';
        }
    }

    // is this message a reply
    if ($parent_id = intval($_REQUEST['parent_id'])) {

        // send the parent_id along when the form is submitted
        $parent_field = 
            sprintf('<input type="hidden" name="parent_id" value="%d">',
                    $parent_id);

        // if no subject's been passed in, use the subject of the parent
        if (! $subject) {
            $parent_subject = $dbh->getOne('SELECT subject FROM pc_message
                                        WHERE id = ?',array($parent_id));
            /* prefix 'Re: ' to the parent subject if it exists and
               doesn't already have a 'Re:' */
            $subject = htmlspecialchars($parent_subject);
            if ($parent_subject && (! preg_match('/^re:/i',$parent_subject))) {
                $subject = "Re: $subject";
            }
        }
    }

    // display the posting form, with errors and default values
    print<<<_HTML_
<form method="post" action="$_SERVER[PHP_SELF]">
<table>
<tr>
 <td>Your Name:</td>
 <td>$form_errors[author]<input type="text" name="author" value="$author">
</td>
<tr>
 <td>Subject:</td>
 <td>$form_errors[subject]<input type="text" name="subject" value="$subject">
</td>
<tr>
 <td>Message:</td>
 <td>$form_errors[body]<textarea rows="4" cols="30" wrap="physical" 
name="body">$body</textarea>
</td>
<tr><td colspan="2"><input type="submit" value="Post Message"></td></tr>
</table>
$parent_field
<input type="hidden" name="cmd" value="save">
</form>

_HTML_;
}

// pc_message_validate() makes sure something is entered in each field
function pc_message_validate() {
    global $form_errors;

    $form_errors = array();

    if (! $_REQUEST['author']) {
        $form_errors['author'] = 'Please enter your name.';
    }
    if (! $_REQUEST['subject']) {
        $form_errors['subject'] = 'Please enter a message subject.';
    }
    if (! $_REQUEST['body']) {
        $form_errors['body'] = 'Please enter a message body.';
    }

    if (count($form_errors)) {
        return false;
    } else {
        return true;
    }
}

To properly handle concurrent usage, pc_message_save( ) needs exclusive access to the msg table between the time it starts calculating the thread_pos of the new message and when it actually inserts the new message into the database. We've used MySQL's LOCK TABLE and UNLOCK TABLES commands to accomplish this. With other databases, the syntax may vary, or you may need to start a transaction at the beginning of the function and commit the transaction at the end.

The level field can be used when displaying messages to limit what you retrieve from the database. If discussion threads become very deep, this can help prevent your pages from growing too large. For example, here's how to display just the first message in each thread and any replies to that first message:

$sth = $dbh->query(
    "SELECT * FROM msg WHERE level <= 1 ORDER BY thread_id,thread_pos");
while ($row = $sth->fetchRow()) {
    // display each message
}

If you're interested in having a discussion group on your web site, you may want to use one of the existing PHP message board packages. The most popular is Phorum (http://www.phorum.org/), and there are a number of others listed at http://www.zend.com/apps.php?CID=261 .

Personal tools