Review: the previous MySQL driver's classes - PHP

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.