Counting the number of queries with PDO

One common habbit you often see when people write their database wrappers is count the number of queries they perform. PDO doesn't have support for this internally, so I've seen people struggle and do different kinds of solutions to this. Mostly the reason to this is because people don't know how to properly extends the PDO classes and how to call the parent methods with variable number of arguments, like PDO requires you to do.

In my last post I discussed calling parent methods with Variable number arguments, so let's put that into actual use. Counting the number of queries is quite simple, really. You just have to override the methods that perform the actual queries and increment the query count each time these methods are called in addition to calling the parent method.

Here is an example class for overriding the PDO class.

class lqPDOWrapper extends PDO
{
 private $queryCount;

 public function __construct ($dsn)
 {
  $args = func_get_args();
  call_user_func_array(array($this, 'parent::__construct'), $args);

  $this->queryCount = 0;
  $this->setAttribute(PDO::ATTR_STATEMENT_CLASS,
   array('lqPDOStatementWrapper', array(& $this->queryCount)));
 }

 public function exec ($statement)
 {
  $this->queryCount++;

  $args = func_get_args();
  return call_user_func_array(array($this, 'parent::exec'), $args);
 }

 public function query ($statement)
 {
  $this->queryCount++;

  $args = func_get_args();
  return call_user_func_array(array($this, 'parent::query'), $args);
 }

 public function getQueryCount ()
 {
  return $this->queryCount;
 }
}

However, the PDO class is not the only object that needs to be extended. You will also need to create new clas for the statements, because using prepared statements, the statement class can also perform queries. So, here's how to extend the PDOStatement class.

class lqPDOStatementWrapper extends PDOStatement
{
 private $queryCount;

 protected function __construct (& $queryCount)
 {
  $this->queryCount = & $queryCount;
 }

 public function execute ($input_parameters = array())
 {
  $this->queryCount++;

  $args = func_get_args();
  return call_user_func_array(array($this, 'parent::execute'), $args);
 }
}

Now, whenever you want to get the number of queries performed through the database object, just use the getQueryCount() method in the PDO object. Note that even though the arguments in these functions are not actually used directly, the argument lists are (mostly) needed to conform to the PDO class spesification.

In the class extending the PDO class, I've taken advantage of the PDO's internal built in feature to allow usage of different statement classes. This makes it simple to create your own PDOStatement wrappers. In addition you can pass the constructor arguments for the class, which I take advantage to pass reference to the database object's counter.

This solution is kind of neat, since usually you see people passing the actual database object to the statement class in order to increment the query count from the statement class. In my solution, however, I simply pass the reference to the database object's query counter to the statement class. This reference is then passed down to the statement class's query counter. Because the statement's member $queryCount is actually reference to the database object's member $queryCount, incrementing the statement class's query counter increments the database object's query count.

Here is a simple example how to use the above classes:

$db = new lqPDOWrapper('mysql:host=localhost;dbname=test', 'root', '');
$db->query('SELECT 1 + 1');
$sth = $db->prepare('SELECT 2 + 2');
$sth->execute();
$sth->execute();

echo "Performed " . $db->getQueryCount() . " queries.";

This will output "Performed 3 queries.". One query was performed through the query() method and two queries were performed by calling the execute() method twice.