[ Team LiB ] Previous Section Next Section

Using the PEAR DB Package

The PHP Extension and Application Repository (PEAR) is a collection of powerful and quality-controlled libraries that can be used to extend PHP's functionality. We cover PEAR in much more detail in Hour 23, "PEAR: Reusable Components to Extend the Power of PHP." The DB package, however, is so enormously significant as a database tool, it would be a serious omission to not include it here.

We have looked at two mechanisms for working with SQL. You might have notice how similar the functions we covered are. Yet despite these similarities, shifting a project from one set of database functions to another is time-consuming. You would have to go through your source code and change function names before the transfer would be complete.

Wouldn't it be better to have a library that hides these implementation details behind a common set of functions or methods? When you choose to change a database, you can substitute a different implementation behind the common database interface you are using without disturbing your code. Your code would continue to work with the functions it has always called, and the functions would work with the new database functions on your behalf.

In previous editions of this book, we cooked up our own code to handle database abstraction to a certain extent. Now, however, a standard library exists that is designed precisely for this purpose. The DB package supports a number of databases, including MySQL, Dbase, FrontBase, Interbase, Mini SQL, PostgeSQL, Microsoft SQL Server, ODBC, Informix, SyBase, and, of course, SQLite.

Let's begin to work with the DB package.

Installing the PEAR::DB Package

PEAR::DB should be bundled with your distribution of PHP 5. If you do not have it, though, you can install the PEAR::DB package from the command line with this simple command:


pear install DB

You also might want to run another PEAR command, like so:


pear upgrade DB

This updates your DB package and ensures that you have the latest version, as well as support for even more database applications.

Working with the PEAR::DB Package

In this section, we reproduce the code we wrote for SQLite using a MySQL database. The code has only one line of code specific to MySQL.

The first thing we need to do to work with the DB package is acquire a DB object. This is achieved by calling the static connect() method on the DB class. The connect() method requires what is known as a data source name (DSN). A DSN string combines all the information that is needed to identify and establish a connection with a database server.

When assembled, a DSN looks a bit like a Web address. Table 13.1 lists most of the elements of a DSN.

Table 13.1. Some of the Parts of a Data Source Name

Part

Description

data_app

One of mysql, pgsql, ibase, msql, mssql, oci8, odbc, sybase, ifx, or fbsql

syntax

SQL syntax (for example, sql92)

protocol

Connection protocols, such as TCP and Unix

user

The username

pass

The password

host:port

Host and port (the port is optional); for example, localhost:3306

database

The database to work with

Table 13.1 shows many more elements than you would probably use in a DSN. They would be put together in the order in which they are listed:


data_app(syntax)://user:pass@protocol+host:port/database

In reality, you will probably use only a few of these parts to make up your DSN. Let's construct a DSN for working with a MySQL database and use it to acquire a database object:


require_once("DB.php");
$user = "p24_user";
$pass = "cwaffie";
$host = "localhost";
$database = "p24";

$dsn = "mysql://$user:$pass@$host/$database";
$db = DB::connect($dsn);

We assemble our DSN with values for user, password, host, and database. We then pass the assembled string to the DB::connect() method, which is a factory method. That is, it uses the information you pass it to decide which object you need. The object it returns is always a child of DB_common. In our example, we acquire a DB_mysql object, which provides the MySQL-specific functionality we need.

We could, of course, have configured the DB package to work with SQLite, like this:


$dsn = "sqlite://./mydb.db";
$db = DB::connect($dsn);

After we have connected to the database, our examples should run identically for either database.

If, for some reason, our call to DB::connect() fails, it returns a DB_error() object instead of the object we want. We can test for an error with the DB::isError() method:


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

DB::isError() tests the type of a DB package return value. If it is a DB_error object, the method returns true. DB_error provides the getMessage() method, which enables us to print an informative error message to the browser.

We have set up the p24 database so that it contains a people table:


CREATE TABLE people
( id INT PRIMARY KEY,
 firstname VARCHAR(255),
 secondname VARCHAR(255) );

Let's clear the table of data, so that we are working with a clean sheet:


$delete_query = $db->query( "DELETE FROM people" );
if ( DB::isError( $delete_query ) ) {
  die ($delete_query->getMessage());
}

We introduce the query() method, which accepts a SQL query and returns different values according to the type of query it is passed. If the query passed generates a resultset, we expect a DB_result object from query(). If, as in our fragment, we pass a query that does not generate data, query() will return a positive integer. As before, we test for a DB_error object using DB::isError(). You should write code that anticipates all possible error conditions and implement strategies for recovery or failure. This is known as coding defensively. To keep our code clear of repetition, we will drop the error tests in future fragments, but you should you test for errors in production code.

So, the query() method enables us to execute SQL statements. Where possible, you should try to keep your SQL as standard as you can. If you use application-specific features, you risk undermining the portability the DB package provides.

Let's add some data to the people database:


$insert = array(
      array( "firstname" => "joan",
          "secondname" =>"peel" ),
      array( "firstname" => "mary",
          "secondname" =>"biscuit" )
);

foreach ( $insert as $row ) {
  $id = $db->nextId('people_sequence');
  $row['id'] = $id;
  print "Inserting {$row['firstname']} {$row['secondname']}: $id<br />\n";
  $db->autoExecute( 'people', $row, DB_AUTOQUERY_INSERT );
}

We have introduced a few new features of the DB package in the previous fragment. First, we build up some data that we will use to populate our table. We use an array of associative arrays, with each subarray representing a row and containing field values indexed by field names. We loop through our data array, calling a new method—nextID(). nextID() is an example of a sequence, which is used to acquire unique IDs for primary keys. nextID() requires a sequence name. This can be anything you want, but you should always use the same name for a table if you want to ensure that your ID values are unique. Behind the scenes, our DB_common object has created a sequence table in the p24 database to keep track of the ID values it has generated. We can therefore be sure that we will always get a unique ID as long as we call nextID() with the same name and in relation to the same database.

Why have we used this relatively complicated way of generating a unique ID for our row, when MySQL and SQLite automatically add an ID for us? The reason is portability. By using the interface provided by the DB package to generate ID values, we ensure that we can change our code to work with another database with the minimum of amendment. In fact, we should have to change only the DSN string.

So, we have an ID value that we tack onto the $row array generated for each iteration of the foreach loop. This means that $row is an associative array containing the names and values for a complete row of the people table. We could use this to generate an INSERT SQL statement. The DB package, however, provides a useful, convenient method. Let's look at it again:


$db->autoExecute( 'people', $row, DB_AUTOQUERY_INSERT );

The autoExecute() method accepts a table name, an associative array containing field names and corresponding values, and a mode value. The mode can be one of DB_AUTOQUERY_INSERT and DB_AUTOQUERY_UPDATE. If you want to update a table, you can also pass a WHERE string as a fourth argument (such as id=5). The autoExecute() method constructs a SQL string on your behalf and passes it to the database.

Like query(), the autoExecute() method returns a DB_result object if all goes well or a DB_Error object if a problem exists.

So, we have populated the people table with some sample data. Let's update the table before moving on to listing information:


$update_query = "UPDATE people SET firstname='John' WHERE secondname='peel'";
$update_result = $db->query( $update_query );

The previous fragment should be familiar to you by now. We simply call the query() method with an UPDATE SQL statement.

Finally, let's work with a SELECT statement:


$query = "SELECT * FROM people";
$query_result = $db->query( $query );

while ( $row = $query_result->fetchRow( DB_FETCHMODE_ASSOC ) ) {
  print "row: {$row['id']} {$row['firstname']} {$row['secondname']}";
  print "<br />\n";
}

Again, we use the query() method. We are expecting a DB_result object, which we can use to extract our resultset. Don't forget that you should use DB::isError() to test production code.

The DB_result class provides the fetchRow() method that acquires row data from a resultset for us and advances the pointer to the next row. It returns null when the data has all been read. You can pass an integer to fetchRow() to influence the structure of the data it returns. We used DB_FETCHMODE_ASSOC because we want an associative array. Also available is DB_FETCHMODE_ORDERED, which is the default value and causes the row to be returned as a numerically indexed array. You can also pass fetchRow() the DB_FETCHMODE_OBJECT constant to cause an object to be returned containing the row's field names as properties, populated with their respective field values.

Finally, we can free the results of our query from memory and disconnect from the database:


$query_result->free();
$db->disconnect();

Calling the DB_result::free() method causes the result resource to be released by the DB_result object. The DB_Common::disconnect() method relinquishes our connection to the database.

Database code is frequently a barrier to portability, and switching between database applications can be a real headache. Used carefully, the DB package helps you avoid the issue of migrating from one database solution to another.

    [ Team LiB ] Previous Section Next Section