PEAR::DB

PEAR::DB is an advanced, object-oriented database library that provides full database abstraction - that is, you use the same code all your databases. If you want your code to be as portable as possible, PEAR::DB provides the best mix of speed, power, and portability. However, if your scripts are only ever going to run locally, there is no real compelling reason to use PEAR::DB.

PEAR::DB works by abstracting not only the calls neccessary to work with the databases (such as mysqli_connect(), pgsql_query(), etc), but also clashes in SQL syntax, such as the oft-argued "LIMIT" clause. In PHP 5.1 there's a new extension called PHP Data Objects (PDO) that abstracts only the functions, which gives a half-way house between PEAR::DB and just using normal DB calls.

This script below provides a good demonstration of how PEAR::DB works:

<?php
    include_once('DB.php');

    $conninfo = "mysqli://username:password@localhost/phpdb";
    $db = DB::connect($conninfo);
    
    if (DB::isError($db)) {
        print $db->getMessage();
        exit;
    } else {
        $result = $db->query("SELECT * FROM people;");

        while ($result->fetchInto($row, DB_FETCHMODE_ASSOC)) {
            extract($row);
            print "$Name: $NumVisits\n";
        }
    
        $result->free();
    }
    
    $db->disconnect();
?>

Notice how PEAR::DB uses a URL-like connection string, often called a Data Source Name (DSN), to entirely define its connection. This is the same method as seen in JDBC, so it should be familiar to Java developers already. The string can be broken down like this:

The connection type is for what kind of server to which you intend on connecting. You can choose from the following list:

The rest of the string is quite straightforward, so you should now be able to fashion a DSN for your own system. Continuing on with the code, once the DSN is prepared, it is simply a matter of passing it into a call to DB::connect() as its first parameter - this will return a reference to the object we can use for querying. Remember that PEAR::DB is object-oriented, which means you need to hang on to the return value from DB::connect().

DB::isError() is a special function call that takes one parameter, the value to check, and returns true if that value is one of PEAR::DB's error types. Here $db is passed in, because if DB::connect() failed, it would return an error message that is picked up by DB::isError(). In the off-chance that an error has occurred, it will be stored in the getMessage() function of our database connection.

However, if things go well we can start querying the system using the query() function of our $db object. This takes the SQL query to perform as its only parameter, and returns another DB object that contains the result information. To cycle through the result information, a while loop is used, taking advantage of the fetchInto()PEAR::DB function. FetchInto() will return false if it cannot return any more rows, and takes two parameters: where it should send the data it fetches, and how it should store the data there. Using DB_FETCHMODE_ASSOC means that PEAR::DB will set $row up to be an associative array of one row in the result set, recursively iterating through the rows with each while loop.

At the end of the script, the free() and disconnect() functions called. As per usual, this is not required because PHP will clean up connections when scripts are finished, but it is important to make sure you do not give PHP extra work to do. Also as per usual, another good reason to use these two is to free up resources - there is little point hanging on the end of an SQL connection and doing nothing.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.