PHPBuilder - Best Practices: Database Abstraction



RSS Twitter
Articles Databases

Best Practices: Database Abstraction

by: Allan Kent
|
January 15, 2001

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.

1
|
2
|
3
|
4
|
5
Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Allan Kent

Comment:



Comment:

(Maximum characters: 1200). You have characters left.