Section 6.4. PEAR DB

6.4. PEAR DB

The most commonly used PEAR package for database access is PEAR DB. DB is a database abstraction layer that provides a single API for querying most of the databases supported by PHP, as well as some more database-specific things in a portable way, such as sequences and error handling. PEAR DB itself is written in PHP, and has drivers for most of PHP's database extensions.

In this section, you learn how to use PEAR DB, and when it makes sense to use PEAR DB instead of using one of PHP's database extensions natively.

6.4.1. Obtaining PEAR DB

To install PEAR DB, you need the PEAR Installer that is installed along with PHP. Use the following command:

 $ pear install DB

If you have problems, see Chapter 10, "Using PEAR."

6.4.2. Pros and Cons of Database Abstraction

The two main advantages of using a database abstraction layer such as PEAR DB are

A single API is easy to remember. You are more productive when you spend less time looking up the documentation.
A single API allows other components to use the DB API for generic DBMS access, without worrying about back-end specifics.

Because DB is implemented in PHP, these advantages come at a cost:

A layer written in PHP is slower than using built-in PHP functions, especially if running without an opcode cache.
The extra layer of code adds complexity and potential error sources.

Deciding the right choice for you depends on your needs. Requirements that speak for using PEAR DB or another form of abstracted DBMS access are portability, reusability, rapid development, or that you already use other PEAR packages.

Some requirements that speak against using PEAR DB are high performance requirements where the database itself would not be the bottleneck, a significant buy-in with some specific DBMS product, or a policy of avoiding external dependencies.

6.4.3. Which Features Are Abstracted?

DB does not abstract everything, such as SQL or database schema grammar. The features it does abstract are

Database connections
Fetching results
Binding input variables (prepare/execute)
Error reporting
Sequences
Simple database and table descriptions
Minor quirks and differences

The following are not abstracted, either because they are outside the scope of DB, too expensive, or simply not yet implemented:

SQL syntax
Database schemas (CREATE TABLE, for example)
Field types
Character encodings
Privilege management (GRANT, and so on)

Database schemas and field types are abstracted by the MDB package, which is another database abstraction layer found in PEAR. MDB is a merge of Metabase and DB, two of the most popular database abstraction layers for PHP. The intent behind MDB has been to merge with the next major DB release.

6.4.4. Database Connections

PEAR DB borrows the term data source name (DSN) from ODBC to describe how a database is addressed.

6.4.4.1 Data Source Names

DSNs use the uniform resource identificator (URI) format. This is an example DSN that refers to a mysql database on localhost called "world":

 mysql://user:password@host/world

The full DSN format is a lot more verbose than this, and most fields are optional. In fact, only the database extension name is mandatory for all drivers. The database extension determines which DB driver is used, and which other DSN fields are required depends on the driver.

These are some example DSNs:

 dbext dbext://host dbext://host/database dbext://user:pw@host/database dbext://user:pw@host dbext(dbtype)://user:pw@protocol+host:port//db/file.db?mode=x

dbext is the database back-end driver. The drivers bundled with DB are dbase, fbsql, ibase, ifx, msql, mssql, mysql, mysqli, oci8, odbc, pgsql, sqlite, and sybase. It is possible to install additional drivers as separate packages.

The syntax of the DSN URI is the same for all drivers, but which fields are required varies depending on the back-end database's features. This section uses mysql for examples. Consult the PEAR DB online manual for DSN details.

6.4.4.2 Establishing Connections

Here is an example of how to establish a database connection using PEAR DB:

 <?php require_once 'DB.php'; $dbh = DB::connect("mysql://test@localhost/test"); if (DB::isError($dbh)) {     print "Connect failed!\n";     print "Error message: " . $dbh->getMessage() . "\n";     print "Error details: " . $dbh->getUserInfo() . "\n";     exit(1); } print "Connect ok!\n";

This script connects to the "test" database using the mysql extension. The database server runs on localhost, and the connection will be opened as user "test" with no password.

DB.php is the only file you need to include to use PEAR DB. DB::connect() is a factory method that includes the right file for your driver. It creates a driver object, initializes it, and calls the native function for creating the actual connection. DB::connect() will raise a PEAR error on failure.

For SQLite databases, all you need to specify is the PHP extension and the database file, like this:

 sqlite:///test.db

Here, "test.db" will be opened from the current directory. To specify the full path, the database file name must be prefixed with yet another slash, like this:

 sqlite:////var/lib/sqlite/test.db

6.4.4.3 Configuration Options

You can configure some of the DB behavior per connection with the setOption() method. Options are parameters that are less frequently used than the ones used in the DB::connect() factory method:

 $dbh->setOption("autofree", true);

Each option has a name and a value. The value may be of any type, but the currently implemented options exclusively use string and integer values.

Most configuration options may be changed at any time, except for the ones that affect the database connection (persistent and ssl).

The options supported by DB are the following:

persistent. (Boolean) Whether DB uses a persistent connection to the backend DBMS.
ssl. (Boolean) Whether to use SSL (secure sockets layer) connections to the database (may not be available).
debug. (integer) For adjusting debug information. 0 means no debug info, and 1 means some debug info.
seqname_format. (string) Table or sequence name format used by emulated DB sequences. *printf-style format string, where %s is substituted by the DB sequence name. Defaults to %s_seq. Changing this option after populating your database may completely break your application, so be careful!
autofree. (Boolean) Whether to automatically free result sets after queries are finished (instead of PHP doing it at the end of the request if you forget to do it yourself).
portability. (integer) Bitmap telling what features DB should emulate for inter-DBMS portability; see the "Portability Features" section later in this chapter for more details.

6.4.5. Executing Queries

There are four ways of running queries with PEAR DB. All are performed by calling different methods in the connection object: query(), limitQuery(), prepare()/execute(), or simpleQuery(). An explanation of each follows.

6.4.5.1 `query($query, $params = array())`

This is the default way of calling queries if you don't need to limit the number of results. If the result contains one or more rows, query() returns a result object; otherwise, it returns a Boolean indicating success.

Here is an example that returns results:

 <?php require_once 'DB.php'; PEAR::setErrorHandling(PEAR_ERROR_DIE, "%s<br />\n"); $dbh = DB::connect("mysql://test@localhost/world"); $result = $dbh->query("SELECT Name FROM City WHERE " .                       "CountryCode = 'NOR'"); while ($result->fetchInto($row)) {     print "$row[0]<br />\n"; }

This example uses the "world" database referenced in the previous MySQL section.

Here, the query() method returns a DB_result object. DB_result's fetchInto() method retrieves a row of results and stores it in the $row array. When the last row has been read, fetchInto() returns null. Continue reading for more details about fetchInto() and the other fetch methods. The query() method also accepts an additional parameter for passing input parameters to the query:

 <?php require_once 'DB.php'; PEAR::setErrorHandling(PEAR_ERROR_DIE, "%s<br />\n"); $dbh = DB::connect("mysql://test@localhost/world"); $code = 'NOR'; $result = $dbh->query("SELECT Name FROM City WHERE CountryCode = ?", $code); while ($result->fetchInto($row)) {     print "$row[0]<br />\n"; }

This example does exactly the same thing as the previous one, except it uses prepare/execute or bind if the database supports it. The other advantage of passing input parameters like this is that you need not worry about quoting. DB automatically quotes your parameters for you as necessary.

6.4.5.2 `limitQuery($query, $from, $count, $params = array())`

This method is almost identical to query(), except that it takes a "from" and "count" parameter that limits the result set to a specific offset range. Here's an example:

 <?php require_once 'DB.php'; $from = isset($_GET['from']) ? (int)$_GET['from'] : 0; $show = isset($_GET['show']) ? (int)$_GET['show'] : 0; $from = $from ? $from : 0; $show = $show ? $show : 10; PEAR::setErrorHandling(PEAR_ERROR_DIE, "%s<br />\n"); $dbh = DB::connect("mysql://test@localhost/world"); $result = $dbh->limitQuery("SELECT Name, Population FROM City ".                            "ORDER BY Population", $from, $show); while ($result->fetchInto($row)) {     print "$row[0] ($row[1])<br />\n"; }

The limitQuery() method ensures that the first result is at offset $from (starting at 0), and no more than $show results are returned.

6.4.5.3 `prepare($query)` and `execute($sth, $data = array())`

The last way of running queries is to use the prepare() and execute() methods.

The prepare() method will parse the query and extract input parameter placeholders. If the back-end database supports either input parameter binding or the prepare/execute paradigm, the appropriate native calls are done to prepare the query for execution.

Next, the execute() takes a prepared query along with input parameters, sends the parameters to the database, executes the query, and returns either a Boolean or a DB_result object, just like the other querying methods.

You may call execute() many times for each prepared query. By using prepare/execute (for example) in a loop with many INSERT queries, you may save yourself from a lot of query parsing overhead, because the database has already parsed the query and just needs to execute it with new data.

You can use prepare() and execute() regardless of whether the back-end database supports this feature. DB emulates as necessary by building and executing a new query for each execute() call.

Here is an example that updates the world database numbers with official numbers for Norway as of January 1, 2004:

 <?php require_once 'DB.php'; $changes = array(     array(154351, "Trondheim", "NOR"),     array(521886, "Oslo", "NOR"),     array(112405, "Stavanger", "NOR"),     array(237430, "Bergen", "NOR"),     array(103313, "BÊrum", "NOR"), ); PEAR::setErrorHandling(PEAR_ERROR_DIE, "%s<br />\n"); $dbh = DB::connect("mysql://test@localhost/world"); $sth = $dbh->prepare("UPDATE City SET Population = ? " .                      "WHERE Name = ? AND CountryCode = ?"); foreach ($changes as $data) {     $dbh->execute($sth, $data);     printf("%s: %d row(s) changed<br />\n", $data[1],            $dbh->affectedRows()); }

Here, the query is prepared once, and $sth contains a reference (integer or resource, depending on the driver) to the prepared query. Then the prepared query is executed once for each UPDATE statement.

This example also demonstrates the affectedRows() call, which returns the number of rows with different content after the execute() call.

6.4.5.4 `simpleQuery($query)`

This method is meant for data-manipulation queries that do not return any results beyond success or failure. Its only purpose is that is has slightly less overhead. It returns a Boolean that indicates success or a PEAR error on failure. Here's an example:

 $dbh->simpleQuery("CREATE TABLE foobar (foo INT, bar INT)");

Nothing stops you from running SELECTs and other queries returning data with simpleQuery(), but the return value will be a database extension-specific resource handle. Do not use simpleQuery() for SELECTs.

6.4.6. Fetching Results

The DB_result class has two methods for fetching results and three ways of representing a row of data.

6.4.6.1 Fetch Modes

As with most native database extensions, DB offers different ways of representing a row of data:

DB_FETCHMODE_ORDERED, returning a numerically indexed array, like this:

 array( 0 => first column,        1 => second column,        2 => third column, ... )

DB_FETCHMODE_ASSOC, returning an associative array with column names as keys:

 array( "ID"          => first column,        "Name"        => second column,        "CountryCode" => third column, ... )

DB_FETCHMODE_OBJECT, returning an object with public member variables named after column names.
The default fetch mode is DB_FETCHMODE_ORDERED.

6.4.6.2 Configuring Fetch Modes

You may change the default fetch mode by calling the setFetchMode() method in the connection object, like this:

 $dbh->setFetchMode(DB_FETCHMODE_ASSOC);

This fetch mode then applies to any queries executed by this connection object.

You may also override the default fetch mode per query with an extra parameter to the fetch methods, like this:

 $row = $result->fetchRow(DB_FETCHMODE_OBJECT); // or like this: $result->fetchInto($row, DB_FETCHMODE_ASSOC);

6.4.6.3 `fetchRow($fetchmode = DB_FETCHMODE_ORDERED, $row = 0)`

This method returns an array with row data.

fetchRow() returns the array or object with row data on success, NULL when reaching the end of the result set, or a DB error object.

6.4.6.4 `fetchInto(&$arrr, $fetchmode = DB_FETCHMODE_ORDERED, $row = 0)`

fetchInto() returns DB_OK and stores the row data in $arr when a row was successfully retrieved, returns NULL when reaching the end of the result set, or returns a DB error object. As it happens, DB_OK evaluates to true and NULL evaluates to false. Provided you have an error handler set up, you can then write a loop, like this:

 while ($result->fetchInto($row)) {     // ... do something }

In general, it is always better to use fetchInto(). It makes looping over results easier and slightly faster because fetchRow() is really just a wrapper around fetchInto().

6.4.6.5 Using Your Own Result Class

By default, the object fetch mode (DB_FETCHMODE_OB JECT) returns a stdClass object.

If you configure the fetch mode using the DB::setFetchMode() method rather than specifying the fetch mode in the fetch call, you can add an extra parameter to specify the class to use for the returned object.

The only interface requirement is that the constructor must accept a single array parameter. The array passed to the constructor will have the row data indexed by column name.

You can configure your own class only when controlling the fetch mode with DB::setFetchMode(). Here is an example that uses a class implementing a getter method to access row data:

 <?php require_once 'DB.php'; class MyResultClass {     public $row_data;     function __construct($data) {         $this->row_data = $data;     }     function __get($variable) {         return $this->row_data[$variable];     } } PEAR::setErrorHandling(PEAR_ERROR_DIE, "%s<br />\n"); $dbh = DB::connect("mysql://test@localhost/world"); $dbh->setFetchMode(DB_FETCHMODE_OBJECT, "MyResultClass"); $code = 'NOR'; $result = $dbh->query("SELECT Name FROM City WHERE CountryCode = ?", $code); while ($row = $result->fetchRow()) {     print $row->Name . "<br />\n"; }

6.4.7. Sequences

Database sequences are tricky portabilitywise because they are part of the SQL grammar in some databases, such as Oracle, or implemented as INSERT side effects, such as MySQL's AUTO_INCREMENT feature. The different ways of handling sequences cannot be mixed easily. To provide a single API, DB offers a third way to deal with sequences, which is different from both of these, but at least works for any database supported by DB:

 <?php require_once 'DB.php'; PEAR::setErrorHandling(PEAR_ERROR_DIE, "%s<br />\n"); $dbh = DB::connect("mysql://test@localhost/world"); $dbh->query("CREATE TABLE foo (myid INTEGER)"); $next = $dbh->nextId("foo"); $dbh->query("INSERT INTO foo VALUES(?)", $next); $next = $dbh->nextId("foo"); $dbh->query("INSERT INTO foo VALUES(?)", $next); $next = $dbh->nextId("foo"); $dbh->query("INSERT INTO foo VALUES(?)", $next); $result = $dbh->query("SELECT * FROM foo"); while ($result->fetchInto($row)) {     print "$row[0]<br />\n"; } $dbh->query("DROP TABLE foo"); #$dbh->dropSequence("foo");

The paradigm is not to use auto-increments, last-insert-id calls, or even "sequencename.nextid" as part of the query. Instead, you must call a driver function to generate a new sequence number for the specific sequence that you then use in your query. The sequence number generation is still atomic.

The only disadvantage with this approach is that you depend on PHP code (DB) to make the right sequences for you. This means that if you need to obtain sequence numbers from non-PHP code, this code must mimic PHP's behavior.

This example displays three lines with "1", "2", and "3". Running this script repeatedly will not restart the output at 1, but continue with "4" and so on. (If you uncomment the last line with the dropSequence() line call, the sequence will be reset and the output will start with "1".)

The methods for dealing with sequences are the following:

`nextId($seqname, $create = true)`

nextId() returns the next sequence number for $seqname. If the sequence does not exist, it will be created if $create is true (the default value).

`createSequence($seqname)`

Creates a sequence or a sequence table for databases that do not support real sequences. The table name is the result of sprintf($dbh->getOption("seqname_format"), $seqname).

`dropSequence($seqname)`

Removes the sequence or sequence table. Subsequent calls to nextId() for the same $seqname will re-create and reset the sequence.

6.4.8. Portability Features

Portability in PEAR DB is a balance between performance and portability. Different users have different needs, so from DB 1.6, you have the option of enabling or disabling specific portability features. Older versions of DB had a catch-all "optimize for speed" or "optimize for portability" setting that is deprecated and not covered here.

Portability features are controlled with the portability configuration option (see "Configuration Options" earlier in this chapter). To combine more than one feature, use a bitwise OR, such as this:

 $dbh->setOption("portability",                 DB_PORTABILITY_RTRIM |                 DB_PORTABILITY_LOWERCASE);

6.4.8.1 Count Deleted Rows

Option: DB_PORTABILITY_DELETE_COUNT

Some DBMSs, such as MySQL and SQLite, store tables in a single file, and deleting all the rows in the table is simply a matter of truncating the file. This is fast, but you will not know how many rows were deleted. This option fixes that, but makes such deletes slower. In MySQL 4, this has been fixed so you do not need this option if you use MySQL 4.0 or newer.

6.4.8.2 Count Number of Rows

Option: DB_PORTABILITY_NUMROWS

When working with Oracle, you will not know how many rows a SELECT returns without either doing a COUNT query or fetching all the rows. This option ensures that the $result->numRows() method always returns the number of rows in the result set. This is not needed for other drivers than Oracle (oci8).

6.4.8.3 Lowercasing

Option: DB_PORTABILITY_LOWERCASE

Field name case (upper- or lowercasing letters) varies between DBMSs. Some leave the case exactly the way it was in the CREATE TABLE statement, some uppercase everything, and some are case-insensitive and others not. This option always lowercases column names when fetching results.

6.4.8.4 Trimming Data

Option: DB_PORTABILITY_RTRIM

Some DBMSs keep whitespace padding from CHAR fields, while others strip it off. This option makes sure there is no trailing whitespace in the result data.

6.4.8.5 Empty String Handling

Option: DB_PORTABILITY_NULL_TO_EMPTY

Oracle does not distinguish between NULL and '' (the empty string) when inserting text fields. If you fetch a row into which you just inserted an empty string, that field will end up as NULL. This option helps making this consistent by always converting NULL results to empty strings.

6.4.8.6 Really Portable Errors!

Option: DB_PORTABILITY_ERRORS

This option should not have been necessary, but some error codes have been incorrectly mapped in older versions and changing the mapping would break compatibility. This option breaks backward compatibility, but fixes the error mappings so they are consistent across all drivers. If you truly want portable errors (why wouldn't you?), use this option.

To enable all the portability features, use DB_PORTABILITY_ALL.

6.4.9. Abstracted Errors

Knowing how to deal with or recover from an error is an important part of any application. When dealing with different DBMS servers, you will discover that report different errors for the same issue, even if you are using ODBC.

To compensate for this and make it possible to write portable PHP scripts that can handle errors gracefully, DB uses its own set of error codes to represent errors in an abstracted yet simple way.

6.4.9.1 DB Error Codes

Each database driver converts the error codes or error messages from the DBMS to a DB error code. These codes are represented as PHP constants. The following list contains the supported error codes and examples of situations that causes them:

DB_ERROR_ACCESS_VIOLATION. Missing privileges for a table, no read access to file referenced by opaque parameters, or bad username or password.
DB_ERROR_ALREADY_EXISTS. Table, sequence, procedure, view, trigger, or some other condition already exists.
DB_ERROR_CANNOT_CREATE. Cannot create table or file; the cause of problem is outside the DBMS.
DB_ERROR_CANNOT_DROP. Cannot drop table or delete file; the cause of problem is outside the DBMS.
DB_ERROR_CONNECT_FAILED. Could not establish database connection.
DB_ERROR_CONSTRAINT. Foreign key does not exist, row contains foreign key referenced by another table, and field constraints violated.
DB_ERROR_CONSTRAINT_NOT_NULL. Field may not be NULL.
DB_ERROR_DIVZERO. Division by zero error.
DB_ERROR_INVALID. Catch-all "invalid input" error.
DB_ERROR_INVALID_DATE. Bad date format or nonsensical date.
DB_ERROR_INVALID_NUMBER. trying to use a non-number in a number field.
DB_ERROR_MISMATCH. Number of parameters do not match up (also prepare/execute).
DB_ERROR_NODBSELECTED. Database connection has no database selected.
DB_ERROR_NOSUCHDB. TRying to access a non-existing database.
DB_ERROR_NOSUCHFIELD. trying to query a non-existing column.
DB_ERROR_NOSUCHTABLE. trying to query a non-existing table.
DB_ERROR_NOT_CAPABLE. Database back-end cannot do that.
DB_ERROR_NOT_FOUND. trying to drop a non-existing index.
DB_ERROR_NOT_LOCKED. trying to unlock something that is not locked.
DB_ERROR_SYNTAX. SQL syntax error.
DB_ERROR_TRUNCATED. Returned data was truncated.
DB_ERROR_UNSUPPORTED. Performing an operation not supported by DB or the DBMS client.
DB_ERROR_VALUE_COUNT_ON_ROW. See DB_ERROR_MISMATCH.

6.4.9.2 Graceful Error Handling

DB uses the PEAR errors to report errors. Here is an example that alerts the user if he tries to add a unique combination of keys twice:

 <?php require_once 'DB.php'; $dbh = DB::connect("mysql://test@localhost/world"); $dbh->setOption('portability', DB_PORTABILITY_ERRORS); $dbh->query("CREATE TABLE mypets (name CHAR(15), species CHAR(15))"); $dbh->query("CREATE UNIQUE INDEX mypets_idx ON mypets (name, species)"); $data = array('Bill', 'Mule'); for ($i = 0; $i < 2; $i++) {     $result = $dbh->query("INSERT INTO mypets VALUES(?, ?)", $data);     if (DB::isError($result) && $result->getCode() == DB_ERROR_CONSTRAINT) {         print "Already have a $data[1] called $data[0]!<br />\n";     } } $dbh->query("DROP TABLE mypets");

See Chapter 7, "Error Handling," for details on how to catch PEAR errors.

6.4.10. Convenience Methods

Although PEAR DB is mostly a common API, it also contains some convenience features for retrieving all the data from a query easily. All these methods support prepare/execute style queries, and all of them return PEAR errors on failure.

6.4.10.1 `$dbh->getOne($query, $params = array())`

The getOne() method returns the first column from the first row of data. Use the $params parameter if $query contains placeholders (this applies to the rest of the convenience functions, too). Here's an example:

 $name = $dbh->getOne('SELECT name FROM users WHERE id = ?',                      array($_GET['userid']));

6.4.10.2 `$dbh->getRow($query, $params = array(), $fetchmode = DB_FETCHMODE_DEFAULT)`

The getrow() method returns an array with the first row of data. It will use the default fetch mode, defaulting to ordered. Ordered data will start at index 0. Here's an example:

 $data = $dbh->getRow('SELECT * FROM users WHERE id = ?',                      array($_GET['userid']));

6.4.10.3 `$dbh->getCol($query, $col = 0, $params = array())`

The getCol() method returns an array with the $col'th element of each row. $col defaults to 0. Here's an example:

 $userids = $dbh->getCol('SELECT id FROM users');

6.4.10.4 `$dbh->getAssoc($query, $force_array = false, $params = array(), $fetchmode = DB_FETCHMODE_DEFAULT, $group = false)`

This method returns an associative array with the contents of the first column as key and the remaining column as value, like this (one line per row):

 array(col1row1 => col2row1,       col1row2 => col2row2,       ...)

If the query returns more than two columns, the value will be an array of these values, indexed according to $fetchmode, like this:

 array(col1row1 => array(col2row1, col3row1...),       col1row2 => array(col2row2, col3row2...),       ...)

or with DB_FETCHMODE_ASSOC:

 array(field1 => array(name1 => field2, name3 => field3...),       field2 => array(name2 => field2, name3 => field3...),       ...)

The $force_array parameter makes the value an array even if the query returns only two columns.

If the first column contains the same key more than once, a later occurrence will overwrite the first.

Finally, you set the $group parameter to trUE, and getAssoc() will keep all the rows with the same key in another level of arrays:

 $data = $dbh->getAssoc("SELECT firstname, lastname FROM ppl",                     false, null, DB_FETCHMODE_ORDERED, true);

This example would return something like this:

 array("Bob" =>  array("Jones", "the Builder", "Hope"),       "John" => array("Doe", "Kerry", "Lennon"),       ...)

6.4.10.5 `$dbh->getAll($query, $params = array(), $fetchmode = DB_FETCHMODE_DEFAULT)`

This method returns all the data from all the rows as an array of arrays. The inner arrays are indexed according to $fetchmode:

 array(array(name1 => col1row1, name2 => col2row2...),       array(name1 => col1row2, name2 => col2row2...),       ...)

You can flip around the dimensions in this array by OR'ing DB_FETCHMODE_FLIPPED into fetch mode. With a fetch mode of DB_FETCHMODE_FLIPPED | DB_FETCHMODE_ASSOC, the result will look like this:

 array(name1 => array(col1row1, col1row2, ...),       name2 => array(col2row1, col2row2, ...),       ...)