Developing a basic MySQL abstraction class - PHP

As I mentioned in the introduction, one of the most typical use of the native Iterator, Countable and ArrayAccess interfaces occurs when developing classes that have the ability tor traverse database result sets. In this particular situation, since my goal is to demonstrate how to implement the interfaces in a useful way, in the lines to come I’m going to start building a pair of classes that will work specifically with MySQL.

However, it’s possible to use the same interfaces with other popular RDBMS, such as MS SQL and Oracle. In theory you shouldn’t have major problems building classes that will work with those database servers, and will probably make use of the standard PDO extension (PHP Data Objects).

Thus, to begin, the first of two classes that I plan to build will be a proxy for the native MySQLi PHP 5 class, and its initial definition will be as follows:

class MySQLiWrapper extends MySQLi

{

   private static $_instance = NULL;

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

        }

       list($host, $user, $password, $database) = $config;

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

        if ($this->connect_error)

        {

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

        }

   }

    // prevent cloning class instance

    private function __clone(){}

    // perform query

    public function runQuery($query)

    {

        if (is_string($query) AND !empty($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();

    }

}

As I anticipated a moment ago, the previous “MySQLiWrapper” class is a Singleton wrapper for the native MySQLi class that comes with PHP 5. Essentially, all that this wrapper does is implement some straightforward methods, which allow you to connect to MySQL, run hard-coded SQL queries, retrieve insertion IDs, and so forth.

While it’s fair to say that this first sample class does perform a few interesting tasks like the ones mentioned above, there’s still no clue as to how it can implement some of the interfaces discussed in the introduction. Or is there? Well, if you analyze the implementation of the “runQuery()” method more closely, you’ll realize that it returns a new object, which is an instance of an undefined class called “MySQLi_ResultWrapper.”

The reason to code this method in such a way is simply the separation of concerns. On the one hand, there’s one class that take cares of establishing a connection to MySQL and executing queries, while on the other hand there will be a separate class that will handle database result sets generated by “SELECT” statements. That’s quite easy to understand, right?

Since the purpose of the still-undefined “MySQLi_ResultWrapper” class will be manipulating row sets, it becomes a good candidate for turning into an implementer of the pertinent Iterator, Countable and ArrayAccess interfaces. This will  allow us to traverse and count table records, as well as navigate easily across them.

Therefore, provided that you grasped the logic driving the previous “MySQLiWrapper” class, it’s time to show the definition of “MySQLi_ResultWrapper,” which at its initial stage will implement the methods defined by the Iterator interface.

To learn how this brand new class will be created, read the section to come. I’ll be there, waiting for you.