Creating a Data Abstraction Layer in PHP

[Motoma]

This article is cross posted from my personal blog. You can find the original article, in all its splendor, at http://motomastyle.com/creating-a-my...-layer-in-php/.

Introduction:
The goal of this tutorial is to design a Data Abstraction Layer (DAL) in PHP, that will allow us to ignore the intricacies of MySQL and focus our attention on our Application Layer and Business Logic. Hopefully, by the end of this guide, you will have a working DAL and learn a little about PHP, MySQL, and Object-Oriented design in the process.

Assumptions:
You know PHP and have is set up.
You know MySQL and have it set up.
You have a cursory knowledge of object-oriented methodologies.
You are not a smacktard.

Defining the Project:
The purpose of this project is to create a working MySQL DAL to distance ourselves from the menial tasks that are associated with PHP/MySQL systems, such as passing around connection handles and iterating through result sets. We will create an object which will create and maintain our connection to our database, provide us with the tools necessary to perform all of our required SQL queries, properly handle errors, tidily present our data, and keep our application code clean.

Defining the Object:
Our Database object will consist of local variables, a constructor, mutators, and methods. I will leave out destructor and scoping from the database class, in order to maintain compatibility with PHP4.

Local Variables:
We will need variables to keep track of our connection information, as well as keep track of any open connections we may have.
The Constructor:
The Constructor for our class will server to create an instance of our Database class. It will provide the class' variables with their initial values.
The Mutators:
Our class will provide the functionality for changing databases on the fly, therefor we will design it to allow us to alter our connection information, both individually, and as a whole.
The Methods:
We are designing our DAL to connect to a database, therefor our Database class better have a connect method! Along with connect method, it should have a way of disconnecting, performing queries, and returning results.

Object Parts:
Our Database class wills start off simple enough; we need only to define the class and our variables to get the ball rolling:

class Database
{
    var $database_name;
    var $database_user;
    var $database_pass;
    var $database_host;    
    var $database_link;
 

Next we move on to our constructor. Remember, this will give us the initial values for our database connection, so it is fine if we enter our current information as the default:

    function Database()
    {
        $this->database_user = "motoma";
        $this->database_pass = "bI1dU5";
        $this->database_host = "localhost";
        $this->database_name = "dalDatabase";
    }

Now we move on to our mutators. These will allow us fine-tuned control over our connection data. We will want one method for each variable we want to set. We could also, optionally, create one to set all in one call:

    function changeUser($user)
    {
        $this->database_user = $user;
    }
 
    function changePass($pass)
    {
            $this->database_pass = $pass;
    }
 
    function changeHost($host)
    {
        $this->database_host = $host;
    }
 
    function changeName($name)
    {
        $this->databse_name = $name;
    }
 
    function changeAll($user, $pass, $host, $name)
    {
        $this->database_user = $user;
        $this->database_pass = $pass;
        $this->database_host = $host;
        $this->database_name = $name;
    }

We are really on a roll now! On to our connection related methods. Our connect method will need to establish a connection using the credentials we provide, and let us know when connection errors occur:

    function connect()
    {
        $this->database_link = mysql_connect($this->database_host, $this->database_user, $this->database_pass) or die("Could not make connection to MySQL");
        mysql_select_db($this->database_name) or die ("Could not open database: ". $this->database_name);        
    }

Conversly, the disconnect method will close any connection our class has previously made:

function disconnect()
    {
        if(isset($this->database_link)) mysql_close($this->database_link);
        else mysql_close();    
    }

That was not very dificult, was it? On to our methods. The first, and most basic thing we will need to do is perform a query which returns no results. DELETES, UPDATES, and INSERTS fall into this category of query. Before we can perform a query, however, we should check if the database connection has been established. If not, we will need to establish a connection before we perform our query:

    function iquery($qry)
    {
        if(!isset($this->database_link)) $this->connect();
        $temp = mysql_query($qry, $this->database_link) or die("Error: ". mysql_error());        
    }

Onto our first result returning query handler. Personally I like working with arrays. Associative arrays make sense when returning a result set, due to the structures in which we commonly see these results displayed. Much like our resultless query handler, we will need to check on our connection before we try anything. It will then perform the query, and iterate through the result set, placing it tidily in an associative array for us to work with.

function query($qry)
    {
        if(!isset($this->database_link)) $this->connect();
        $result = mysql_query($qry, $this->database_link) or die("Error: ". mysql_error());
        $returnArray = array();
        $i=0;
        while ($row = mysql_fetch_array($result, MYSQL_BOTH))
            if ($row)
     $returnArray[$i++]=$row;
mysql_free_result($result);
        return $returnArray;
    }
}

One could add a few more functions to this list, namely for returning the last inserted id of an auto_increment field and checking for table existence.

Using our DAL:
Now that we have a DAL, how do we use it? Here's an example:

<?php
// Our Database Class
include("lib/database.php");
 
// Instantiate our Database Class
$db = new Database();
// Query!
$resArr = $db->query("SELECT * FROM users WHERE userid != 1");
 
// $resArr is now an associative array containing the entire result set
// of the SQL query. We can now use and abuse this data as necessay.
// In this hypothetical situation, I am returing all of the users in
// my table except the Administrator
 
echo '<table><tr><th>UserID</th><th>UserName</th>';
echo '<th>RealName</th><th>Homepage</th></tr>';
foreach($resArr as $user)
{
    echo '<tr><td>'.$user['userid'].'</td><td>'.$user['username'].'</td>';
    echo '<td>'.$user['realname'].'</td><td>'.$user['homepage'].'</td></tr>';
}
echo "</table>";
?>

Wrap Up:
Hopefully this tutorial has shown you how to quickly and easily create a minimal Data Abstraction Layer for your web application. Having a DAL to work with allows us to focus on the Application and Business layers of our applications, without having to go through the monotony of data access.

[nathj]

I'm new to web development but with a background in OO desktop application development. This tutorial has sown me exactly what I needed to know. I can now get what I want from my web system. This was brilliantly clear

Thank you
Nathan

[Motoma]

I'm new to web development but with a background in OO desktop application development. This tutorial has sown me exactly what I needed to know. I can now get what I want from my web system. This was brilliantly clear

Thank you
Nathan

Glad it helped you out. Good luck with your projects, and come back to TSDN any time you have a question.

[realin]

Glad it helped you out. Good luck with your projects, and come back to TSDN any time you have a question.

wow that is something i have been looking for :)
thanks mann :p

[kovik]

Well, I guess I'll have to be the critic then.

This database wrapper class is extremely simplistic... Almost too much so. I'm not sure if this is as far as you have gone with this, as it is very possible that you were giving a very simplistic example, but I feel that I should at least extend upon it for people who have not written a database wrapper before.

We (and by we, I mean the DevNetwork community) have found that the database wrapper should handle nearly everything that the database will do. We give it a query, it handles the rest. However, it doesn't do all of this in one function... That drastically lowers its flexibility.

There is one query function. This function performs the query, and then saves the result if it is a valid resource using is_resource(). SELECT statements create valid resources. UPDATE statements do not. Whenever a result set is created, we save it. The result itself is an entirely different object, and can be handled on its own.

Getting the rows of a result object is handled in the results object with different fetch functions like FetchRow(), FetchObject(), etc. We also give shortcuts to these functions in the database class that automatically uses the last result set object (which should be saved in the database object).

The way the database objects works is like this:

$pSql->Query("SELECT * FROM `table`;");
 
while($data = $pSql->FetchObject())
{
    print_r($data);
}

If you need to handle queries that don't return a results set:

$pSql->Query("SELECT * FROM `table`;");
$pSql->Query("INSERT INTO `table` SET `foo` = 'bar';"); // Doesn't return a results set
 
// Still using the data from the original SELECT statement
while($data = $pSql->FetchObject())
{
    print_r($data);
}

And if you need to handle multiple queries that DO return results sets:

$pSql->Query("SELECT * FROM `table`;");
$pResults = $pSql->GetResults();
 
$pSql->Query("SELECT * FROM `anotherTable`;");
$pResults2 = $pSql->GetResults();
 
while($data = $pResults->FetchObject())
{
    print_r($data);
 
    if($data2 = $pResults2->FetchObject())
    {
        print_r($data2);
    }
}

We've had a few debates on database wrappers and the goal is flexibility and capability. The flexibility comes from not restricting yourself to something like handling everything through the Query() function or not saving your results sets as separate entities in case you have other plans for them, like sending the resource to different functions. The capability comes from things like saving the errors, getting the last insert id, determining how many rows were affected, determining how many rows were matched (which is different than affected!), getting stats, explaining queries, saving the amount of queries performed, timing queries, etc.


Also, a personal preference of mine is to refrain from saving the username, password, or database name inside of the database class. You need to reconnect at every page request anyway, and when you serialize objects across sessions and such, it will attempt to serialize the database connection as well, leaving your credentials stored in plain text.

Another preference of mine is to use exceptions in the query class that save the mysql_error() message along with the erroneous query, and then just run all of my database queries through the same try...catch statement, and whenever an exception is caught, it logs the error message for me and tells the user that there was an error processing their request.


I'm not sure how long you've been using yours, so I don't want to seem rude or anything. My first attempt at a database wrapper was similar to this. However, just like all of my other classes, it developed through necessity. I re-factored as I found that I needed more abilities, more efficiency, more security measures, and cleaner usage methods so that the resulting code was as short and sweet as I could make it.

[nathj]

I re-factored as I found that I needed more abilities, more efficiency, more security measures, and cleaner usage methods so that the resulting code was as short and sweet as I could make it.

This is something that is always worth doing. I have taken the basic DAL that is described in this article, and as a newbie at the time it was fantastic. It really helped me get to grips with PHP as an OO language. I have since tweaked my DAL to handel some of the issues you mention in your post.

As a starting point I think the DAL listed here is a great way to get going. But we should all remember that however good we think something we've developed is there is almost always room for improvement, but we've got to start somewhere.

Cheers
nathj

[pbmods]

Now that PHP 4 has been discontinued, shall we update this article for PHP 5?

[Markus]

OOP scares me :(

I've yet to delve into it..

[coolsti]

I think this is a very good How To and I feel it is very good that it is as simple as it is, in order to be clear to people who have not done much OOP before. Of course it can be made more complex for more flexibility and functionality, as one poster mentioned, but as a tutorial on the use of OOP, it is excellant.

I have not yet myself gone so far as to make a class to handle my database queries, but have made a lot of functions to simplify things. The beauty of making a class is that everything becomes well organized and easy to find (i.e. no more "where is that function that did that database select for me, I can't remember....") and namespaced. But I have made a lot of classes that do a lot of other stuff for me, and this tutorial will maybe push me to make one for databases.

However, I believe that one can also go too far with OOP programming in a flexible scripting type language like PHP. For me, my application is so database oriented, that each and every page requires a database connection, so I don't need a class to create and maintain this for me. The connection is made early on in the scripts and the connection is maintained until the script ends.

Another issue I have is whether it is important or not to have mutators. I started off writing lots of these for my classes, but then dropped them. PHP is too flexible; if I need to change the value of a class variable, I change it directly rather than call a function to change it for me (public vs. private variables). What I am changing and the fact that it belongs to a class is obvious from the code. Of course a mutator would be useful if you want to carry out an associated task along with changing the variable in question, like logging the variable change.

But it is a great How To and tutorial. Programming with a bit of OOP like this simplifies greatly the task of programming PHP.

[DaWei]

If you don't use mutators it's a good bet that you're a one-man show writing relatively small applications that aren't widely distributed, modified, and maintained.

[bilibytes]

You could also explain the Factory Pattern an show how to implement it on DAL point of view.

[Dormilich]

wouldn't be the Singleton Pattern be the first pattern discussed, when it comes to database handling?

btw. what use shall the Factory Pattern have, concerning DAL (that's not clear to me)

[pbmods]

@Dormilich
DBAL does work much better with the singleton pattern, as maintaining multiple database connections per script execution tends to cause more problems while trying to solve a problem that doesn't exist.

The factory pattern is often useful in situations where you might be using different database adapters. Generally this is only useful for one-size-fits-all frameworks such as Zend Framework:

$db = Zend_Db::factory('Pdo_Mysql', array(
    'host'     => '127.0.0.1',
    'username' => 'webuser',
    'password' => 'xxxxxxxx',
    'dbname'   => 'test'
));

-- from http://zendframework.com/manual/en/zend.db.html

In the code above, Zend_Db::factory() takes care of loading the appropriate adapter class and satisfying any platform-specific dependencies.

A database class is not the best example of a situation calling for the factory pattern because the type of adapter does not generally change that much.

In other words, if you have data for the same application stored on two different database systems, you're probably doing it wrong.

A data transfer class, for example, might use a factory to load the appropriate class depending on the protocol of a given URL. E.g.:

$client = Data_Transfer_Client::factory($url);
// $url = 'http://...' => returns Data_Transfer_Client_Http
// $url = 'smb://...' => returns Data_Transfer_Client_Samba
// etc.
 

And so on. Because the value of $url is so volatile, it is much easier to write a factory method and put all one's if statements in a single location.

[azegurb]

here
i dont understand what does this function do.

i try to undertand of loop objective but nothink i understood.
can you explain me for what reason there is loop used and what is a purpose to create this array

thanks beforehands

function query($qry) 
    { 
        if(!isset($this->database_link)) $this->connect(); 
        $result = mysql_query($qry, $this->database_link) or die("Error: ". mysql_error()); 
        $returnArray = array(); 
        $i=0; 
        while ($row = mysql_fetch_array($result, MYSQL_BOTH)) 
            if ($row) 
     $returnArray[$i++]=$row; 
mysql_free_result($result); 
        return $returnArray; 
    } 
}

[Dormilich]

this method does a query and immediately fetches the result into an array (you’ll probably have more than one result row)

[azegurb]

Pls can you show it in a simple real wordl example
thank you very much

[Markus]

The article shows you a clear example of how to use the class.

[azegurb]

Ok thank you very much. i have already understood