Introduction to PHP’s PEAR classes : Comparing normal Database connections with PEAR classes.

PHP Database Abstraction:

Abstraction is a technique which simplifies something complex. It does this by removing non-essential parts of the object, allowing us to concentrate on the important parts.

PEAR’s DB classes are one such database abstraction layer, and in this article we’ll take a look at some traditional database access methods and then compare them with the method employed by PEAR.

Traditional Database Access

Let’s quickly run through how you would connect to a database and run through the records using PHP’s native functions. We’ll start with MySQL since that seems to be one of the more popular databases. Note to Postgresql users – I said _more_ popular, not _most_ popular, so you can hold back on that hate mail for the moment 🙂 [ *cough* -tim ]

Here’s an SQL dump for the database that I am using. It’s MySQL and the database is called phptst. It consists of a single table called demo which has 3 fields.

# MySQL dump 8.11
#
# Host: localhost	Database: phptst
#--------------------------------------------------------
# Server version	3.23.28-gamma

#
# Table structure for table 'demo'
#

CREATE TABLE demo (
  demo_id mediumint(9) NOT NULL auto_increment,
  demo_stamp bigint(20),
  demo_text varchar(50),
  PRIMARY KEY (demo_id)
);

#
# Dumping data for table 'demo'
#

INSERT INTO demo VALUES (1,978872113,'Record number 1');
INSERT INTO demo VALUES (2,978872652,'Record number 2');
INSERT INTO demo VALUES (3,978872652,'Record number 3');
INSERT INTO demo VALUES (4,978872652,'Record number 4');
INSERT INTO demo VALUES (5,978872652,'Record number 5');

Right, on to the code:

<?php include( 'dbinfo.php' );
$conn = mysql_connect( $dbhost, $dbuser, $dbpass );
mysql_select_db($dbname);
$sql = 'SELECT * FROM demo';
$demoResult = mysql_query( $sql );
while ( $demoRow = mysql_fetch_row( $demoResult ) ) {
echo $demoRow[2] . '<br>';
}
mysql_close ($conn);
?>

The first thing that I like to do is move all of my connection information out of the main script and into a small include file. This way when I change the password for the database I don’t have to go and edit every script that connects to that database. My dbinfo.php looks something like this:

<?php
$dbhost = 'localhost';
$dbuser = 'theuser';
$dbpass = 'thepassword';
$dbname = 'phptst';
?>

The line $conn = mysql_connect( $dbhost, $dbuser, $dbpass ); now uses the variables I set in dbinfo.php rather than having the info hard coded. OK. $conn is now a link identifier to my database connection. mysql_select_db($dbname); tells PHP which MySQL database I want to use.

I set up my query with $sql = 'SELECT * FROM demo'; and then run the query on my database with $demoResult = mysql_query( $sql );. $demoResult is now an idetifier to the result set that my query just returned. We then use a while statement to loop through the result set:

while ( $demoRow = mysql_fetch_row( $demoResult ) ) {

each iteration populating the array $demoRow with next row of information from our result set. When we’re done we can close our connection to the database with a call to mysql_close().

Here’s the output from that script:

Record number 1
Record number 2
Record number 3
Record number 4
Record number 5

If that quick and dirty run through mysql was too horrible to bear, there’s an intro article to PHP and MySQL on webmonkey.

Doing it the PEAR way

Out with the old, and in with the new – how the hell do we do that with PEAR ? Here’s our MySQL example above, rewritten using PEAR DB.

<?php include('dbinfo.php');
require_once( 'DB.php' );
$db = DB::connect( "mysql://$dbuser:$dbpass@$dbhost/$dbname" );
$sql = 'SELECT * FROM demo';
$demoResult = $db->query($sql);
while ($demoRow = $demoResult->fetchRow()) {
echo $demoRow[2] . '<br>';
}
$db->disconnect();
?>

OK, compare that to what we saw with MySQL. The process is pretty much the same (connect, query, loop through result, disconnect), but the way that we do each step has changed. Let’s go through it line by line and I’ll explain what’s going on.

Our first line is as it was in our original script. I’m including my dbinfo.php so that I don’t have to hard code my database usernames and passwords. The next line require_once( "DB.php" ); is require’ing the file DB.php. The _once part makes sure that requires of files that contain duplicate variable or function declarations do not break your script.

The file DB.php can be found in the /pear subdirectory of the PHP4 distribution. If the installation process didn’t do it, make sure that the directory that contains the PEAR files is included in your include_path in the php.ini.

Inside DB.php a class of type DB is defined. One of the functions of the DB class is the connect() function, and this is what we are using here:

<?php $db = DB::connect( "mysql://$dbuser:$dbpass@$dbhost/$dbname" ); ?>

The connect() function creates a new database object and connects to the database as specified in the string that you pass it. The string takes the format of:

phptype://username:password@protocol+hostspec/database

phptype is the type of database that you wish to connect to. Valid types are ibase, msql, mssql, mysql, oci8, odbc, pgsql and sybase.

username, password and hostspec are self explanatory. protocol is the protocol to connect on (eg tcp) and database is the database that you want to use. The only thing in that string that is required is the phptype. Everything else is optional, but in most cases you are going to want to specify a username, password and database.

If I wasn’t using the dbinfo.php include file the string I passed the connect function would look something like:

"mysql://theuser:thepassword@localhost/phptst"

Assuming that the connect() function succeeded, $db is now an object that not only contains methods for accessing your database but also contains all sorts of information about the conection you have just created.

What is important for this example though, is that the object has a function called query() that will execute an SQL statement on the database. It returns a result object with it’s own properties and methods that we can use to get at the data.

$demoResult = $db->query($sql);

says “in the object $db is a function called query(). Run it with the argument $sql and stick the results you get back from it in $demoResult“.

One of the functions in $demoResult is fetchRow(). fetchRow() will return a row of data as an array, much like mysql_fetch_row() will.

while ($demoRow = $demoResult->fetchRow()) {

fetchRow() is returning an array indexed numerically. What if you wanted to get an associative array back like you can with mysql_fetch_assoc() ? The fetchRow() function has an optional argument that specifies the type of array that will be returned by the function. If no argument is specified then DB_FETCHMODE_DEFAULT is assumed. It then returns whatever the default has been set to. If you wanted to explicitly specify each one, then you would use DB_FETCHMODE_ORDERED for an array index numerically, or DB_FETCHMODE_ASSOC for an array index by field name (associative). DB_FETCHMODE_DEFAULT, DB_FETCHMODE_ORDERED and DB_FETCHMODE_ASSOC are all constants that are defined in DB.php.

So if we wanted to use an associative array, we would rewrite our code as follows:

<?php
while ($demoRow = $demoResult->fetchRow(DB_FETCHMODE_ASSOC)) {
echo $demoRow['demo_text'] . '<br>';
}
?>

Once we have finished with our connection to the database we can disconnect with $db->disconnect();.

Let’s take another look at those two scripts side by side:

Native PHP Functions

PEAR

<?php include( 'dbinfo.php' );
// no need to include PEAR
$conn = mysql_connect( $dbhost, $dbuser, $dbpass );
mysql_select_db($dbname);
$sql = 'SELECT * FROM demo';
$demoResult = mysql_query( $sql );
while ( $demoRow = mysql_fetch_row( $demoResult ) ) {
echo $demoRow[2] . '<br>';
}
mysql_close ($conn);
?>

<?php include('dbinfo.php');
require_once( 'DB.php' );
$db = DB::connect( "mysql://$dbuser:$dbpass@$dbhost/$dbname" );
// no need to select DB
$sql = 'SELECT * FROM demo';
$demoResult = $db->query($sql);
while ($demoRow = $demoResult->fetchRow()) {
echo $demoRow[2] . '<br>';
}
$db->disconnect();
?>

Great. So we’ve found another way of grabbing stuff data from my MySQL database. You may ask what the point is. Why not just do it the normal way ? Indulge me here for a moment.

Suppose you decided to migrate to PostgreSQL.

connect - yourusernamehere
CREATE TABLE "demo" (
	"demo_id" int8 DEFAULT nextval('serial'::text) NOT NULL,
	"demo_stamp" int8,
	"demo_text" character varying(50),
	PRIMARY KEY ("demo_id")
);
COPY "demo" FROM stdin;
1	978872113	Record number 1
2	978872652	Record number 2
3	978872652	Record number 3
4	978872652	Record number 4
5	978872652	Record number 5
.

There’s the same database dump but from PostegreSQL. Now if you had used native PHP database functions in your code you would first have to work out how the pgsql functions worked, and then you’d need to go play search and replace through all of your code.

Course if you had used PEAR, you would go and change the line

$db = DB::connect( “mysql://$dbuser:$dbpass@$dbhost/$dbname” );

$db = DB::connect( “pgsql://$dbuser:$dbpass@$dbhost/$dbname” );

I did, reran the script and this is what I got:

Record number 1
Record number 2
Record number 3
Record number 4
Record number 5

Fairly nifty.

What about error checking ?

Say we ran the MySQL script again, and made a bit of a typo in the SQL statement – $sql = "SELECT * FROM demo2";

Warning: Supplied argument is not a valid MySQL result resource in c:\temp\allandbmysql.php on line 7

Messy. So we could add some error checking to the line that does the query:

$demoResult = mysql_query( $sql ) or die( $sql );

which now will output

SELECT * FROM demo2

Which means we have an error and we can go and debug it. Course if you’ve got a couple of inner joins happening you may not easily be able to work it out. And if I made a typo in one of the field names I was selecting:

$sql = "SELECT bogus_field FROM demo";

I would get the same error message.

MySQL has a useful function called mysql_error() which will return the last error that was generated, so changing the line:

$demoResult = mysql_query( $sql ) or die( $sql );

$demoResult = mysql_query( $sql ) or die( mysql_error() );

will now result in

Table 'phptst.demo2' doesn't exist

in the first case and

Unknown column 'bogus_field' in 'field list'

in the second.

So what does PEAR have to offer in the way of error reporting ?

The function DB::isError() will check to see whether the result that has been returned to you is an error or not. If it is an error you can use DB::errorMessage() to return a text description of the error that was generated. You need to pass DB::errorMessage() the return value from your function as an argument.

Rewriting the PEAR code to use the error checking:

<?php
if ( DB::isError( $demoResult = $db->query( $sql ) ) ) {
echo DB::errorMessage($demoResult);
} else {
while ($demoRow = $demoResult->fetchRow()) {
echo $demoRow[2] . '<br>';
}
}
?>

Conclusion

We’ve seen that PEAR is functional as a database abstraction layer, but is it a good database abstraction layer ?

Let’s take a look at some properties of a good abstraction and see how PEAR fares:

properly named: The first thing you encounter in an abstracted object is it’s name, and you know what they say about first impressions.
makes sense: The properties and methods that make up the object should be appropriate for that object.
minimal: Introducing extraneous properties and methods only serve to complicate the object and your use thereof.
complete: The object must contain those properties and methods that are required to use the object for the purpose for which it was designed. A database object that lacks some fundamental database functionality is useless.

All code that is submitted to PEAR adheres to the coding standards that have been agreed on by the core programmers in the group. Often, a lot of discussion surrounds the naming and contents of classes that are submitted to PEAR, so peer review takes care of points one and two.

Even though the PEAR DB code is not without it’s bugs, development is ongoing. Hopefully this article has given you an idea of what is possible and a taste of things to come.

Tags: php, pear

Leave a Reply Cancel reply