Review: the MySQL driver so far - PHP

Before showing you how an undeclared static property can stop the MySQL driver built in the previous part from unnecessarily connecting multiple times to the database server, I'd like to review the definitions of the classes that compose the driver.

Here's the first of these classes. It is a wrapper for the native MySQLi class included in PHP 5. 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();

}

}

Aside from performing a few straightforward tasks, such as running hard-coded SQL queries and finding insertion IDs, the above "MySQLiWrapper" class can lazily connect to MySQL. This means that its "connect()" method will be called only once when performing a given query, thanks to the use of a static private property named "$_connected."

This shows a concrete usage of a static property within a class that can be utilized in a pretty realistic situation. Speaking of classes, here's the other one that comprises the driver. It handles result sets:

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

}

}

To be frank, you shouldn't feel intimidated by the lengthy definition of this class. It fetches rows from a given result set in different flavors (as objects, numerically-indexed or associative arrays) via a simple API. On the other hand, most of the remaining methods are implemented simply to make a data sets traversable by a "foreach" construct, as the class is an implementer of the "Iterator" and "Countable" native interfaces respectively.

And now that you've recalled how the MySQL drivers does its business, here's an example that shows how to use it for pulling data from a sample "users" database 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();

}

Even though at first sight the above example seems pretty trivial, it does demonstrate how the internal use of a static property can prevent an instance of the "MySQLiWrapper" class to connect to MySQL each time it performs a specified SQL query. That wasn't too difficult to grasp, was it?

As I explained at the beginning, it's also possible to get the same benefit by using a static variable not declared explicitly. As you may know, whenever a static variable is created inside a method of a class, it becomes automatically available to all of the spawned instances. Clever use of this knowledge can be of great help in preventing unnecessary connections to a RDBMS.

Based on this intrinsic behavior of static variables, in the following section I'm going to introduce some subtle changes to the previous "MySQLiWrapper" class. As you'll see in a moment, it will make use of an undeclared static variable to connect only once to MySQL.

To learn how this will be accomplished, read the segment to come. It's only one click away.