Logo         Community
The Company
Your Account
Contact Us
Creating Database-Independent Applications With PEAR DB
Switch from one database to another without having to re-code your application

| The Abstract Approach |

In order to see how the database abstraction layer works, consider this next script, which rewrites the previous one using PEAR DB instead of PHP's MySQL extension:

<head><basefont face="Arial"></head>

// include DB

// open connection
$dbh = DB::connect("mysql://john:doe@localhost/db2");
if (PEAR::isError($dbh)) {
    die("Unable to connect. " . $dbh->getMessage());

// create and execute query
$query = "SELECT country, animal FROM symbols";
$result = $dbh->query($query);
if ($dbh->isError($result)) {
    die("Error in query: " . DB::errorMessage($result) . ". Query was: $query");

// check for returned records
if ($result->numRows() > 0) {
    // print them
    echo "<table cellpadding=10 border=1>";
    while($row = $result->fetchRow()) {
        echo "<tr>";
        echo "<td>" . $row[0] . "</td>";
        echo "<td>" . $row[1] . "</td>";
        echo "</tr>";
    echo "</table>";
} else {
    // or print status message
    echo "No rows found!";

// free result set memory

// close connection


This output of this script is equivalent to that of the previous one; however, since it uses database-independent functions to interact with the database server, it holds out the promise of continuing to work no matter which database is used.

The first step is, obviously, to include the abstraction layer class. Once this is done, open a connection to the database by invoking the class' connect() method and passing it a DSN string containing connection parameters: the database type, user name and password, host name, and database name. Once a connection has been made, the connect() method will return an object representing the connection. The object's query() method can then be used to execute SQL queries. Successful query execution returns a new result object (for SELECT queries) or a Boolean value indicating whether the query was successful (for non-SELECT queries). It's then a simple matter to use the object's fetchRow() method, in combination with a loop to iterate over the returned records and print each one. Once the heavy lifting is all done, the free() method frees up the memory associated with the result set, while the disconnect() method gracefully closes the database connection and disengages from the database.

In the event that a different database is used, the only change required in the script above would be to the line invoking connect() - a new connection string would need to be passed to the function with the type of the new database, and appropriate connection parameters. Everything else would stay exactly the same, and the code would continue to work exactly as before.

This is the beauty of an abstraction layer - it exposes a generic API to developers, allowing them to write one piece of code that can be used in different situations, with all the ugly bits hidden away and handled internally.  Which translates into simpler, cleaner code, better script maintainability, shorter development cycles and an overall Good Feeling.

How to do Everything with PHP & MySQL
How to do Everything with PHP & MySQL, the best-selling book by Melonfire, explains how to take full advantage of PHP's built-in support for MySQL and link the results of database queries to Web pages. You'll get full details on PHP programming and MySQL database development, and then you'll learn to use these two cutting-edge technologies together. Easy-to-follow sample applications include a PHP online shopping cart, a MySQL order tracking system, and a PHP/MySQL news publishing system.

Read more, or grab your copy now!

previous page more like this  print this article  next page
In trog...
Logging With PHP
Building A Quick-And-Dirty PHP/MySQL Publishing System
Output Buffering With PHP
Date/Time Processing With PHP
Creating Web Calendars With The PEAR Calendar Class
In the hitg report...
Crime Scenes
Animal Attraction
Lord Of The Strings
In boombox...
Patience - George Michael
Think Tank - Blur
My Private Nation - Train
In colophon...
Hostage - Robert Crais
The Dead Heart - Douglas Kennedy
Right As Rain - George Pelecanos
In cut!...
American Chai
The Core
Find out how you can use this article on your own Web site!

Copyright © 1998-2018 Melonfire. All rights reserved
Terms and Conditions | Feedback