Creating an object oriented MySQL abstraction class

Creating an object oriented MySQL abstraction class

In this tutorial I will show you how you can create an object oriented database abstraction class for MySQL database. Besides the basic functions this manager class can summarize total time spent with db connection and query execution and counts how many query were executed during your PHP script.

Step 1.
The first question is why we need at all such script if we have all function in PHP as default. There are more arguments:

• You can easy reuse the script in all of your development.
• You can change the database without changing all of your script. You need to edit only the abstraction class.
• You can make optimalisation and simplification more easy.
• You can centralize error handling and db access.
• You can make the debugging easier.
• You can easy implement query time statistics and query counters.

Now let's summarize what kind of function do we need:

• Creating a connection to the database
• Executing sql queries
• Get the number of selected rows if possible
• Get the number of modified rows if possible
• Get the auto increment id of the latest insert statement if possible
• Load a query result into a normal array
• Get the error message and/or id if necessary
• Get the total time spent in the database
• Get the number of the executed queries

Step 2.
Before we can start the implementation of the functions we need to collect all common used class variables which stores important information.
These are:

• A connection identifier to store the actual connection link.
• A result Set to store query results.
• An error message and error id to store error information.
• A counter to count executed queries.
• A time counter to store total query times.

So the basic DB manager class looks like this:

<?php

class DBManager{
    
    var $connection   = '';
    var $queryCounter = 0;
    var $totalTime    = 0;    
    var $errorCode    = 0;
    var $errorMsg     = '';
    var $resultSet    = '';
    
}
?>

Step 3.
Now we can implement the functions. Let's start with the most important one. Connecting to the database. We will create no stand alone connect script but the constructor of the class will be responsible to build up the database connection. So if you create a database object from this class you immediately have a valid connection as well.

So the constructor needs the following parameters:

• hostname
• username
• password
• database name

In the function first we try to connect to the database server and if it was success than we try to select the given database. In case of any error we set the error message and error id variables and return with false.

The constructor of the class is the following:

<?php
    function DBManager($host, $user, $pass, $db){
        $startTime = $this->getMicroTime();
        
        // Try to make a connection to the server
        if (!$this->connection = @mysql_connect($host,$user,$pass,true)){
            $this->errorCode = mysql_errno();
            $this->errorMsg  = mysql_error();
            return false;
        }
        
        // Now select the database
        if (!@mysql_select_db($db,$this->connection)){
            $this->errorCode = mysql_errno();
            $this->errorMsg  = mysql_error();
            @mysql_close($this->connection);
            return false;
        }

        $this->totalTime += $this->getMicroTime() - $startTime;
            
        return true;
    }
?>

Step 4.
As next step we will implement the query execution function. In this function we measure the execution time (see details later), increasing the query counter and of course executing the query itself. If an error occurs than we set the error message and error id variables and returns with false. If everything was working fine we return with the result set.

The code is the following:

<?php
    function executeQuery($sql){
        $startTime = $this->getMicroTime();

        ++$this->queryCounter;
        
        if(!$this->resultSet = @mysql_query($sql,$this->connection)){
            $this->errorCode = mysql_errno();
            $this->errorMsg  = mysql_error();
            $this->totalTime = $this->getMicroTime() - $startTime;
            return false;
        }
        
        $this->totalTime += $this->getMicroTime() - $startTime;

        return $this->resultSet;
    }
?>

If the query contains an Insert, Update or Delete command than it can be important to know how many records were affected by this query. To get it we implement a function for that as follows:

<?php
    function getAffectedRows()
    {
        return @mysql_affected_rows($this->connection);
    }    
?>

In case of a Select statement the number of selected records could be interesting. To get it we have a similar function:

<?php
    function getSelectedRows()
    {
        return @mysql_num_rows($this->resultSet);
    }
?>

In this topic we have a last important issue. In case of Insert maybe we want to know an auto increment filed value. To get this information after the record was inserted we can use the following code:

<?php
    function getInsertId(){
        return @mysql_insert_id($this->connection);
    }
?>

Step 5.
Now let's create some other useful function to make our class more usable.
In case of a select it sometimes it would be nice to get an associative array instead of the MySQL result set. So we implement a function which returns with such an array generated from the actual result set.
The code looks like this:

<?php
    function loadResult() {
        $array = array();
        while ($row = mysql_fetch_object( $this->resultSet )) {
            $array[] = $row;
        }
        mysql_free_result( $this->resultSet );

        return $array;
    }
?>

As our function don't send error messages back to the caller just sets the error variables inside the class so we need function to get this information. These are simple getter functions as follows:

<?php
    function getErrrorCode(){
        return $this->errorCode;
    }
    
    function getErrorMessage(){
        return $this->errorMsg;
    }
?>


Step 6.
As final step we need to implement a function to get query times. To do it we need a function which returns the actual time in milliseconds. With calling this function at the beginning and at the end of the affected db functions we can calculate the total db time. Besides this we implement 2 other getter functions to get the number of executed queries and the total query time in a usable format.

So our helper functions looks like this:

<?php
    function getDBTime(){
        return round($this->totalTime,6);
    }
    
    function getSqlCount(){
        return $this->queryCounter;
    }

      function getMicroTime() {
        list($usec, $sec) = explode(" ",microtime());
        return ((float)$usec + (float)$sec);
    }
?>

Download:
You can download a database abstraction class as well.