Best Practices: Database Abstraction

A PEAR mini-HOWTO

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.

In the case of database abstraction, the complexities of connecting to a database
is hidden behind a standard API, thereby allowing the programmer to connect to
many different types of databases without relearning the methods and syntax
peculiar to each different type.

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.