So you’ve written a great application with PHP and it is up and running smooth. Then, your boss calls and tells you that your company won’t be using MySQL anymore. You’re moving to Oracle. Your application is dead in the water. You should have used database abstraction.

It’s a grim tale that happens all too often. Don’t think it can’t happen to you. It will. When you least expect it. But don’t worry, there are ways to plan for situations like this. Wouldn’t you love to just smile at your boss and tell him it won’t be a problem? Well, today you will learn just how to do that.

Database Abstraction is a method of coding that allows you to concentrate on coding rather than the syntax for a particular database API. You use generic methods to access the database, any database. Obviously, you need additional code or libraries to do database abstraction. That is where PEAR comes in. PEAR includes a database abstraction class that you can use in your scripts. There are other abstraction classes out there, but for the purpose of this tutorial we will focus on the one provided with PEAR.

What is PEAR?

A PEAR is “The fleshy pome, or fruit, of a rosaceous tree (Pyrus communis), cultivated in many varieties in temperate climates.” OK, so that isn’t the PEAR we are talking about here. The description from the official PEAR site is “PEAR is a framework and distribution system for reusable PHP components.”

Essentially, PEAR gives you the developer a bunch of reusable components that follow a particular coding standard and API. This allows you to create applications that are generic enough to work on a variety of platforms with little or no modifications. PEAR provides functionality for databases, authentication, filesystem operations, networking, XML, and tons of other things. Today we will concentrate on PEAR’s database abstraction layer.

I am guessing that you are already somewhat familiar with PHP and its database capabilities, so I won’t go into a primer course here. Let’s just begin by looking at a few of the functions available to you in PEAR’s database abstraction class. This is by no means a complete list of functions available from PEAR. For more information about PEAR, see the website at http://pear.php.net/.

DB::connect

mixed connect (string $dsn [, array $options])

That is our connect function. It is what we will use to connect to the database. Its actual use will look something like:

$db = DB::connect("mysql://user:pass@host/db_name", FALSE);

It will return a connection object or an error object which will be stored in $db. You may be wondering what that wierd :: is. Well, DB is the class for data abstraction and connect is the function. Normally you would see it look like DB->connect(). The reason for the :: is so that we can use the connect function without creating an instance of the class DB. Maybe a little bit different than you are used to, but it has its uses. The :: can also be used to access a function of a base class when you are dealing with inherited classes.

You can see that we passed the connect function what sort of looks like a URL. This is a dsn, or data source name. Here you pass it the database type and other connection information such as username and password. For a complete list of database types, see this page.

The second, optional parameter is whether to use persistent connections or not. Not all databases support persistent connections and the default for this parameter is false.

Here is how it is commonly used:

<?php
require_once 'DB.php';
$user = 'foo';
$pass = 'bar';
$host = 'localhost';
$db_name = 'clients_db';
$dsn = "mysql://$user:$pass@$host/$db_name";
$db = DB::connect($dsn, false);
?>

DB::disconnect

boolean disconnect ()

This is, obviously, the function to use when disconnecting from a database. It is always good practice to use the disconnect function. In PHP, the end of a script should automagically do a disconnect, but I wouldn’t rely on it. Use it just to be safe.

To use this function, we will reference the database object that was returned from an inital connect statement:

$db = DB::connect($dsn, false);
$db->disconnect();

DB::isError

boolean isError (DB_Error $value)

The error support built into the database abstraction class in PEAR is second to none. When using one of the many database functions, if there is an error an error object will be returned. You can check if the object returned is an error with DB::isError. We can check if a connection attempt returns an error or if a query returns an error. If the object is an error, the function returns true. Otherwise, it returns false. If we determine that we are dealing with an error object, then there are methods of that error object we can use to display the error.

It is always good practice to check for return errors when making connections or querying a database. Here is an example use of DB::isError for testing if a connection was made:

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

DB::getOne

mixed &getOne (string $query [, array $params])

Now, this is a useful little function. This allows you to grab the first column in the first row of data that a query returns. It is very useful for doing count functions or sum functions. It returns the value of the first column in the first row or an error object.

A typical use would be:

$numrows = $db->getOne('SELECT count(*) FROM mystuff');

One nice thing about DB::getone is that it does the query, gets the results, and frees the results. No need to worry about cleanup with this function.

DB::query()

mixed &query (string $query [, array $params])

This is the general purpose query function. On failure it will return an error object. On success it will return either a DB_OK or a result set object. A DB_OK is a PEAR constant that just tells you the query executed successfully. You will receive a DB_OK for any query that executes properly that doesn’t return a result set, i.e. an INSERT or UPDATE.

Usage for this function:

$sql = "SELECT * FROM mystuff ORDER BY stuff";
$result = $db->query($sql);

DB_Result::fetchRow

mixed fetchRow ([integer $fetchmode [, integer $rownum]])

OK, we have seen everything we need to do except actually get the data. This function will grab a single row from a result set. It will return either a row of data, NULL if there are no more rows, or an error object. The neat thing about this fetchRow function is that you specify the row number you want it to grab. This comes in handy when you are dealing with a DBMS that doesn’t support LIMIT or a similar function.

There are three fetch modes you can specify with this function:

• DB_FETCHMODE_ORDERED: this is the default fetch mode and will return an array with numerical keys.
• DB_FETCHMODE_ASSOC: this mode returns an array with column names as the keys.
• DB_FETCHMODE_OBJECT: this mode will return an object with column names as the properties.

Typical usage for this function:

$row = $result->fetchrow(DB_FETCHMODE_ASSOC);

A simple script

OK, hopefully this tutorial has turned you on to the world of PEAR. I will be introducing other components of PEAR in the near future. I now will leave you with a simple script to do results paging (previous and next links) using the PEAR DB class. This method of paging should work across all databases that PEAR supports.

<?
$limit = 5;
require_once 'DB.php';
$user = 'user';
$pass = 'pass';
$host = 'localhost';
$db_name = 'test';
$dsn = "mysql://$user:$pass@$host/$db_name";
$db = DB::connect($dsn);
if (DB::isError($db)) {
    die ($db->getMessage());
}
if(isset($_GET['start'])): $start = $_GET['start']; else: $start = 0; endif;
$sql = "SELECT * FROM mystuff ORDER BY stuff";
$result = $db->query($sql);
if (DB::isError($result)) {
    die ($result->getMessage());
}
foreach (range($start, $start + $limit - 1) as $rownum) {
    if (!$row = $result->fetchrow(DB_FETCHMODE_ASSOC, $rownum)) {
        break;
    }
    echo $row['stuff'] . "<BR>\n";
}
$result->free();
$numrows = $db->getOne('SELECT count(*) FROM mystuff');
$db->disconnect();
if($start > 0) {
    echo "<a href=\"".$_SERVER['PHP_SELF']."?start=".($start - $limit)."\">Back</a><BR>\n";
}
if (($start + $limit) < $numrows) {
    echo "<a href=\"".$_SERVER['PHP_SELF']."?start=".($start + $limit)."\">Next</a><BR>\n";
}
?>