Abstracting a database class

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hey all!

I've been using PHP for a while and I'm hearing that I should look into
using it OO. I'm looking into making changes to the program Camera Life
(http://fdcl.sf.net ). I want to abstract the connection to the database
which is currently MYSQL and maybe implement another database, or just
leave it open for addition. If I get lucky and get the hang of this,
I'd also like to abstract calls to GD.

So firstly, is there a good solution to this problem? I've seen many
examples, but many of them are incomplete or simply academic. Also,
will I lose any functionality by doing it this way? I am currently
using a complicated SELECT statement with a CONCAT. Will this be
possible with the abstracted class?

Thanks for your consideration,

Re: Abstracting a database class

Have a look at this: http://adodb.sourceforge.net/ The abstraction is  
already done for you. Or, you can look on pear.php.net for their DB class  


Quoted text here. Click to load it

Re: Abstracting a database class

Full Decent wrote:
Quoted text here. Click to load it

You might want to take a peek at the following:


Or for PHP5:



Re: Abstracting a database class

Full Decent wrote:
Quoted text here. Click to load it
I usually implement this with a double abstraction.

The first is a database factory to provide concepts such as SQL query  
support, database connection sharing, error handling, etc.

The second is a database interface implementation that isolates all the  
SQL in one class. This implementation is the only class that  
instantiates the factory and uses it.

This way, I can change database factories (to connect to different  
databases) with ease and, since the interface implementation contains  
all the SQL, there is only one file I need to scan to adjust for the  
variances in the SQL languages. (e.g. limit 1 in MySQL vs. count = 1 in  

Is abstracting the database communications (with a factory) and  
abstracting the interfaces a good idea? I certainly think so. In fact,  
abstracting all communications is a good idea in my book. I use it for  
all my SOAP work too.


Re: Abstracting a database class

Quoted text here. Click to load it
Hmmm, ADODB already takes care of that for you. It also deals with date  
formatting and other issues. Plus it provides useful features like caching  
and SQL logging. Why reinvent the wheel?


Re: Abstracting a database class

Balazs Wellisch wrote:
Quoted text here. Click to load it
ADOdb is an example of an abstract database factory, as is PEAR DB. It  
was not my intention to imply that there was a need to write the  
factory, just that it was a good idea to isolate the concept of the  
factory from the concept of the SQL code driving the factory, and, in  
turn, to isolate the SQL code from the rest of the application space.  
Sometimes, due to the databases I am talking to and the in-house  
standards in place, it is necessary to write my own factory. This does  
not diminish the advantages of the two abstraction layer model.

In other OO languages, Java for example, it is common to have a caching  
database factory and a database bean - which is an interface  
implementation - that contains the SQL code for the application.


Re: Abstracting a database class

I am going to start by porting to ADODB. Later I will see if I can
abstract further by separating the accesses as Leong suggested.


Re: Abstracting a database class

Full Decent wrote:
Quoted text here. Click to load it

I would definetely consider it :) For instance, if you use mysql and  
then need to move the site to another server with a different dbms, I  
bet you'd hate to change all those mysql_* calls :)
So, off the top of the head, I'd go with something like

class DB {
   function DB($host,$db,$user,$pwd) {
     $this->host = $host;
     $this->db = $db;
     $this->user = $user;
     $this->pwd = $pwd;

   function connect() {
     // abstract


   function query($query) {
     // abstract


class DBMySql extends DB {
   function connect() {

class DBPostgreSql extends DB {
   function connect() {
     pg_connect("host=$this->host dbname=$this->db user=$this->user  

and so on. Then in any place you want to use database, assume you are  
using class DB:

  * @var DB
  * @access private
var $db;

Then instantiate it to whatever you use:

$this->db = new DBMySql("localhost","test","user","pwd");
$this->connect(); // works for any database now

Then, if you move to another database, simply change which class you  

As for the SQL statments, I'd say there could be a problem if the syntax  
differs in different dbms. For instance, in Oracle to get current date  
one would call sysdate(), and in ms-sql - getdate(). In this case you  
may want to consider aliases for function names which are replaced when  
query() method is called.


Re: Abstracting a database class

Full  Decent wrote:
Quoted text here. Click to load it

That's not a good approach. There is no point in abstracting calls to
the database, as the bulk of the differences between databases lies in
the sematics of the queries. What you really want to do is abstract the
entire data retrieval process. You application code doesn't need to
know that it's getting its data from a database. The data just needs to
be coming from somewhere: a MySQL database, a MS-SQL database, an
XML-file, or even a SOAP-compatible web-service. If your application is
agnostic in regards to data source, then it's automatically database

Re: Abstracting a database class

I like this idea and the possibility of running "DB queries" against a
real DB, the filesystem, or the network. Do you know of existing
classes that provide this level of abstraction beyond ADODB?

Re: Abstracting a database class

Full Decent wrote:
Quoted text here. Click to load it

It's just a matter of separating the concerns of your code. Doesn't
really require special logic. For example, say you need to retrieve a
article from the database, instead of running the query in the
application code itself, call a function to do it:

$message = GetMessageById($id);


$message = GetMessageByTitle($title);


$messages = GetMessagesByDate($start, $end);

If you want to get fancy, use a class factory

$messages = $message_factory->Load($criteria, $offset, $length);

There are many ways to do this. The bottomline is to isolate the data
save/retrieval code, so that you can swap in a different mechanism by
simply including a different file.

Re: Abstracting a database class


on 01/03/2006 07:38 PM Full Decent said the following:
Quoted text here. Click to load it

You may want to take a look at Metastorage. This is a tool that  
generates code for classes of objects that perform what is known as  
object-relational mapping. In simple words, you can abstract table rows  
and treat them as objects.

Besides the abstraction level that Metastorage generated code provides,  
it also makes you much more productive. You just need to describe the  
classes, variables, validation rules, relationships between classes and  
functions that you need manipulate your objects. Then Metastorage  
generates all the code of the classes that you describe in a few seconds.

Metastorage can also generate classes to install your database schema,  
report data extraction classes and classes to generate and process  
themed Web forms to perform common types of object manipulation operations.

A similar effort to write, test and debug equivalent code manually would  
take days, weeks of months to be ready. The more complex your project  
becomes, the greater are the productivity gains that Metastorage provides.

Here you may find more information about Metastorage:


Here are some screenshots of Metastorage generator Web interface and  
generated application forms using different themes

Here is a tutorial:


Manuel Lemos

Metastorage - Data object relational mapping layer generator

PHP Classes - Free ready to use OOP components written in PHP

Site Timeline