Database Connection Management | Core Web Application Development with PHP and MySQL

When we have to execute more than one query per user page request, we should avoid writing code similar to the following:

 <?php function get_user_info($in_usernid) {   $conn = @new mysqli(...);   if (!$conn === NULL)     throw new DatabaseErrorException(mysqli_connect_error());   $query = <<<EOQ SELECT * FROM Users WHERE user_id= $in_userid EOQ;   $results = $conn->query($query);   if ($results === NULL or $results === FALSE)   {     $conn->close();     throw new DatabaseErrorException($conn->error);   }   $row = $results->fetch_assoc();   $results->close();   $conn->close();   return $row; } function count_messages_for_user($in_userid) {   $conn = @new mysqli(...);   if (!$conn === NULL)     throw new DatabaseErrorException(mysqli_connect_error());   $query = <<<EOQ SELECT COUNT(user_id) FROM MESSAGES    WHERE author_id = $in_userid EOQ;   $results = $conn->query($query);   if ($results === NULL or $results === FALSE)   {     $conn->close();     throw new DatabaseErrorException($conn->error);   }   $row = $results->fetch();   $results->close();   $conn->close();   return $row[0]; } function get_message($in_msgid) {   $conn = @new mysqli(...);   if (!$conn === NULL)     throw new DatabaseErrorException(mysqli_connect_error());   $query = <<<EOQ SELECT * FROM MESSAGES    WHERE message_id = $in_msgid EOQ;   $results = $conn->query($query);   if ($results === NULL or $results === FALSE)   {     $conn->close();     throw new DatabaseErrorException($conn->error);   }   $row = $results->fetch_assoc();   $results->close();   $conn->close();   return $row; } ?>

Each of these functions creates a connection to the database server. If we called more than one of these functions from within your scripts, we would create multiple database connections, which would negatively impact performance. Even worse, if we were to have these functions call each other, we would find your scripts creating multiple simultaneous connections!

A Better Approach

A slightly better approach to managing connections is to create a connection once and pass it around as a parameter to the various functions called, as follows:

 <?php function get_user_info($in_usernid, $in_conn) {   $query = <<<EOQ SELECT * FROM Users WHERE user_id= $in_userid EOQ;   $results = $in_conn->query($query);   if ($results === NULL or $results === FALSE)   {     throw new DatabaseErrorException($in_conn->error);   }   $row = $results->fetch_assoc();   $results->close();   return $row; } function count_messages_for_user($in_userid, $in_conn) {   $query = <<<EOQ SELECT COUNT(user_id) FROM MESSAGES    WHERE author_id = $in_userid EOQ;   $results = $in_conn->query($query);   if ($results === NULL or $results === FALSE)   {     throw new DatabaseErrorException($in_conn->error);   }   $row = $results->fetch();   $results->close();   return $row[0]; } $conn = @new mysqli(...); if ($conn === NULL)   throw new DatabaseErrorException(mysqli_connect_error()); $ui = get_user_info($_SESSION['user_id'], $conn); $cmsgs = count_messages_for_user($_SESSION['user_id'], $conn); // etc... $conn->close(); // etc. ... ?>

This newer system lets us create only one database connection and then pass it around, which results in less overhead in managing connections. It is, however, slightly cumbersome.

The Best Approach

The solution we will use in our sample web applications to manage database connections will be to create a class called DBManager, which maintains a static class variable called $s_conn. This class will create one connection when somebody wants to make a connection, and will then just return that same connection object the next time somebody asks for one. The complete code for our class looks like this:

 <?php /**  *=-----------------------------------------------------------=  * dbmanager.inc  *=-----------------------------------------------------------=  * Author: Marc Wandschneider  *  * This class manages database connections for us.  For the  * mysqli case, we just create one connection per new instance  * of the PHP session.  */ require_once('posterstore/dbconninfo.inc'); /**  *=-----------------------------------------------------------=  * DBManager  *=-----------------------------------------------------------=  */ class DBManager {   /**    * This is the connection we're using for this instance.  It    * will automagically be closed when our instance closes.    */   private static $s_conn;   /**    *=---------------------------------------------------------=    * getConnection    *=---------------------------------------------------------=    * Static method to get a connection to the database server    * with which we are interacting.    *    * Returns:    *    mysqli object representing the connection.  Throws on    *    failure.    */   public static function getConnection()   {     if (DBManager::$s_conn === NULL)     {       /**        * Create a new mysqli object, throw on failure.        */       $conn = @new mysqli(DB_HOST, DB_USER, DB_PASS, DB_DBASE);       if (mysqli_connect_errno() !== 0)       {         $msg = mysqli_connect_error();         throw new DatabaseErrorException($msg);       }       /**        * Make sure the connection is set up for utf8        * communications.        */       @$conn->query('SET NAMES \'utf8\'');       DBManager::$s_conn = $conn;     }     return DBManager::$s_conn;   } } ?>

This new DBManager class will enable us to rewrite each of our functions to ask for a connection, thus not burdening their parameter lists with connections, but it will only create a new connection the first time. Our functions will now look similar to the following:

 function count_messages_for_user($in_userid) {   $conn = DBManager::getConnection();   $query = <<<EOQ SELECT COUNT(user_id) FROM MESSAGES    WHERE author_id = $in_userid EOQ;   $results = $conn->query($query);   if ($results === NULL or $results === FALSE)   {     $conn->close();     throw new DatabaseErrorException($conn->error);   }   $row = $results->fetch();   $results->close();   return $row[0]; }

The only unusual thing about this system is that we do not appear to ever call the close method on the mysqli object. We could either add a static closeConnection method to the DBManager class that we called from the end of our scripts, or we could do what we did earlier and rely on PHP to close the connection when our script execution ends. Both have reasonable arguments for and against their usage. We made our choice out of convenience.

A New and Improved String Escaping Function

In addition to the connection management we provide in the DBManager class, we have written a new function (called mega_escape_string) to help us prevent SQL injection and cross-site scripting (XSS) attacks. This method takes three parametersthe string to escape, an optional second parameter indicating whether any markup element characters such as < or > should be escaped, and an optional third parameter indicating whether percent symbols (%) should be stripped out. Both of these optional parameters default to FALSE.

 function mega_escape_string (   $in_string,    $in_markup = FALSE   $in_removePct = FALSE ) {   $str = ereg_replace('(\')', '\\\1', $in_string);   if ($in_removePct)     $str = ereg_replace('(%)', '\\\1', $str);   if ($in_markup == TRUE)   {     $str = htmlspecialchars($str, ENT_NOQUOTES,         'UTF-8');   }   return $str; }