Improving MySQL Connection with Static Variables in PHP 5 Classes

In this third part of a four-part series on static variables, I show how to use another static property in the previous MySQL driver to connect “more cleverly” to the database server. This example demonstrates how powerful a property like this can be, when utilized in an effective manner.

While at a glance the subject may seem rather irrelevant and even pretty boring, the truth is that the proper use of static variables in PHP 5 classes can be of great help in improving the way that they perform certain tasks and their relationship in a given application context.

Thus, if you’re a PHP developer who wishes to see how to utilize static properties in a real-world scenario, then this series of articles may be what you’re looking for. It teaches how to build a functional object-oriented MySQL driver, which will employ a couple of class-level variables to reduce the instantiation of objects and connect to MySQL more efficiently.

Now that you know what to expect from this series, I’d like to spend a few moments reviewing the concepts discussed in the previous part. In that article I finished building the aforementioned MySQL driver by appending to it a class exclusively responsible for handling result sets.

With the addition of this class, I was able to code a script that used a Singleton instance of the driver first for connecting to the server, then for selecting a specified database, and finally for fetching records from a fictional “users” table. At the end of its execution, the script also showed how to use the functionality of the driver to iterate over those records via a simple “foreach” construct. In short, the script was simple and effective.

Of course, the driver should be considered and evaluated as to what it really is: an example. Its development demonstrated how a simple static property can be used for turning a whole class into a Singleton – which is useful when working with a database handler object like the one mentioned above.

Due to their nature, however, static variables can be utilized in multiple contexts and with purposes other than building Singleton classes. With that idea in mind, I’m going to show how to use yet another variable to improve the way that the driver establishes a lazy connection to MySQL.

The experience will be really educational, trust me. So join me in this third part of the series and begin reading right now!

{mospagebreak title=Review: the previous MySQL driver’s classes}

Since my goal in this tutorial is to show how the use of another static property within the MySQL driver can more efficiently handle a connection to MySQL, it would be helpful to reintroduce the definitions of its building classes, so you can recall how they function.

That being said, here’s the signature of the first sample class, which is a wrapper for the MySQLi class included with PHP 5:

 

class MySQLiWrapper extends MySQLi

{

 

private static $_instance = NULL;

private $_config = array();

 

// return Singleton instance of MySQL class

public static function getInstance(array $config = array())

{

if (self::$_instance === NULL)

{

self::$_instance = new self($config);

}

return self::$_instance;

}

 

// private constructor

private function __construct(array $config)

{

if (count($config) < 4)

{

throw new Exception(‘Invalid number of connection parameters’);

}

$this->_config = $config;

 

}

 

// prevent cloning class instance

private function __clone(){}

 

// establish a connection to MySQL

public function connect()

{

list($host, $user, $password, $database) = $this->_config;

parent::__construct($host, $user, $password, $database);

if ($this->connect_error)

{

throw new Exception(‘Error connecting to MySQL : ‘ . $this->connect_errno . ‘ ‘ . $this->connect_error);

}

unset($host, $password, $database);

}

// perform query

public function runQuery($query)

{

if (is_string($query) AND !empty($query))

{

// lazy connect to MySQL

$this->connect();

// run the specified query

if ((!$this->real_query($query)))

{

throw new Exception(‘Error performing query ‘ . $query . ‘ – Error message : ‘ . $this->error);

}

return new MySQLi_ResultWrapper($this);

}

}

 

// get insertion ID

public function getInsertID()

{

return $this->insert_id;

}

 

// close database connection

public function __destruct()

{

$this->close();

}

}

From the above code fragment, it’s easy to follow the logic implemented by each method of the “MySQLiWrapper” class. All it does is establish a lazy connection to MySQL, run SQL queries and find insertion IDs when possible.

Undoubtedly, the workhorse method of this class is the one called “query().” After executing a hard-coded query, it returns a composed object of type “MySQLi_ResultWrapper.” To make things clear for you, the originating class of this object has the following definition:

class MySQLi_ResultWrapper extends MySQLi_Result implements Iterator, Countable

{

private $_pointer = 0;

 

// fetch row as an object

public function fetchObject()

{

if (!$row = $this->fetch_object())

{

return NULL;

}

return $row;

}

 

// fetch row as an associative array

public function fetchAssocArray()

{

if (!$row = $this->fetch_assoc())

{

return NULL;

}

return $row;

}

 

// fetch row as an enumerated array

public function fetchNumArray()

{

if (!$row = $this->fetch_row())

{

return NULL;

}

return $row;

}

 

// fetch all rows

public function fetchAll($type = MYSQLI_ASSOC)

{

if ($type !== MYSQLI_ASSOC AND $type !== MYSQLI_NUM AND $type !== MYSQLI_BOTH)

{

$type = MYSQLI_ASSOC;

}

if (!$rows = $this->fetch_all($type))

{

return NULL;

}

return $rows;

}

 

// get definition information on fields

public function fetchFieldsInfo()

{

if (!$fieldsInfo = $this->fetch_fields())

{

throw new Exception(‘No information available for table fields.’);

}

return $fieldsInfo;

}

 

// get definition information on next field

public function fetchFieldInfo()

{

if (!$fieldInfo = $this->fetch_field())

{

throw new Exception(‘No information available for current table field.’);

}

return $fieldInfo;

}

 

// move pointer in result set to specified offset

public function movePointer($offset)

{

$offset = abs((int)$offset);

$limit = $this->num_rows – 1;

if ($limit <= 0 OR $offset > $limit)

{

return FALSE;

}

unset($limit);

return $this->data_seek($offset);

}

 

// count rows in result set (implementation required by ‘count()’ method in Countable interface)

public function count()

{

return $this->num_rows;

}

 

// reset result set pointer (implementation required by ‘rewind()’ method in Iterator interface)

public function rewind()

{

$this->_pointer = 0;

$this->movePointer($this->_pointer);

return $this;

}

 

// get current row set in result set (implementation required by ‘current()’ method in Iterator interface)

public function current()

{

if (!$this->valid())

{

throw new Exception(‘Unable to retrieve current row.’);

}

$this->movePointer($this->_pointer);

return $this->fetchObject();

}

 

// get current result set pointer (implementation required by ‘key()’ method in Iterator interface)

public function key()

{

return $this->_pointer;

}

 

// move forward result set pointer (implementation required by ‘next()’ method in Iterator interface)

public function next()

{

++$this->_pointer;

$this->movePointer($this->_pointer);

return $this;

}

 

// 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();

}

}

As you can see above, since the “MySQLi_ResultWrapper” class is an implementer of the “Iterator” and “Countable” SPL interfaces, it’s really easy to traverse database result sets by using a “foreach” iterator.

Still not convinced of this? Take a look at the script below, which performs exactly that iteration over some records fetched from a sample “users” table:

<?php

 

try

{

// connect to MySQL

$db = MySQLiWrapper::getInstance(array(‘host’, ‘user’, ‘password’, ‘database’));

 

// fetch users from database

$users = $db->runQuery(‘SELECT * FROM users’);

 

// display user data

foreach ($users as $user)

{

echo ‘First Name: ‘ . $user->fname . ‘ Last Name: ‘ . $user->lname . ‘<br />’;

}

}

// catch exceptions

catch(Exception $e)

{

echo $e->getMessage();

exit();

}

Now that you’ve seen how to use the tandem composed of the “MySQLiWrapper” and “MySQLi_ResultWrapper” classes, you may think that adding an extra static property to the former to improve its behavior might be a pointless process. Well, if you think that, I’m afraid you’re wrong.

If you study the implementation of the “query()” method included within the “MySQLiWrapper” class, you’ll realize that it connects lazily to MySQL. This is good and neat, since establishing a connection to a database is always an expensive, time-consuming operation. However, what happens if a script calls this method multiple times? Obviously, there will be multiple calls to the database server, too!

That’s an undesirable side effect that must be fixed as soon as possible. In this particular case, we’ll solve the problem by using another static property, which will be added to the class.

Want to see how this will be done? Then read the section to come.

{mospagebreak title=Connecting to MySQL only once}

As I explained in the previous section, preventing the “MySQLiWrapper” class from attempting to connect to MySQL multiple times can be handled through the use of an additional static property. Basically, the idea is to utilize this variable as a flag that makes sure that the connection is made only once.

However, the concrete implementation of this solution should be done within the “connect()” method of the class. This point is demonstrated by the code sample below. Take a look at it, please:

 

class MySQLiWrapper extends MySQLi

{

 

private static $_instance = NULL;

private static $_connected = FALSE;

private $_config = array();

 

// return Singleton instance of MySQL class

public static function getInstance(array $config = array())

{

if (self::$_instance === NULL)

{

self::$_instance = new self($config);

}

return self::$_instance;

}

 

// private constructor

private function __construct(array $config)

{

if (count($config) < 4)

{

throw new Exception(‘Invalid number of connection parameters’);

}

$this->_config = $config;

 

}

 

// prevent cloning class instance

private function __clone(){}

 

// establish a connection to MySQL

public function connect()

{

// if no previous connection exits, connect to MySQL

if (self::$_connected === FALSE)

{

list($host, $user, $password, $database) = $this->_config;

parent::__construct($host, $user, $password, $database);

if ($this->connect_error)

{

throw new Exception(‘Error connecting to MySQL : ‘ . $this->connect_errno . ‘ ‘ . $this->connect_error);

}

// connection is successful

self::$_connected = TRUE;

unset($host, $password, $database);

}

}

 

// perform query

public function runQuery($query)

{

if (is_string($query) AND !empty($query))

{

// lazy connect to MySQL

$this->connect();

// run the specified query

if ((!$this->real_query($query)))

{

throw new Exception(‘Error performing query ‘ . $query . ‘ – Error message : ‘ . $this->error);

}

return new MySQLi_ResultWrapper($this);

}

}

 

// get insertion ID

public function getInsertID()

{

return $this->insert_id;

}

 

// close database connection

public function __destruct()

{

$this->close();

}

}

Now, the “MySQLiWrapper” class declares a brand new $_connected static property. This property is used within the “connect()” method to make sure that the connection to MySQL is established only once. With this simple approach, I’m getting the best of both worlds: on one hand, the method is only called on demand (read a lazy connection), while on the other hand, the connection is performed only once.

As I said before, static properties can be really useful for improving the behavior of classes when used in a clever way; the code fragment illustrates this concept.

Now that you’ve grasped how the enhanced version of the “MySQLiWrapper” class does its business, it’s time to set up an example that shows it in action. This example will be coded in the following section, so click on the link below and read the lines to come.

{mospagebreak title=A final example}

To demonstrate the actual functionality of the $_connected static property just added to the “MySQLiWrapper” class, below I wrote a script that runs two trivial queries against the same sample “users” table. In this case, the class will happily connect to MySQL only once, which is the expected behavior. 

The script looks like this:

<?php

 

try

{

// connect to MySQL

$db = MySQLiWrapper::getInstance(array(‘host’, ‘user’, ‘password’, ‘database’));

 

// fetch users from database

$users = $db->runQuery(‘SELECT * FROM users’);

 

// display user data

foreach ($users as $user)

{

echo ‘First Name: ‘ . $user->fname . ‘ Last Name: ‘ . $user->lname . ‘<br />’;

}

 

// fetch users’ first names from database

$users = $db->runQuery(‘SELECT fname FROM users’);

 

// display user data

foreach ($users as $user)

{

echo ‘First Name: ‘ . $user->fname . ‘<br />’;

}

 

}

// catch exceptions

catch(Exception $e)

{

echo $e->getMessage();

exit();

}

There you have it. Thanks to the inclusion of a static variable, now any object spawned from the previous “MySQLiWrapper” class will establish only a single connection to MySQL. Hopefully, this final example will spark your own inspiration and get you started using static properties in all sorts of creative ways.

Final thoughts

In this third part of the series, I showed how to use another static property within the previous MySQL diver to connect “more cleverly” to the database server. Once again, this example demonstrated how powerful a property like this can be when utilized in an effective manner.

In the last chapter of the series I’m going to teach you how to get the same benefit when connecting to MySQL via a static, local variable, instead of explicitly using a class property.

Don’t miss the upcoming tutorial!

Google+ Comments

Google+ Comments