Rob's Blog

I’ve been using my simple MySQLi wrapper class for some time now, and feel that it’s been reasonably well fine-tuned. It’s been used on several live projects, and is working extremely well. Most importantly, it has helped to simplify my code enormously. It allows me to execute parameterised SQL statements with a single line of code, and return the results as an object or array.

A little while ago I also started investigating PHP Data Objects (PDO). PDO provides much the same functionality as MySQLi (with the advantage of being relatively database independent), and also provides a really nice set of additional functions such as fetching a result set into an existing object. I’ve created a wrapper with an identical interface as the earlier MySQLi version, which encapsulates PDO functionality to a MySQL database. This is a drop-in replacement to the MySQLi version, and has allowed me to benchmark the differences in performance. In the process of checking out PDO, I’ve learned a couple of interesting things:

1) Unrelated to databases, but PHP does allows properties to be added to objects dynamically at runtime. You may have already known this, but it was news to me. Previously I’ve been using the magic __get and __set functions. Instead, I could have just created a new instance of StdClass, and added my properties to it as so:

  $obj = new StdClass();
  $obj->myProperty = 17;
  $obj->anotherProperty = array('Blue', 1.712);

Kris Jordan thinks that using __get and __set is around 30 times slower than using dynamic properties on StdClass.

2) The PDO wrapper class seems to be quicker by a significant margin. I’ve timed the execution time for various pages (by differencing the results of a call to “microtime()” at the start and end of each script) and the PDO version is consistently quicker. For some sites, PDO reduces script execution time by nearly 50%.

To help simplify usage, I’ve bundled everything together, defined an interface that exposes the relevant access methods, and added a few factory methods to an abstract base class:

  interface DFData
  {
    function Close();
    function StartTransaction();
    function CommitTransaction();
    function RollbackTransaction();

    function FetchRow($sql);
    function FetchRowAssoc($sql);
    function FetchSingleObj($sql);
    function FetchObj($sql);
    function ExecuteSQL($sql);
  }

  abstract class DF_DataObject implements DFData
  {
    public static function PDOInstance($username, $password, $database, $servername = 'localhost') {...}
    public static function MySQLiInstance($username, $password, $database, $servername = 'localhost') {...}
  }

I haven’t been playing with this for long, but so far it’s proven to be robust, reliable, and in the case of the PDO version, quick.

Once again, for those that have missed the earlier posts, the wrapper class allows to you eliminate SQL injection attacks by providing a really easy way to execute parameterised queries:

  $db = DF_DataObject::PDOInstance('username', 'password', 'databasename');

  // Insert statements
  $db->ExecuteSQL('INSERT INTO Fruit (Name, Colour) VALUES (?, ?)', 'ss', 'Banana', 'Yellow');
  $db->ExecuteSQL('INSERT INTO Fruit (Name, Colour) VALUES (?, ?)', 'ss', 'Apple', 'Green');
  $db->ExecuteSQL('INSERT INTO Fruit (Name, Colour) VALUES (?, ?)', 'ss', 'Lemon', 'Yellow');
  $insertID = $db->ExecuteSQL('INSERT INTO Fruit (Name, Colour) VALUES (?, ?)', 'ss', 'Orange', 'Yellow');

  // Update statements
  $db->ExecuteSQL('UPDATE Fruit SET Colour = ? WHERE ID = ?', 'si', 'Orange', $insertID);

  // Select statements
  $obj = $db->FetchSingleObj('SELECT Name, Colour FROM Fruit WHERE Colour = ?', 's', 'Green');
  /*
     $obj->Name = 'Apple'
     $obj->Colour = 'Green'
  */

  while ($fruit = $db->FetchObj('SELECT Name, Colour FROM Fruit WHERE Colour = ?', 's', 'Yellow'))
    echo $fruit->Name;

  /*
    Banana
    Lemon
  */

  $arr = $db->FetchRowAssoc('SELECT Name, Colour FROM Fruit WHERE Colour = ?', 's', 'Orange');
  /*
    $arr = array('Name' => 'Orange', 'Colour' => 'Orange');
  */

Source code is here