Using ext/mysqli: Part II - Extending mysqli

Intended Audience

This brief article is intended for novice programmers who have some experience using the new mysqli extension. It assumes that the basic concepts behind databases and programming are understood, and that readers know how to execute a PHP script and send a query to a MySQL server. Basic familiarity with object-oriented programming will also be useful.

Introduction

The first part of this series provided a quick overview of ext/mysqli. In this installment, we will discuss how to extend the default mysqli class to cleanly provide additional functionality.

Using and Extending the mysqli Class

One of the charms of the new mysqli extension is that it provides a very useful object-oriented (OO) interface that is efficient, compact and easily extensible.

The functionality of the OO interface is accessed via the mysqli, mysqli_stmt and mysqli_result classes. The mysqli class represents a connection, while the mysqli_result and mysqli_stmt classes represent, respectively, a result set for a query and a prepared statement.

In this article, we will focus on the mysqli class, as this is the easiest class to extend.

Using the mysqli Class

Here is a brief snippet of code to serve as a refresher on how the class is used. If you need a more detailed introduction to the use of the mysqli class, please read the first part of this series.

<?php

/* Connect to a MySQL server */
$mysqli = new mysqli('host', 'user', 'pass', 'world');

/* Send a query to the server
* mysqli::query returns an object of class mysqli_result */
$result = $mysqli->query(
  'SELECT name, population
    FROM city
    ORDER BY population DESC LIMIT 5');

print("The five largest cities are:\n");

/* Fetch the results of the query */
while( $row = $result->fetch_assoc() ){
  printf("\t%s (%s)\n", $row['name'], $row['population']);
}

/* Destroy the result set and free the memory used */
$result->close();

/* Close the connection and free the memory used*/
$mysqli->close();

?>



Extending the mysqli Class

The default set of methods and properties that are a part of the mysqli class are fairly complete. Developers are provided with all of the tools needed to create and destroy connections, send queries to the server, view the status of the MySQL server, and so on.

However, as the class is intended for general use, many developers will find that their applications can benefit from a customized version of the functionality present in mysqli.

One powerful way to include the extra functionality is by sub-classing the mysqli class.

This approach has two major benefits:

• Consistency: By extending the parent class in a sensible way, a developer can provide an interface that is well-organized and comfortable for users of the parent class.
• Terseness: Rather than rewriting code present in the parent class, the subclass can just reuse the functionality. Once again, a developer who knows the parent class can easily follow the changes made in the child class.

Children with Bad Habits

Here is a trivial example that adds a new method in a child class of mysqli. The new method, called query_and_fetch(), sends a query to the server and then returns the first field of the first row of data.

<?php

/* Create a child class of mysqli */
class example_mysqli extends mysqli
{
  /* Execute a query and fetch the first field
   * Note that the result set is not cleaned up
   * and there is no error handling. Kids are so messy ;)
   */
  function query_and_fetch($query)
  {
    return array_pop($this->query($query)->fetch_row());
  }
}

$my = new example_mysqli('host', 'user', 'pass');
echo $my->query_and_fetch('SELECT NOW()');

?>

For experienced OO programmers the above sample will come as no surprise. Others may be surprised to find that all of the functionality of the mysqli class is available to the example_mysqli class. The only code we had to write was the code to explicitly state that example_mysqli is a sub-class of mysqli, and the query_and_fetch() code.

Making Exceptions

Here is a slightly more complex example that demonstrates how to add support for exceptions to a sub-class of mysqli.

Exceptions are a mechanism to allow for cleaner error handling. One of their major benefits is that they allow developers to cleanly separate the error handling code from the rest of the code flow. Additionally, exceptions in PHP come with a lot of handy information to help debug errors. For more information on exceptions in PHP, check out the article on the changes in PHP 5/Zend Engine 2.

<?php

class example_mysqli extends mysqli
{
  /* Make errors in query throw an exception */
  function query($query)
  {
    /* Call mysqli's query() method */
    $result = parent::query($query);
    if(mysqli_error($this)){
      throw new exception(mysqli_error($this), mysqli_errno($this));
    }
    return $result;
  }
}

$my = new example_mysqli('host','user', 'pass', 'world');

try {
  $result = $my->query("SELECT THEN()");
  var_dump($result->fetch_row());
} catch(Exception $exception) {
  var_dump($exception->getTrace());
}

?>

Note that we can use a mysqli object as an argument for mysqli_error(). This is because the connection, statement and result handles in the procedural interface for ext/mysqli are actually objects. Whether or not we want to do this is debatable, but the option is there and, in some cases, is even required.

Really Making an Exception

Up to this point, each of the examples has illustrated one key method for extending a class. Here is a more advanced example that demonstrates how to add more support for exceptions to a sub-class of mysqli. It is not complete enough for production code, but should provide a good basis for efforts of this kind.

First, we create custom sub-classes of Exception for handling connection errors and query errors.

<?php
/* Create custom exception classes */
class ConnectException extends Exception {}
class QueryException extends Exception {}

Next, we have a simple helper function that places strings in single quotes. It also ensures that any single quotes and backslashes within the string are escaped with a leading backslash, so as to avoid breaking the quotes. We will need this function later to help invoke the parent constructor^[1].

function add_single_quotes($arg)
{
  /* single quote and escape single quotes and backslashes */
  return "'" . addcslashes($arg, "'\\") . "'";
}

Now we get to the work of sub-classing mysqli. In this example, we override only the __construct() and query()methods. However, for code that is used in production, most of the major methods would likely be overridden in a similar fashion.

class example_mysqli extends mysqli
{
  function __construct()
  {

The next few lines of code simply take the arguments passed to the example_mysqli constructor and pass them on to the mysqli constructor:

   /* Pass all arguments passed to the constructor on to the parent's constructor */
    $args = func_get_args();
    eval("parent::__construct(" . join(',', array_map('add_single_quotes', $args)) . ");");

Note that we construct an instance of ConnectException here, instead of constructing an instance of Exception. Later on, the class of the exception determines which catch block will be used to handle the exception.

   /* Throw an error if the connection fails */
    if(mysqli_connect_error()){
      throw new ConnectException(mysqli_connect_error(), mysqli_connect_errno());
    }
  }

  function query($query)
  {
    $result = parent::query($query);
    if(mysqli_error($this)){
      throw new QueryException(mysqli_error($this), mysqli_errno($this));
    }
    return $result;
  }
}

Now we actually use the sub-class. Note that we have all of our connection and query code within the try block. If an exception is thrown, then the type of exception will be used to determine which catch block to use. In this case, the first catch block should be used, as it handles exceptions of class ConnectException. If an exception of a class that we weren't expecting is thrown, we will catch it with the last catch block.

Once we correct the errors in the instantiation of the example_mysqli object, everything should run smoothly and the catch blocks will not be executed.

try {
  $my = new example_mysqli('http://mysql.example.com','root');
  $result = $my->query("SELECT NOW()");
  var_dump($result->fetch_row());
}
catch(ConnectException $exception) {
  echo "Connection Error\n";
  var_dump($exception->getMessage());
}
catch(QueryException $exception) {
  echo "Query Error\n";
  var_dump($exception->getMessage());
}
/* Handle exceptions that we weren't expecting */
catch(Exception $exception) {
  echo "Who was that masked exception?\n";
  var_dump($exception->getMessage());
}

$result->close();
$my->close();

?>

The approach shown above demonstrates how easily significant functionality can be added to mysqli. To the end user of the sub-class, very little additional knowledge is required to use the enhanced functionality.

Summary

In this article, we demonstrated how to easily create sub-classes of the mysqli class, and provided a few useful examples. You should now be able to create useful extensions of mysqli that incorporate the functionality needed to support your applications.

[1] - The constructor is the method that is called when a new object is instantiated. When we call something like $my = new mysqli(...);, the __construct() method is the part that actually handles most of the work of creating the object.

About the Authors

Zak Greant is a professional Open Source advocate, writer and programmer. He works for MySQL AB as their Community Advocate. Zak is a maintainer of both PHP's MySQL extensions, and is also a co-author of PHP Functions Essential Reference.

Georg Richter is the author of the mysqli extension, and also maintains the mysql and ncurses extensions. He works for MySQL AB as a Senior Developer, and is a member of the Apache Software Foundation.