Copyright Notice

This article is Copyright (c) Al Andersen, http://www.alandersen.com/

You are free to use this code in your programs providing you adhere to the most current version of the GNU General Public License (GPL).

Disclaimers And Caveats

This code is provided, as is. I don't guarantee that it will work, and I don't guarantee that it will not destroy data on your hard disk. Use at your own risk! Backup important files!

This code requires that a server version of PHP is installed on your computer, with MySQL and/or PostgreSQL database functionality compiled in.

This program requires that a web server, capable of running PHP scripts, is installed on your computer.

Database Abstraction Module For PHP

So, you've written your latest and greatest piece of software and you're now ready to unleash it upon the world. Excellent! It stores data in a database? Awesome! I can't wait to use it with my PostgreSQL application? What? It only works with MySQL? Why? Because everyone uses it? Yeah, right! Thanks, but no thanks!

This scenario is quite common, and it's unfortunate because with a little bit of forethought you can write a database-neutral application. Sure, you can use something like ADODB, but that's like using a sledgehammer for hanging a picture -- way too much power, and you don't even want to get into the overhead and learning curve involved, especially the latter if you're not a Windows programmer. So, what to do?

Learn MySQL? You missed my point! I prefer PostgreSQL. If you like MySQL, fine, but if you want your application to reach as many people as possible, you need to get over this "my database is better than your database" mentality, which brings us to the point of this tutorial.

Why not write an abstraction module that addresses the core functions for your database? You simply gather them into a module, put a function around them that has a generic name, and then use these new function names in your code in lieu of your database-specific functions. Providing you don't use any database-specific SQL, your code should now be highly portable. All someone needs to do is replace the database-specific calls in your abstraction module with their database calls.

To this end, I've created two database abstraction modules, on for MySQL and one for PostgreSQL. They're interchangeable, for the most part, and I've added them as attachments to this tutorial.

Now just write your code using standards-compliant SQL, use the generic function names, and then include the module that uses the the database prefer. It's as simple as that!

Here's an example of using these generic database functions to get rows of data from a table:

// This module includes a connect call to the database, so it's ready to go!
require_once ("db.php); 
$sql = "SELECT * FROM mytable ORDER BY id");
$resultset = db_query ($sql, __FILE__, __LINE__, "Cannot retrieve data from mytable!");
$data = array();
while ($row = db_fetch_array ($resultset))
{
    $data[] = $row;
}
db_free_result ($resultset);

If you've been using PHP for any length of time you should recognize that this is a standard way to retrieve data. The db_query, db_fetch_array, and db_free_result generic function calls probably look familiar. That's because they're just wrapper functions around the standard MySQL and/or PostgreSQL functions of like name.

That's not much more effort than you would expend in writing your normal database-specific code.

There are a couple of gotcha's (aren't there always?).

No matter how hard you try, your SQL is not always going to be standards-compliant. Database types are a major source of grief. For example, PostgreSQL has many types that are not available in MySQL, which in turn also has a few that aren't available in PostgreSQL. Types dealing with dates are especially onerous. Try to use data types that are portable, even if it means not having code as efficient as you'd like to be -- usually portability more than makes up for any lack of efficiency.

Another gotcha is that you can't use these two abstraction modules at the same time for they both use the same function names. You could get around this by renaming the functions from db_x to dbmysql_x and dbpsql_x in the appropriate files, but that would mean also renaming all the function calls in your code, which can be quite labor intensive for code of any significant size.

Play with the modules and see if you can use them, modify them, and/or expand them. If you have a better idea, go for it. Most of all, try to make your database code portable, which in turn makes it more useful for a greater audience.

We'll use this database abstraction module in our next tutorial.