Login

Handling MySQL Data Sets with Static Variables in PHP 5

In this second part of a four-part series, I finish building a sample MySQL driver by adding to it a whole new class responsible for iterating over database result sets through a friendly and intuitive API. This functional example shows that the use of a simple static property makes it easier to implement the Singleton pattern, which is convenient when developing a class that abstracts the access to a database server, be it MySQL or a different one.

Understanding the behavior of static class properties in PHP 5 is a straightforward process that can be tackled with minor headaches, even for inexperienced developers taking their first steps into the huge and sometimes esoteric field of object-oriented programming.

And the reason for this is simple: essentially, a static property is only an in-memory place that’s shared by all of the instances spawned from the same originating class. This means that any value assigned to that property can also be accessed and modified by those instances.

While at first sight, this is not especially interesting, it must be said that static properties do play a relevant role in the way that classes behave and carry data. In many cases, though, it’s far easier to explain the rationale behind this kind of property than to show or recreate from scratch a real-world scenario where they can be seen in action.

Bearing in mind that you may want to see an example that shows how to use static class variables in a concrete and realistic case, in this group of tutorials you’ll learn how to build an extensible MySQL driver. This driver will employ a few static properties to behave like a Singleton class, and to improve the way that it connects to the RDBMS as well.

In the introductory chapter of this series, I started off developing the first component of the MySQL driver, which turns out to be a basic class that acts like a wrapper for the native MySQLi class included with PHP 5. However, the most important thing to notice here is that this component uses a static $_instance property to turn the driver into a Singleton, which prevents us from dealing with multiple instances of it.

The driver in its current state is only capable of opening and closing a connection to the server, and running hard-coded SQL queries, so it’s desirable to provide it with more functionality. Therefore, in this second part of the series I’m going to add to the driver a second component. It will be comprised of a separate class, responsible for handling result sets through a simple API.

Ready to learn the full details regarding the construction of this data set processing class? Then start reading right away!

{mospagebreak title=Review: using a static property within a sample class}

Before we start building the second component of the MySQL driver mentioned in the introduction, it’d be really helpful to reintroduce the definition of the first class that comprises this driver. Among other tasks, it connects to the database server, runs SQL queries and so forth via a Singleton object.

As I said before, the above “MySQLiWrapper” class performs a few simple tasks, such as opening and closing a connection to MySQL, executing hard-coded SQL statements and so forth. While the respective implementations of the methods that carry out all these operations do deserve further analysis, the most important detail to stress here is the use of the static $_instance property within the “getInstance()” method. It returns to client code a Singleton instance of the class.

This method itself demonstrates a concrete use of a static variable that not only improves the behavior of its containing class, but also boots the performance of the application using the class in question.

And now that you’ve recalled the inner workings of the previous “MySQLiWrapper” driver, it’s time to start defining the class that iterates over result sets, since an object spawned from this class is created within the corresponding “query()” method.

As you’ll see in a few moments, this whole new class will be an implementer of the Iterator and Countable PHP 5 native interfaces. Its definition will be shown in the section to come. Therefore, to get there click on the link that appears below and keep reading.

{mospagebreak title=Building a MySQLi_Result wrapper class}

As you just saw in the preceding segment, the “query()” method that belongs to the “MySQLiWrapper” class comprises a new object of type “MySQLi_ResultWrapper.” As its name suggests, this object is tasked with processing result sets; it also inherits functionality from the native MySQLi_Result PHP 5 class.

You may be asking yourself what the originating class of this object looks like. Well, to answer that question, pay attention to the following code sample, which shows the definition of that class:

// determine if result set pointer is valid or not (implementation required by ‘valid()’ method in Iterator interface)

public function valid()

{

return $this->_pointer < $this->num_rows;

}

// free up result set

public function __destruct()

{

$this->close();

}

}

Despite the rather lengthy definition of the above “MySQLi_ResultWrapper” class, its underlying logic is very easy to follow. Most of its methods are inherited from the “Iterator” and “Countable” interfaces packaged with the SPL library. Every object spawned from this class will be able to iterate over a given result set by using a simple “foreach” construct.

In addition, the class includes a few additional methods that fetch rows from the queried table in different flavors, and retrieve information about its comprising fields as well.

Having discussed how the previous “MySQLi_ResultWrapper” does its business, I’m now going to code a script that shows how to use the MySQL driver in a useful fashion.

This will be done in the final part of this tutorial. Therefore, to see how this script will be built, read the following segment. It’s only one click away.

{mospagebreak title=Putting the MySQL driver to work}

As I anticipated in the section that you just finished reading, I coded a short script below that shows how to use the previous MySQL driver to pull some records from a “users” table and display the data on screen. Take a look at it:

As seen before, once a Singleton instance of the driver has been properly grabbed via the static “getInstance()” method, the object is then used for fetching user-related data from a fictional database table. Finally, the retrieved result set is traversed by using a simple “foreach” construct.

Even when for obvious reasons, the static $_property is hidden behind the class’ API, this code fragment shows in a nutshell how useful this property can be for building a Singleton MySQL abstraction class. But as you may have guessed, there are many other possible uses for static class variables. For the moment, though, go ahead and read the conclusions below.

Final thoughts

This is it for now. In this second part of the series, I finished building this sample MySQL driver by adding to it a whole new class responsible for iterating over database result sets through a friendly and intuitive API. This functional example showed that the use of a simple static property makes it easier to implement the Singleton pattern, which is convenient when developing a class that abstracts the access to a database server, be it MySQL or a different one.

In the tutorial to come, I’m going to show you how to improve the behavior of this driver even further, specifically when connecting to MySQL, by using yet another static property.