Efficient use of Statements

PHP Logo

PHP 5 and MySQL 4.1 bring prepared statements to the masses. While various database extensions supported prepared statements already, they were not quite as popular as MySQL and remained obscur. PHP 5 arrives with a new MySQLi extension for MySQL 4.1 and above with support for prepared statements. In addition, PDO offers a unified API for databases and has PDOStatement objects (created by PDO->prepare()).

Using statements has the advantage of reducing the amount of data transfered to the server by sending the query once and only sending data afterwards. In the process, any work required to escape the values is usually handled by the bind process (which is a huge time saver). Of course, the basic rule is to reduce the amount of queries to the database as much as possible, but in the real world, maintenance tasks often require to perform a lot of processing and update queries are not very flexible when multiple rows need to be updated at once.

In perfect situations, it’s possible to prepare the statement and have a loop executing it for various data. I came up with design problems quite a few times where either too much processing had to be done to preserve readability or where the flow simply was not regular enough (recursive calls?). The base idea was to isolate the query and the call to it in a method to make it more significant when called from various places (The examples use PDO and considers it uses Exception error handling):

 function update( $id, $name ) {     $stmt = $this->dbh->prepare( "UPDATE foo_table SET some_name = :name WHERE id = :id" );     $stmt->bindParam( ':name', $name );     $stmt->bindParam( ':id', $id );     $stmt->execute(); } 

Calling the function with the required data feels just fine. While being very clean and readable, it’s useless since the statement is recreated every single call to the function. One solution would have been to have the statement as a member variable. The constructor is the only place where it really makes sense to initialize the statement, but I don’t really like the idea of creating statements I might not use. What if there are multiple instances of the object anyway?

Static members could be an option, or even a Singleton object preparing statements on demand! I think both solutions actually fall out of scope. A member variable, or worst an external object, should not be required to solve a problem local to a function call. After all, each statement is only used in a function. After a few seconds of reflection, I remembered old theory with static local variables. It ended up working just fine:

 function update( $id, $name ) {     static $stmt = null;     if( is_null( $stmt ) )         $stmt = $this->dbh->prepare( "UPDATE foo_table SET some_name = :name WHERE id = :id" );     $stmt->bindParam( ':name', $name );     $stmt->bindParam( ':id', $id );     $stmt->execute(); } 

The only overhead is the condition to verify if the statement is already prepared and everything is kept locally. No global or class scope pollution! I would have expected the initialization to be possible on a single line (static $stmt = $this->dbh->prepare(...)), but there was something it didn’t like about it.

Leave a Reply

Your email address will not be published. Required fields are marked *