Abstract PHP's database code with PEAR::DB

With the PEAR::DB classes you can avoid: re-writing frequently used routines to suit different vendors' databases, or the time-consuming process of building your own wrapper functions.

Why abstract your database code?

Because often there's no reason why that useful section of code can't be reused on other database servers. Whether it's a ten line algorithm you spent half a day on, or a ten thousand line monster you just know you will need to migrate someday from MySQL to Oracle or PostgreSQL. You might also develop on one database server while targeting several production servers from different vendors. It makes good sense to avoid replacing all the database specific functions and routines by using an abstraction layer between your code and database vendor specific functions.

Does that sound like a good idea? I thought so. One way to achieve this is to build wrapper scripts to hide the real access functions from your programs. Building a library of these wrappers to support all the functions of supported databases in the PHP API is a really big nasty project. Fortunately most of it has already been done, and is available via the PEAR repository, included in recent PHP releases by default.

By using PEAR::DB and a simple configuration script, you can avoid much of the database function related hassle associated with migrating scripts. You also get access to a comprehensive, standardized error handling system, and several functions not yet available in some of PHP's individual database APIs (like prepare() and execute()).

So, what do I need?

• First, you need to be familiar with at least one of PHP's sets of database access functions, it doesn't matter which group (MySQL, Oracle, PostgreSQL, …).
• Second, you will need access to a PHP build which includes PEAR. If you can execute <?php require_once 'DB.php'; ?> without an error then you are good to go (Windows users may need to add the PEAR path to their php.ini file, the PEAR FAQ explains this).
  
  If, after the security announcement (2002 / 2 / 27) your host still hasn't, and doesn't intend to, upgrade, then IMO you need to find a new host. Version 4.1.2 of PHP builds PEAR by default.
• Finally, you will need a database to play with. Doh!

Caveat: the SQL statements will need to be generic enough to work on all of your target databases, otherwise they should be built separately from each other (via a switch block, or the prepare() strategy described at the end of this introduction). PEAR::DB only helps abstract access to the database, it cannot abstract SQL statements. For accurate information about what each supported database is capable of, implemented in PEAR, and tested for, read the document at your pear_base_dir/DB/STATUS.

The config file

PEAR::DB needs to know about the usual suspects for a database connection (the server hostname, username, password, and database name), just like mysql_connect() or pg_connect() do. By using an external config file instead of listing this information inline you:

• save time and increase portability by only needing to change a parameter in one place;
• gain some security because you can move the file with password information outside the web accessible directory on the server; and
• avoid having to type that stuff out again and again, or waste time hunting down the inevitable typo.

// /home/foo/incs/db_config.php
// put this file somewhere safe, preferably outside the document root

$DB_host    = "bigBoxInTheCorner";  // the hostname of the database server
$DB_user    = "thatIsMe";           // the username to connect with
$DB_pass    = "notTellingYou";      // the user's password
$DB_dbName  = "myDatabase";         // the name of the database to connect to
$DB_dbType  = "seeBelow";           // the type of database server.

At the time of writing, the supported database types for the $DB_dbType variable are:

The PEAR::DB object

Once you've saved the config file somewhere creating a connection with a PEAR::DB object is really easy:

require_once 'DB.php';          // You know you're gonna need this
                                // Imports all the PEAR::DB classes, static
                                // methods, etc...

                                // Get the connection variables:
require_once '/home/foo/incs/db_config.php';

$dsn = $DB_dbType . "://"       // Build a DSN string (Data Source Name)
        . $DB_user . ":"        // Required by DB::connect()
        . $DB_pass . "@" 
        . $DB_host . "/" 
        . $DB_dbName;

$db = DB::connect($dsn, TRUE);  // Creates a database connection object in $db
                                // or, a database error object if it went wrong.
                                // The boolean specifies this is a persistent
                                // connection like mysql_pconnect(), it
                                // defaults to FALSE.

if (DB::isError($db)) {         // Check whether the object is a connection or
                                // an error.
    die($db->getMessage());     // Print out a message and exit if it's 
                                // an error object.
}

// Do all sorts of stuff with your database connection here.

$db->disconnect();              // Close the connection.

So, creating database connections with PEAR::DB objects doesn't take that much more effort than a regular PHP database whatever_connect() function. Building, executing, and processing SQL queries is also a piece of cake:

// Get a connection as above

$sql = "SELECT first_name
        , last_name
        , pet_name
        FROM person
        WHERE person_id > 0";   // Build your SQL query
    
$res = $db->query($sql);        // $db->query processes the query in the
                                // string $sql, using the database
                                // connection we built before in $db.
                                //
                                // $res will be a result resource object
                                // or an error object

if (DB::isError($res)) {        // Check the result object in case there
    die($res->getMessage());    // was an error, and handle it here.
}

while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
    // Fetch a row from the result resource object $res.
    //
    // DB_FETCHMODE_OBJECT returns a row with column names as keys.
    // Other possibilities are listed here:
    // http://pear.php.net/manual/en/core.db.tut_fetch.php

    if (DB::isError($row)) {
        die($row->getMessage());  // fetchRow can return an error object
                                  // like most PEAR::DB methods.
    }
    
    print ("<p>Hey, it's:<br />" 
            . $row->first_name . " " . $row->last_name 
            . " ... and their freaky pet: " . $row->pet_name 
            . "</p>\n");
}

$res->free();                   // Release the result resource object.
                                // Similar to: mysql_free_result($result)

// Disconnect as above

Other handy PEAR::DB features

Just get*() the data already

The fun doesn't stop there folks. There are also some neat time-saving methods in PEAR::DB. The get*() group (getOne(), getRow(), getCol(), getAssoc(), and getAll()) takes an SQL query string as an argument, queries the database, fetches the results, then frees the result resource, all in one go. Explanations and examples are available at the bottom of the fetch() tutorial.

prepare() to execute()!

Some database servers allow you to save an SQL query with place holders, then use it later by filling in the blanks. For example: say you want to insert a variable number of rows into a table, but would like to avoid putting the INSERT query inside a loop. PEAR::DB emulates this functionality for those databases that don't support it natively. (As at the release of PHP v. 4.1.2, a fully tested, native implementation of this feature is only available for Oracle, and an untested implementation for InterBase; it is emulated on all the other supported databases. See the pear_base_dir/DB/STATUS document for full details of the implemented / emulated, tested / untested, features of your target databases.)

Here's an example of how the executeMultiple()function could be used on a table like the one in our previous SELECT example:

// Get a connection as above

    // First we translate a normal INSERT query into a generic 
    // one we can re-use. So, if this is a valid INSERT query:
    //
    // INSERT INTO person (first_name, last_name, pet_name) 
    //        VALUES ("jonnie", "walker", "tipples");
    // 
    // Replace the quoted values with ? and your good to go:

$sth = $db->prepare("INSERT INTO person"
            . " (first_name, last_name, pet_name)"
            . " VALUES (?, ?, ?)");    

    // You can also use ! instead of ? to insert data as is (not quoted),
    // or & to insert the data from a filename.


    // Next we build a two dimensional array that contains the
    // values we want to insert. 
    // executeMultiple() will process all of these in a single step.
    // execute() will process only one SQL command at a time.

$data = array (
            array ("johnnie", "walker", "tipples"),
            array ("mandy", "morrison", "mumpy"),
            array ("jumpy", "jones", "jiggles"),
            array ("willy", "the menace", "groutch"));


    // executeMultiple() takes the above prepared statement handler ($sth)
    // and the array of data ($data), and processes it. 

$res = $db->executeMultiple($sth, $data);

    // If everything went ok executeMultiple returns a Result object 
    // (in the case of SELECT statements).
    // If anything went wrong then a DB Error object will return, just
    // like nearly everywhere else in the PEAR project.


if (DB::isError($res)) {         // Check the result object in case there
    die($res->getMessage());  // was an error, and handle it here.
}

// Disconnect as above

More details can be found in PEAR's execute() tutorial. They also suggest a nifty solution for handling different database's SQL syntax with an array of prepared statements where the key is the database type:

// Get a connection as above

    // Create an array where the database type string is the key
    // to the appropriate SQL statement.
    // MySQL supports either syntax, but you get the general idea.

$sql['oci8']['INSERT_PERSON'] = "INSERT INTO person"
    . " (first_name, last_name, pet_name)"
    . " VALUES ( ?, ?, ? )" ;
$sql['mysql']['INSERT_PERSON'] = "INSERT INTO person"
    . " SET first_name=?, last_name=?, pet_name=?" ;
    
    // And so on with all the types of syntax you need to support.

    // Next, prepare the query, using the $DB_dbType variable
    // to access the correct SQL syntax:

$sth = $db->prepare($sql[$DB_dbType]['INSERT_PERSON']);    

    // The rest as in the previous executeMultiple() example.

Summary

PEAR::DB supplies us with a standard, fairly easy-to-use, API for database connections. By using the methods it supplies we can more easily write portable code than is possible using PHP's vendor-specific database functions. And, the icing on the cake, we get new functionality previously not available for all databases.

References

The core documentation for PEAR, the PEAR::DB docs, and the downloadable docs will be very handy. The PEAR FAQ is primarily targeted at PEAR developers.

PHP lists, including the PEAR Project list archives can be found at Zend's Mailing List page.

Tutorials, reviews, and information about PEAR classes are listed on this PHP Weblog.

PHP Builder have a more detailed tutorial by Allan Kent.