Creating a MySQL Data Abstraction Layer in PHP

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.



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.

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:

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:

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:

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:

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

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:

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.

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:

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.

Hope this helps,


comments powered by Disqus