With websites now featuring full-blown dynamic applications that link to databases, data accessing has become a critical process. Often, an object-oriented solution is wanted to manage the data access operations. This works well, except when certain statements are hard-coded in that can cause headaches when a update is required. Alejandro Gervasio explains how a new category of tools, known as database interfaces, help to solve this problem.

Introduction

As websites evolved from simple linked regular HTML files to full-blown dynamic applications, where a database is the real workhorse for storing and delivering huge amounts of data, data accessing became a critical and sometimes hard to maintain process. Particularly, when working with a language such as PHP, designed to offer a friendly learning curve for building “quick and simple” applications, the issue is even more noticeable.

Certainly, it’s not the same thing to update the logic of our simple user registration PHP script, as it is to maintain a 10,000 line PHP application, as you’ll probably agree. In order to solve these difficulties and make maintenance as painless as possible, efforts have been underway to develop several tools aimed to fit in more demanding environments when building large database-driven applications. At first glance, these tools can be categorized in two general groups:

Database Abstraction

(X)HTML Generation

The first group covers a large variety of tools, targeted specifically to abstracting all of the processes involved in database access routines, which allows for more flexibility and portability in working with different database systems.

The second group may include several template tools, often developed as a set of classes that separate the application logic from the (X)HTML display logic. While both categories offer to developers different levels of complexity according to application requirements, they boost significantly the development process, by encouraging code reusability and extendibility.

However, let’s forget for a while the second group and focus our attention on the first one. Particularly in large applications, an object-oriented solution is frequently used to manage all of the database access operations. The big picture seems to be good enough to satisfy most of data accessing requirements. But, there is a drawback to this approach. Most of the time, SQL statements are hard coded within the application, which implies some big headaches when an update is required.

As a response to this issue, a new category of tools has emerged from a couple of years ago, widely known as database interfaces. What’s the point of having such tools? Database interfaces allow us to package DML statements, such as insert, update or delete commands within a class, providing a centralized mechanism for processing, verifying and securing data through one single access point inside the application.

Commonly, a database interface works in an intermediate level between the database abstraction layer and the (X)HTML generation layer. Having a class (or interface) that communicates the application logic with the database logic by using a unique shared point presents yet another major advantage: if the structure of the database has to be changed for any reason, it’s a lot easier to modify the code of the DB interface than find and replace embedded SQL within hundreds of lines of code inside an application.

Thus, coding a DB interface is really an instructive experience that hopefully exposes a wide range of advantages and immediate benefits. Why not face the challenge? After all, it’s going to be fun.

Through this series, I’ll build up progressively a DB interface class that will create data access objects (DAO) from a database table, packaging several DML statements into one single structure, in this way allowing an efficient interaction between the application and the database system. Before you end up thinking of this like a bunch of buzzwords with no practical implementation, keep reading to find out more.

{mospagebreak title=Overview of a database interface class: defining general guidelines}

It’s important to know some general guidelines before designing a database interface class. First, the class will be a logical representation of the database table structure with which it will be associated. In a few words, this means that each one of the table fields will be represented inside the class as data members, exposing the corresponding modifiers and accessing methods (in programming parlance, setters and getters).

However, instead of coding directly the DB interface, I’ll delegate the hard work to another class that I’ll call “DBIGenerator.” This class will behave as the “object factory” to generate each DB interface, taking advantage of the capacity offered by the “Factory” design pattern. Just in case you want to learn more about design patterns, the PHP manual contains a good introduction covering the subject. As usual, the Web is the best resource for finding good tutorials and articles treating the topic.

After going through the preliminaries of how to build a database interface, let’s start defining the core logic for the “DBIGenerator” class. As its name clearly suggests, it will be responsible for creating a database interface class, based on a few basic parameters: the name of the database interface and the file system path where the class will be created.

In our first approximation designing the “DBIGenerator” class, we’ll assume that we’re directly working with a hypothetical “users” database table that contains an ID field of type “AUTO_INCREMENT”, and three string-type fields to store “First Name”, “Last Name” and “email” values. As I said before, this will be changed at a later time, when I code the final version of the class, so you don’t have to worry about it. But now, let’s see the list for the “DBIGenerator” class, and next, break down the code, to understand the corresponding explanations in detail. So, keep reading to see how the class looks.

As mentioned before, let’s dissect the class code to properly understand what it does. To begin with, the constructor accepts as parameters the name of the DB interface to be created, defined as $name; the path where the class file will be physically located, that is $path; and finally an $options array that directly represents the field names of the database table tied to the interface. Again, this is merely for example purposes. Once we’ve finished developing our “DBIGenerator” class, it will be capable of handling the table fields for us.

As you can see, I’ve specified default values for the name of the DB interface class, as well as for the path where it will be generated. The constructor simply assigns these arguments as class properties:

Now, let’s take a look at the “create()” method, which handles the process to create the proper class file for making the DB interface work.

In general terms, all that this method does is concatenate strings, in order to build the different methods that will compose the DB interface class. Based on this simple principle, the data members’ declaration and the constructor are built as follows:

Using a regular “foreach” loop, the “set()” and “get()” methods are generated for each option passed as an argument. To clarify things, let’s say that we’ve passed a table field parameter named “firstname.” As a result, the DB interface class will have two methods, “setfirstname()” and “getfirstname()” respectively. In a similar way, if we have an “id” field name, the proper “setid()” and “getid()” methods will be created.

By this time, I assume the underlying idea of generating the class code is already understood. The next step is to create the “submit()”, “load()”, “update()” and “delete()” methods, useful for locating DML statements within the class structure. The next section of the article explains the mentioned methods.

{mospagebreak title=Inserting and selecting a row: the “submit()” and “load()” methods}

As stated previously, here’s the code to generate the “submit()” method:

As you can appreciate, this method simply packages an “INSERT” clause into the class file and performs the row insertion, by executing the query. Please notice that each method accepts a reference of a MySQL database connection object &$db, for purposes of database connectivity and being able to execute the SQL statement. In this case we’re not using the native MySQL PHP functions. Instead, we’re utilizing the method “performQuery()” provided by the object $db. Also, the ID of the row inserted is stored, by calling the “getInsertID()” method.

A similar approach is taken with the rest of the methods, thus the “load()” method is generated as listed below:

In this case, the “load()” method encapsulates a SELECT statement, in order to retrieve an specific row. In this scenario, it’s easy to guess how the other methods will be generated. Take a deep breath and join me in the next explanation.

{mospagebreak title=Updating and deleting a row: defining the “update()” and “delete()” methods}

The next methods that need to be defined are “update()” and “delete()”, in order to complete the round for packaging basic DML statements. Thus, here’s the code to generate the “update()” method:

The above listed method performs an “UPDATE” statement for updating a table row, using a technique similar to that shown in previous methods. Lastly, the “delete()” method, not surprisingly removes a row from the table, like this:

Well, at this time the four essential DML methods have been properly generated. Therefore, the next step consists of creating the DB interface class, simply by writing the generated content to a file at the location specified in the $path parameter, and using the $name argument provided to the “DBIGenerator” class:

That’s all about it. If we’ve passed a value of “user” as the name of the DB interface class along with “DBICLASSES/” as the path where the class will be located, a class file named “user.php” will be created within the “”DBICLASSES/” directory (assuming that you have the proper file permissions), and the name of the class will be “user” too.

Certainly, the power of DB interfaces is becoming evident by now. Since each DML statement is now located within the structure of each DB interface generated, instead of duplicating SQL queries in different sections of an application, it’s much easier to use the corresponding database interface to operate with the table associated to it. It is simple and effective.

With the DB interface class residing happily in the specified location, we need to instantiate an object from the class, for executing the SQL commands against the table. These kinds of objects, known as data access objects, are generated with the “getObject()” method, defined in the following way:

The above defined method checks whether the class file exists. If it does, it includes the file and return a new object from the class. Notice the usage of the “factory pattern” here. According to the parameters passed to the class, it behaves as an “object factory” returning a new DB interface object each time the method is invoked.

Once we’ve finished defining our “DBIGenerator” class, it would be very convenient to show how easily it creates a specific DB interface, establishing a single database table access point. Therefore, let’s jump straight into the code to see this class into action.

I firmly believe that dynamically building DB interfaces will be best understood by example. Bearing that concept in mind, let’s build an example that implements the “DBIGenerator” class and quickly creates a “User” DB interface class, which behaves as a centralized mechanism to access rows in a sample “users” table. Here’s the corresponding code:

The above example demonstrates how simple it is to create a DB interface class. First, I include the necessary class to connect to MySQL, along with the “DBIGenerator” class. Then, I define an array that contains the table field names that will be associated with the DB interface. Remember that I’m doing this from scratch, but in a later version, the process will be completely handled by the class.

Next, I pass to the “DBIGenerator” object the following parameters: the name of the DB Interface class (i.e. “User”), the path where I want the class file to be created, and finally the array containing the table field names.

Finally, the “create()” method is invoked, creating the class file at the specified location. Once the class is available to be used, I’m able to obtain a new $user object, by calling the “getObject()” method.

After executing the above snippet, a DB Interface class has been nicely generated inside a DBICLASSES/ directory, and looks like this:

Wasn’t that great? The “User” class is a logical representation of the sample “users” table, where each property represents a table field. Even better, it also presents the corresponding setters and getters methods for each property, as well as the usual DML statements (insert, update, delete) packaged into several class methods. Through such a useful structure, we can establish a single point to access the “users” table and select, insert, update, or delete rows according to the application’s logic.

This is a brief example. If we ever need to add a new user to the table, we’d do it in the following way:

It’s really that simple. By using the methods provided in the DB interface, we’ve added a new user to the table. As you might guess, the other row operations can be executed in a similar manner.

Summary

In this first part, I’ve demonstrated how DB interfaces can help to maintain SQL code within a class structure, providing a single interaction point between the database logic and the application logic. Of course, the sample “DBIGenerator” class is still immature, since it’s not capable of programmatically handling table fields. Also, all of the SQL statements use a hard-coded table name within the code.

In the next part of the series, these issues will be properly addressed, by improving the class and showing several examples to use DB Interfaces. Indeed, the topic is very promising. You won’t want to miss it!