Location: PHPKode > scripts > PHP mySQL Database Wrapper Class > php-mysql-database-wrapper-class/class.database.php
<?php

/**
 *  mySQL database wrapper
 *
 *  This work is licensed under the Creative Commons Attribution-NonCommercial-NoDerivs 2.5 License.
 *  To view a copy of this license, visit {@link http://creativecommons.org/licenses/by-nc-nd/2.5/} or send a letter to
 *  Creative Commons, 543 Howard Street, 5th Floor, San Francisco, California, 94105, USA.
 *
 *  For more resources visit {@link http://stefangabos.blogspot.com}
 *
 *  @author     Stefan Gabos <hide@address.com>
 *  @version    1.0.5 (last revision: September 09, 2006)
 *  @copyright  (c) 2006 Stefan Gabos
 *  @package    database
 *  @example    example.php
 */

error_reporting(E_ALL);

class database
{

    /**
     *  The time (in seconds) after which a query will be considered to be running for too long
     *
     *  default is 30
     *
     *  @var    integer
     */
    var $maxQueryTime;
    
    /**
     *  The email address to which a notification to be sent when a query's execution time exceeds {@link maxQueryTime}
     *
     *  default is "hide@address.com"
     *
     *  @var    string
     */
    var $maxQueryTimeExceeded_notificationAddress;
    
    /**
     *  The domain name to use in the subject of the mails sent when a query's execution time exceeds {@link maxQueryTime}
     *
     *  default is "localhost.com"
     *
     *  @var    string
     */
    var $maxQueryTimeExceeded_notifierDomain;
    
    /**
     *  After an INSERT, UPDATE, DELETE query, by reading this property you get the number of affected rows
     *
     *  <b>This is a read-only property!</b>
     *
     *  @var    integer
     */
    var $affectedRows;
    
    /**
     *  After a SELECT query, by reading this property you get the number of records returned by it
     *
     *  <b>This is a read-only property!</b>
     *
     *  @var    integer
     */
    var $returnedRows;

    /**
     *  By setting this to TRUE, calling the show_debug_information() method will provide debug information
     *
     *  default is FALSE
     *
     *  @var    boolean
     */
    var $debug;
    
    /**
     *  By setting this property to TRUE, the execution of the script will be halted upon an unsuccessful query
     *
     *  default is TRUE
     *
     *  @var    boolean
     */
    var $haltOnErrors;
    
    /**
     *  By setting this to TRUE, a minimized version of the debug bar will be shown
     *
     *  default is FALSE
     *
     *  @var    boolean
     */
    var $minimizeDebugger;

    /**
     *  After a SELECT query, by reading this property you get the number of records that would've been returned if there was no LIMIT
     *
     *  <b>This is a read-only property!</b>
     *
     *  @var    integer
     */
    var $foundRows;
    
    /**
     *  Default language file to use
     *
     *  default is "english.php"
     *
     *  @var    string
     */
    var $languageFile;
    
    /**
     *  Default template folder to use
     *
     *  Note that only the folder of the template you wish to use needs to be specified. Inside the folder
     *  you <b>must</b> have the <b>debug.xtpl</b> file which will be automatically used
     *
     *  default is "default"
     *
     *  @var    string
     */
    var $template;
    
    /**
     *  Holds debug information
     *
     *  default is array()
     *
     *  @access private
     */
    var $debugInfo;
    
    /**
     *  mySQL link identifier
     *
     *  default is FALSE
     *
     *  @access private
     */
    var $link;
    
    /**
     *  mySQL selected database
     *
     *  default is FALSE
     *
     *  @access private
     */
    var $database;
    
    /**
     *  Constructor of the class
     *
     *  @access private
     */
    function database()
    {
    
        // Sets default values of the class' properties
        // We need to do it this way for the variables to have default values PHP 4
        // public properties
        $this->maxQueryTime = 30;
        $this->maxQueryTimeExceeded_notificationAddress = "hide@address.com";
        $this->maxQueryTimeExceeded_notifierDomain = "localhost.com";
        $this->debug = false;
        $this->haltOnErrors = true;
        $this->minimizeDebugger = false;
        $this->languageFile = "english.php";
        $this->template = "default";
        // private properties
        $this->database = false;
        $this->link = false;
        $this->debugInfo = array();

        // get path of class and replace (on a windows machine) \ with /
        // this path is to be used for all includes as it is an absolute path
        $this->classPath = preg_replace("/\\\/", "/", dirname(__FILE__));
        
        // remove $_SERVER["DOCUMENT_ROOT"] from the path
        // this path is to be used from within HTML as it is a relative path
        $this->strippedPath = preg_replace("/".preg_replace("/\//", "\/", $_SERVER["DOCUMENT_ROOT"])."/i", "", $this->classPath);
        
        // include the language file
        require_once $this->classPath."/languages/".$this->languageFile;
        
    }
    
    /**
     *  Connects to a mySQL database
     *
     *  Example:
     *
     *  <code>
     *      /**
     *          notice that we're doing no error checking as we will have
     *          any errors show up in the debug window
     *          so don't forget to have at the end of your code a call to
     *          show_debug_info() method
     *      {@*}
     *      $db->connect("localhost", "root", "", "test");
     *  </code>
     *
     *  @param  string  $mySQLHost      the address of the mySQL server to connect to (i.e. localhost)
     *
     *  @param  string  $mySQLUser      the username used for authentication when connecting to the mySQL server
     *
     *  @param  string  $mySQLPassword  the password used for authentication when connecting to the mySQL server
     *
     *  @param  string  $mySQLDatabase  the database to be selected after connection is estabilished
     *
     *  @return boolean returns TRUE on success and FALSE upon failure
     */
    function connect($mySQLHost, $mySQLUser, $mySQLPassword, $mySQLDatabase)
    {
    
        // tries to conntect to the mysql database using the given parameters
        $this->link = @mysql_connect($mySQLHost, $mySQLUser, $mySQLPassword);
        
        // if connection could not be estabilished
        if (!$this->link) {
        
            // if debug is on
            if ($this->debug) {
            
                // save debug information
                $this->saveDebugInfo(
                    "messages",
                    array(
                        "message"=>"strLang_couldNotConnectToDatabase",
                        "method"=>"connect()"
                    ),
                    true
                );
                
            }
            
            // return false
            return false;
            
        // if connection could be estabilished
        } else {
        
            // select the database
            $this->database = @mysql_select_db($mySQLDatabase, $this->link);
            
            // if database could not be selected
            if (!$this->database) {
            
                // if debug is on
                if ($this->debug) {
                
                    // save debug information
                    $this->saveDebugInfo(
                        "message",
                        array(
                            "message"=>"strLang_couldNotSelectDatabase",
                            "method"=>"connect()",
                        ),
                        true
                    );
                    
                }
                
                // return false
                return false;
            }
            
        }
        
        // return true if there is no error
        return true;
        
    }
    
    /**
     *  Escapes a string's special characters and prepares it for insertion in a database.
     *  Works even if magic_quotes is ON
     *
     *  <b>Use this for ALL the inserted data to prevent mySQL injection!</b>
     *
     *  Example:
     *
     *  <code>
     *      print_r($db->escape_string("John O'Bryan"));
     *  </code>
     *
     *  @param  string  $string     string to escape
     *
     *  @return string  escaped string
     */
    function escape_string($string)
    {
    
        // checks is there is an active connection
        if ($this->_connected()) {

            // get the state of "magic quotes"
            // and if "magic quotes" are on
            if (get_magic_quotes_gpc()) {

                // strip slashes
                $returnValue = stripslashes($string);

            }

            // escape the string
            $returnValue = mysql_real_escape_string($string);

            // return escaped string
            return $returnValue;
            
        } else {
        
            // we don't have to report any error as _connected() method already did
            return false;
        
        }
        
    }
    
    /**
     *  Alias for the mysql_query function.
     *
     *  After a SELECT query you can get the number of records that would've been returned if there was no
     *  LIMIT by reading the {@link foundRows} property and the actual number of returned records by reading
     *  the {@link returnedRows} property
     *
     *  After an UPDATE, INSERT or DELETE query you can get the number of affected rows
     *  by reading the {@link affectedRows} property.
     *
     *  Example:
     *
     *  <code>
     *      /**
     *          notice that we're doing no error checking as we will have
     *          any errors show up in the debug window
     *          so don't forget to have at the end of your code a call to
     *          show_debug_info() method
     *      {@*}
     *
     *      $result = $db->query("SELECT * FROM table WHERE 1", "", __FILE__, __LINE__);
     *
     *      $result = $db->query("SELECT * FROM table WHERE name = ?", array("Mikey Mouse"), __FILE__, __LINE__);
     *
     *  </code>
     *
     *  @param  string  $query          query to execute
     *
     *  @param  array   $replacements   (optional) an array with as many items as "?" symbols in $query. each item will be
     *                                  automatically {@link escape_string}-ed and will replace the corresponding "?"
     *
     *  @param  mixed   $callerFile     (optional) put here the php magic constant __FILE__ for the debugger to know
     *                                  from what file was the call made
     *
     *  @param  mixed   $callerLine     (optional) put here the php magic constant __LINE__ for the debugger to know
     *                                  from what line was the call made
     *
     *  @param  mixed   $callerMethod   for internal use
     *
     *  @return mixed    returns a resource on success and FALSE on error
     */
    function query($query, $replacements = "", $callerFile = "", $callerLine = "", $callerMethod = "")
    {
    
        // checks is there is an active connection
        if ($this->_connected()) {
        
            unset($this->affectedRows);

            // if $replacements is specified but it's not an array
            if ($replacements != "" && !is_array($replacements)) {

                // issue an error message
                if ($this->debug) {
                
                    // save debug information
                    $this->saveDebugInfo(
                        "queries",
                        array(
                            "query" => $query,
                            "file" => $callerFile,
                            "line" => $callerLine,
                            "method" => $callerMethod,
                            "error" => $this->languageStrings["strLang_warningReplacementsNotArray"]
                        ),
                        true
                    );
                    
                }

                return false;

            // if $replacements is specified and is an array
            } elseif ($replacements != "" && is_array($replacements)) {

                // found how many items to replace are there in the query string
                preg_match_all("/\?/", $query, $matches, PREG_OFFSET_CAPTURE);

                // if the number of items to replace is different than the number of items specified in $replacements
                if (count($matches[0]) != count($replacements)) {

                    // issue an error message
                    if ($this->debug) {
                    
                        // save debug information
                        $this->saveDebugInfo(
                            "queries",
                            array(
                                "query" => $query,
                                "file" => $callerFile,
                                "line" => $callerLine,
                                "method" => $callerMethod,
                                "error" => $this->languageStrings["strLang_warningReplacementsWrongNumber"]
                            ),
                            true
                        );
                        
                    }
                    
                    return false;

                // if the number of items to replace is the same as the number of items specified in $replacements
                } else {

                    // make preparations for the replacement
                    $pattern = array();
                    foreach ($matches[0] as $match) {
                        $pattern[] = "/\\".$match[0]."/";
                    }

                    // mysql_real_escape_string the items in replacements
                    foreach ($replacements as $key=>$replacement) {
                        $replacements[$key] = "'".$this->escape_string($replacement)."'";
                    }

                    // perform the actual replacement
                    $query = preg_replace($pattern, $replacements, $query, 1);

                }

            }

            // if we have a SELECT query and the SQL_CALC_FOUND_ROWS string is not in it
            // (we do this trick to get the numbers of records that would've been returned if there was no LIMIT applied)
            if (strtolower(substr(ltrim($query), 0, 6)) == "select" && strpos($query, "SQL_CALC_FOUND_ROWS") === false) {
            
                // add the 'SQL_CALC_FOUND_ROWS' parameter to the query
                $query = preg_replace("/SELECT/i", "SELECT SQL_CALC_FOUND_ROWS", $query, 1);
                
            }
            
            // starts a timer
            list($usec, $sec) = explode(" ", microtime());
            $startTime = (float)$usec + (float)$sec;

            // executes the query
            $this->lastResult = @mysql_query($query);

            // stops timer
            list($usec, $sec) = explode(" ", microtime());
            $endTime = (float)$usec + (float)$sec;
            
            // if execution time exceeds maxQueryTime
            if ($endTime - $startTime > $this->maxQueryTime) {
            
                // then send a notification mail
                @mail(
                    $this->maxQueryTimeExceeded_notificationAddress,
                    sprintf($this->languageStrings["strLang_erroneousQueryEMailSubject"], $this->maxQueryTimeExceeded_notifierDomain),
                    sprintf($this->languageStrings["strLang_erroneousQueryEMailContent"], $this->maxQueryTime, $endTime - $startTime, $query),
                    "From: ".$this->maxQueryTimeExceeded_notifierDomain
                );
                
            }

            // if debug is on
            if ($this->debug) {

                $warning = "";

                // if there were queries run already
                if (isset($this->debugInfo["queries"])) {

                    // iterate through the run queries
                    // to find out if this query was already run
                    $counter = 1;
                    $keys = array();
                    foreach ($this->debugInfo["queries"] as $key=>$queryData) {
                        // if this query was run before
                        if (trim($queryData["query"]) == trim($query)) {
                            // increase the counter
                            $counter++;
                            // save the pointer to the query in an array
                            $keys[] = $key;
                        }
                    }

                    // if the query was run before
                    if ($counter > 0) {

                        // issue a warning for all the queries that were found to be the same as the current one
                        foreach ($keys as $key) {
                            $warning = sprintf($this->languageStrings["strLang_optimizationNeeded"], $counter);
                            $this->debugInfo["queries"][$key]["warning"] .= $warning;
                        }

                    }

                }

            }

            // if the query was successfully executed
            if ($this->lastResult) {

                // flag determining that the last query was a SELECT query or an action query
                $selectQuery = true;
                
                // the returnedRows property holds the number of records returned by a SELECT query
                $this->returnedRows = @mysql_num_rows($this->lastResult);

                // if returnedRows property is FALSE means the query was not a SELECT query
                // so we'll get the rows affected by the action query
                if (!$this->returnedRows) {
                    // the affectedRows property holds the number of affected rows by action queries (DELETE, INSERT, UPDATE)
                    $this->affectedRows = @mysql_affected_rows();
                }

                // get the number of records that would've been returned if there was no LIMIT
                $foundRows = mysql_fetch_assoc(mysql_query("SELECT FOUND_ROWS()"));
                $this->foundRows = $foundRows["FOUND_ROWS()"];

                if ($this->debug) {

                    // save debug information
                    $this->saveDebugInfo(
                        "queries",
                        array(
                            "query" => $query,
                            "returnedRows" => $this->returnedRows,
                            "affectedRows" => (isset($this->affectedRows) ? $this->affectedRows : ""),
                            "file" => $callerFile,
                            "line" => $callerLine,
                            "method" => $callerMethod,
                            "executionTime" => $endTime - $startTime,
                            "warning" => $warning,
                        )
                    );
                    
                }
                
                // return result resource
                return $this->lastResult;
                
            }
            
            // in case of error
            if ($this->debug) {
            
                // save debug information
                $this->saveDebugInfo(
                    "queries",
                    array(
                        "query" => $query,
                        "file" => $callerFile,
                        "line" => $callerLine,
                        "method" => $callerMethod,
                        "error" => mysql_error()
                    )
                );
                
            }

        }
        
        // we don't have to report any error as _connected() method already did
        return false;
        
    }
    
    /**
     *  Alias for the mysql_fetch_assoc function.
     *
     *  Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead
     *
     *  Example:
     *
     *  <code>
     *      $result = $db->query("SELECT * FROM table WHERE 1", "", __FILE__, __LINE__);
     *      while ($row = $db->fetch_assoc($result)) {
     *          // do stuff...
     *      }
     *  </code>
     *
     *  @param  resource    $resource   (optional) resource to fetch. if not specified, the last link opened by the
     *                                  {@link query} is assumed
     *
     *  @return mixed   returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows
     */
    function fetch_assoc($resource = "")
    {
    
        // checks is there is an active connection
        if ($this->_connected()) {

            // if no resource was specified, and there was a previous call to the "query" method
            if ($resource == "" && isset($this->lastResult)) {
                // assign the last resource
                $resource = $this->lastResult;
            }

            // check if given resource is valid
            if (is_resource($resource)) {

                // return the fetched row
                return mysql_fetch_assoc($resource);

            // if not a valid resource
            } else {

                // save debug information
                $this->saveDebugInfo(
                    "messages",
                    array(
                        "message"=>"strLang_notAValidResource",
                        "method"=>"fetch_assoc()"
                    ),
                    true
                );
                
                // and return FALSE
                return false;

            }
            
        } else {
        
            // we don't have to report any error as _connected() method already did
            return false;
        
        }
        
    }

    /**
     *  Alias for the mysql_insert_id() function.
     *
     *  Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query
     *
     *  @return mixed   The ID generated for an AUTO_INCREMENT column by the previous INSERT query on success,
     *                  0 if the previous query does not generate an AUTO_INCREMENT value, or FALSE if there was
     *                  no mySQL connection
     */
    function insert_id()
    {

        // checks is there is an active connection
        if ($this->_connected()) {
        
            // if there was a previous call to the "query" method
            if (isset($this->lastResult)) {

                // return the fetched row
                return mysql_insert_id($this->link);

            // if no previous calls to the "query" method
            } else {

                // save debug information
                $this->saveDebugInfo(
                    "messages",
                    array(
                        "message"=>"strLang_notAValidResource",
                        "method"=>"insert_id()"
                    ),
                    true
                );

                // and return FALSE
                return false;

            }

        // if no connection found
        } else {

            // we don't have to report any error as _connected() method already did
            return false;

        }



    }
    
    /**
     *  Finds field/fields from ONE row of a table based on a standard mySQL WHERE condition
     *
     *  Example:
     *
     *  <code>
     *      /**
     *          notice that we're doing no error checking as we will have
     *          any errors show up in the debug window
     *          so don't forget to have at the end of your code a call to
     *          show_debug_info() method
     *      {@*}
     *      $foundData = $db->dlookup("name, surname, age", "people", "countryid = 1");
     *  </code>
     *
     *  @param  string  $field          one or more fields to return in the result. if only one field is specified, the
     *                                  returned result will be the specific field's value. if more fields are specified
     *                                  the returned result will be an associative array.
     *
     *                                  you can also use the "*" sign to return all the fields from a row
     *
     *  @param  string  $table          name of the table in which to look for results
     *
     *  @param  string  $condition      (optional) a standard mySQL WHERE condition
     *
     *  @param  mixed   $callerFile     (optional) put here the php magic constant __FILE__ for the debugger to know
     *                                  from what file was the call made
     *
     *  @param  mixed   $callerLine     (optional) put here the php magic constant __LINE__ for the debugger to know
     *                                  from what line was the call made
     *
     *  @return mixed   field/fields found
     */
    function dlookup($field, $table, $condition = "", $callerFile = "", $callerLine = "")
    {
    
        // executes query
        $this->lastResult = $this->query("
            SELECT ".$field."
            FROM ".$table.
            ($condition!="" ? " WHERE ".$condition : ""),
        "", $callerFile, $callerLine, "dlookup()");
        
        // if query was executed successfully and one or more results were found
        if ($this->lastResult && $this->foundRows > 0) {
        
            // take *only* the first row
            $row = $this->fetch_assoc();
            
            // if more fields were specified get them in an array
            $fields_list = explode(",", $field);
            
            // if all cells were requested
            if (trim($field) == "*") {
            
                // return the whole row
                return $row;
                
            // if more than one cell was requested
            } elseif (count($fields_list)>1) {
            
                $retData = array();
                
                // iterate through the requested cells and take each one out from the row and put it in the return result
                foreach ($fields_list as $fields) {
                    $retData[$fields] = $row[trim($fields)];
                }
                
                // return requested cells
                return $retData;
                
            // if a specific cell was requested
            } else {
            
                // return the field's value
                return $row[$field];
                
            }
            
        // if error or no results were found
        } else {
        
            // return empty string
            return "";
            
        }
        
    }
    
    /**
     *  Looks up the maximum value in a field of a table based on a standard mySQL WHERE condition
     *
     *  Example:
     *
     *  <code>
     *      /**
     *          notice that we're doing no error checking as we will have
     *          any errors show up in the debug window
     *          so don't forget to have at the end of your code a call to
     *          show_debug_info() method
     *      {@*}
     *      $maxAge = $db->dmax("age", "people", "countryid = 1");
     *  </code>
     *
     *  @param  string  $field      name of the field in which to look
     *
     *  @param  string  $table      name of the table in which to look for results
     *
     *  @param  string  $condition  (optional) a standard mySQL WHERE condition
     *
     *  @param  mixed   $callerFile     (optional) put here the php magic constant __FILE__ for the debugger to know
     *                                  from what file was the call made
     *
     *  @param  mixed   $callerLine     (optional) put here the php magic constant __LINE__ for the debugger to know
     *                                  from what line was the call made
     *
     *  @return mixed   the maximum value found in the field
     */
    function dmax($field, $table, $condition = "", $callerFile = "", $callerLine = "")
    {
    
        // executes query
        $this->lastResult = $this->query("
            SELECT MAX(".$field.") AS maxval
            FROM ".$table.
            ($condition!="" ? " WHERE ".$condition : ""),
        "", $callerFile, $callerLine, "dmax()");
        
        // if query was executed successfully and one or more results were found
        if ($this->lastResult && $this->foundRows > 0) {
        
            // get all the data in the row
            $row = $this->fetch_assoc();
            // return the result
            return $row["maxval"];
            
        // if error or no results were found
        } else {
        
            return "";
            
        }
        
    }
    
    /**
     *  Counts the values in a field of a table based on a standard mySQL WHERE condition
     *
     *  Example:
     *
     *  <code>
     *      /**
     *          notice that we're doing no error checking as we will have
     *          any errors show up in the debug window
     *          so don't forget to have at the end of your code a call to
     *          show_debug_info() method
     *      {@*}
     *      $countName = $db->dcount("name", "people", "gender = male");
     *  </code>
     *
     *  @param  string  $field      name of the field in which to look
     *
     *  @param  string  $table      name of the table in which to look for results
     *
     *  @param  string  $condition  (optional) a standard mySQL WHERE condition
     *
     *  @param  mixed   $callerFile     (optional) put here the php magic constant __FILE__ for the debugger to know
     *                                  from what file was the call made
     *
     *  @param  mixed   $callerLine     (optional) put here the php magic constant __LINE__ for the debugger to know
     *                                  from what line was the call made
     *
     *  @return mixed   the number of values found
     */
    function dcount($field, $table, $condition = "", $callerFile = "", $callerLine = "")
    {
    
        // executes query
        $this->lastResult = $this->query("
            SELECT COUNT(".$field.") as countval
            FROM ".$table.
            ($condition!="" ? " WHERE ".$condition : ""),
            "", $callerFile, $callerLine, "dcount()"
        );
        
        // if query was executed successfully and one or more results were found
        if ($this->lastResult && $this->foundRows > 0) {
        
            // get all the data in the row
            $row = $this->fetch_assoc();
            // return the result
            return $row["countval"];
            
        // if error or no results were found
        } else {
        
            return "";
            
        }
        
    }
    
    /**
     *  Sums the values in a field of a table based on a standard mySQL WHERE condition
     *
     *  Example:
     *
     *  <code>
     *      /**
     *          notice that we're doing no error checking as we will have
     *          any errors show up in the debug window
     *          so don't forget to have at the end of your code a call to
     *          show_debug_info() method
     *      {@*}
     *      $sumSalary = $db->dsum("salary", "people", "countryid = 1");
     *  </code>
     *
     *  @param  string  $field      name of the field in which to look
     *
     *  @param  string  $table      name of the table in which to look for results
     *
     *  @param  string  $condition  (optional) a standard mySQL WHERE condition
     *
     *  @param  mixed   $callerFile     (optional) put here the php magic constant __FILE__ for the debugger to know
     *                                  from what file was the call made
     *
     *  @param  mixed   $callerLine     (optional) put here the php magic constant __LINE__ for the debugger to know
     *                                  from what line was the call made
     *
     *  @return mixed   the sum of the values
     */
    function dsum($field, $table, $condition = "", $callerFile = "", $callerLine = "")
    {
    
        // executes query
        $this->lastResult = $this->query("
            SELECT SUM(".$field.") as sumval
            FROM ".$table.
            ($condition!="" ? " WHERE ".$condition : ""),
            "", $callerFile, $callerLine, "dsum()"
        );
        
        // if query was executed successfully and one or more results were found
        if ($this->lastResult && $this->foundRows > 0) {
        
            // get all the data in the row
            $row = $this->fetch_assoc();
            // return the result
            return $row["sumval"];
            
        // if error or no results were found
        } else {
        
            return "";
            
        }
        
    }
    
    /**
     *  Parses a mySQL dump file
     *  (this script was posted on php.net so there are some people who contributed to it:
     *  hide@address.com and hide@address.com - thanks guys!)
     *
     *  @param  string  $url    path to the file to be parsed
     *
     *  @param  mixed   $callerFile     (optional) put here the php magic constant __FILE__ for the debugger to know
     *                                  from what file was the call made
     *
     *  @param  mixed   $callerLine     (optional) put here the php magic constant __LINE__ for the debugger to know
     *                                  from what line was the call made
     *
     *  @return void
     */
    function parse_mysql_dump_file($url, $callerFile = "", $callerLine = "")
    {
    
        // checks is there is an active connection
        if ($this->_connected()) {

            // uncomment the next line if you get an error about the memory limit
            //ini_set("memory_limit","20M");

            // read file into an array
            $file_content = @file($url);

            // if file was successfully opened
            if ($file_content) {

                $query = "";

                // iterates through every line of the file
                foreach ($file_content as $sql_line) {

                    // trims whitespace from both begining and end of line
                    $tsql = trim($sql_line);

                    // if line content is not empty and is the line does not represent a comment
                    if ($tsql != "" && substr($tsql, 0, 2) != "--" && substr($tsql, 0, 1) != "#") {

                        // add to query string
                        $query .= $sql_line;

                        // if line ends with ";"
                        if (preg_match("/;\s*\$/", $sql_line)) {

                            // executes query
                            $this->lastResult = $this->query(
                                $query,
                                "", __FILE__, __LINE__, "parse_mysql_dump_file()"
                            );

                            // empties the query string
                            $query = "";

                        }

                    }

                }

            } else {

                // save debug info
                $this->saveDebugInfo(
                    "messages",
                    array(
                        "message"=>"strLang_fileCouldNotBeOpened",
                        "method"=>"parse_mysql_dump_file()"
                    ),
                    true
                );

            }
            
        } else {
        
            // we don't have to report any error as _connected() method already did
            return false;
            
        }
        
    }

    /**
     *  Saves the state of a variable at a given moment and displays it in the debug window
     *
     *  @param  mixed   &$variable      variable to watch
     *
     *  @param  string  $variableName   (optional) name to display along the variable's value. useful when you are watching
     *                                  lots of variables to know which one is which
     *
     *  @return void
     */
    function watch($variable, $variableName = "")
    {
        // save debug information
        $this->debugInfo["watch"][] = array(
            "variable" => $variable,
            "name" => $variableName
        );
    }

    /**
     *  Outputs debug information
     *
     *  @param  boolean     $toLogFile  (optional) if set to TRUE, the output will be logged to the "/logs" foldder insted
     *                                  of being displayed on the screen.
     *
     *                                  note that the logging is not incremental - only the last one is logged
     *
     *                                  no errors whatsoever are shown in the process of file logging so make sure the
     *                                  folder is writable and the file can be created
     *
     *  @param  string      $ipAddress  IP address for which to save log meaning that the log will be saved only if the
     *                                  specified IP address accesses the page
     *
     *                                  by default any call is logged
     *
     *  @return void
     */
    function show_debug_info($toLogFile = false, $ipAddress = "")
    {

        // if debug is enabled
        if ($this->debug) {
        
            // includes, if not included, the xtemplate class
            if (!class_exists("XTemplate")) {
                require_once $this->classPath."/includes/class.xtemplate.php";
            }
            
            $xtpl = new XTemplate($this->classPath."/templates/".$this->template."/debug.xtpl");
            
            $xtpl->assign("templatePath", $this->strippedPath."/templates/".$this->template."/");
            
            // assign all the values from the language file
            $xtpl->assign("languageStrings", $this->languageStrings);
            
            $totalExecutionTime = 0;
            $successfulQueries = 0;
            $unsuccessfulQueries = 0;
            
            // if there is data about queries
            if (isset($this->debugInfo["queries"])) {
            
                // total number of the queries
                $xtpl->assign("totalQueries", count($this->debugInfo["queries"]));
                
                // iterate through all the query realted data
                foreach ($this->debugInfo["queries"] as $debugInfo) {

                    // marks symbols in mySQL query
                    $symbols = array(
                        "/(\=)/",
                        "/(\>)/",
                        "/(\<)/",
                        "/(\*)/",
                        "/(\+)/",
                        "/(\-)/",
                        "/(\,)/",
                        "/(\.)/",
                        "/(\()/",
                        "/(\))/"
                    );
                    $replacement = htmlentities("<span class=\"databasedebugmySQLsymbols\">\$1</span>");
                    $debugInfo["query"] = preg_replace($symbols, $replacement, $debugInfo["query"]);

                    // marks strings in mySQL queries
                    $strings = array(
                        "/\'([^\']*)\'|\"([^\']*)\"/",
                    );
                    $replacement = htmlentities("'<span class=\"databasedebugmySQLstrings\">\$1</span>'");
                    $debugInfo["query"] = preg_replace($strings, $replacement, $debugInfo["query"]);

                    // marks operations in mySQL queries
                    $operations = array(
                        "/(\bDELETE\b)/i",
                        "/(\bFROM\b)/i",
                        "/(\bGROUP BY\b)/i",
                        "/(\bHAVING\b)/i",
                        "/(\bINNER JOIN\b)/i",
                        "/(\bINSERT INTO\b)/i",
                        "/(\bLEFT JOIN\b)/i",
                        "/(\bLIMIT\b)/i",
                        "/(\bORDER BY\b)/i",
                        "/(\bREPLACE INTO\b)/i",
                        "/(\bRIGHT JOIN\b)/i",
                        "/(\bSELECT\b)/i",
                        "/(\bSET\b)/i",
                        "/(\bUPDATE\b)/i",
                        "/(\bUNION\b)/i",
                        "/(\bVALUES\b)/i",
                        "/(\bWHERE\b)/i"
                    );
                    $replacement = htmlentities("<br /><span class=\"databasedebugmySQLoperations\">\$1</span><br /><span style='padding-left:20px'></span>");
                    $debugInfo["query"] = preg_replace($operations, $replacement, $debugInfo["query"]);

                    // marks other special entities in mySQL queries
                    $special = array(
                        "/(\bAND\b)/i",
                        "/(\bOR\b)/i",
                        "/(\bDESC\b)/i",
                        "/(\bASC\b)/i",
                        "/(\bIN\b)/i",
                        "/(\bIS\b)/i",
                        "/(\bNOT\b)/i",
                        "/(\bNULL\b)/i",
                        "/(\bAS\b)/i",
                        "/(\bON\b)/i"
                    );
                    $replacement = htmlentities("<span class=\"databasedebugmySQLspecial\">\$1</span>");
                    $debugInfo["query"] = preg_replace($special, $replacement, $debugInfo["query"]);

                    // assign the query
                    $xtpl->assign("query", html_entity_decode($debugInfo["query"]));
                    
                    // if there is no error message
                    if (!isset($debugInfo["error"])) {
                    
                        // increment the counter of successful queries
                        $successfulQueries++;
                        
                        $xtpl->assign("nr", $successfulQueries);

                        // the execution time
                        $xtpl->assign("executionTime", $debugInfo["executionTime"]);
                        
                        // if there are informations about the file and the line the
                        // query was called from
                        if ($debugInfo["file"] != "" || $debugInfo["line"] != "" || $debugInfo["method"] != "") {
                            if ($debugInfo["method"] != "") {
                                $xtpl->assign("callerMethod", $debugInfo["method"]);
                                $xtpl->parse("main.successfulQueriesContainer.successfulQueriesEntry.extraInfo.callerMethod");
                            }
                            $xtpl->assign("file", $debugInfo["file"] == "" ? $this->languageStrings["strLang_notSpecified"] : $debugInfo["file"]);
                            $xtpl->assign("line", $debugInfo["line"] == "" ? $this->languageStrings["strLang_notSpecified"] : $debugInfo["line"]);
                            // show them
                            $xtpl->parse("main.successfulQueriesContainer.successfulQueriesEntry.extraInfo");
                        }
                        
                        // if query was a SELECT query
                        if (!$debugInfo["affectedRows"]) {
                            $xtpl->assign("returnedRows", !$debugInfo["returnedRows"] ? "0" : $debugInfo["returnedRows"]);
                            $xtpl->parse("main.successfulQueriesContainer.successfulQueriesEntry.selectQuery_related");
                        // if query was an action query
                        } else {
                            $xtpl->assign("affectedRows", !$debugInfo["affectedRows"] ? 0 : $debugInfo["affectedRows"]);
                            $xtpl->parse("main.successfulQueriesContainer.successfulQueriesEntry.actionQuery_related");
                        }

                        // and parse the related template
                        $xtpl->parse("main.successfulQueriesContainer.successfulQueriesEntry");
                        
                        // total execution time of queries
                        $totalExecutionTime += $debugInfo["executionTime"];

                    // if there is an error message
                    } else {
                    
                        // increment the counter of unsuccessful queries
                        $unsuccessfulQueries++;
                        
                        $xtpl->assign("nr", $unsuccessfulQueries);
                        
                        // and the error message (if there is one)
                        $xtpl->assign("error", $debugInfo["error"]);

                        // if there are informations about the file and the line the
                        // query was called from
                        if ($debugInfo["file"] != "" || $debugInfo["line"] != "" || $debugInfo["method"] != "") {
                            if ($debugInfo["method"] != "") {
                                $xtpl->assign("callerMethod", $debugInfo["method"]);
                                $xtpl->parse("main.unsuccessfulQueriesContainer.unsuccessfulQueriesEntry.extraInfo.callerMethod");
                            }
                            $xtpl->assign("file", $debugInfo["file"] == "" ? $this->languageStrings["strLang_notSpecified"] : $debugInfo["file"]);
                            $xtpl->assign("line", $debugInfo["line"] == "" ? $this->languageStrings["strLang_notSpecified"] : $debugInfo["line"]);
                            // show them
                            $xtpl->parse("main.unsuccessfulQueriesContainer.unsuccessfulQueriesEntry.extraInfo");
                        }

                        // and parse the related template
                        $xtpl->parse("main.unsuccessfulQueriesContainer.unsuccessfulQueriesEntry");
                        
                    }
                    
                }
                
            }
            
            // if there are no successful queries
            if ($successfulQueries == 0) {
                // parse the related template
                $xtpl->parse("main.successfulQueriesContainer.successfulQueriesContainer_Empty");
            }
            
            // if there are no unsuccessful queries
            if ($unsuccessfulQueries == 0) {
                // parse the related templates
                $xtpl->parse("main.unsuccessfulQueriesContainer.unsuccessfulQueriesContainer_Empty");
                $xtpl->parse("main.header.noUnsuccessfulQueries");
            // if there were unsuccessful queries
            } else {
                // highlight the fact in header
                $xtpl->parse("main.header.unsuccessfulQueries");
            }
            
            // parse the container templates for both successful and unsuccessful queries
            $xtpl->parse("main.unsuccessfulQueriesContainer");
            $xtpl->parse("main.successfulQueriesContainer");
            
            $xtpl->assign("totalExecutionTime", $totalExecutionTime);
            $xtpl->assign("successfulQueries", $successfulQueries);
            $xtpl->assign("unsuccessfulQueries", $unsuccessfulQueries);
            $messages = 0;
            
            // if there is data about messages
            if (isset($this->debugInfo["messages"])) {
            
                // hightlight the fact in the header
                $xtpl->parse("main.header.messages");

                // iterate through all the messages realted data
                foreach ($this->debugInfo["messages"] as $debugInfo) {
                
                    // assign message
                    $xtpl->assign("message", $this->languageStrings[$debugInfo["message"]]);
                    
                    // caller method
                    $xtpl->assign("method", $debugInfo["method"]);
                    
                    // parse the related template
                    $xtpl->parse("main.messagesContainer.messagesEntry");
                    
                    // incremenet message counter
                    $messages++;
                    
                }
                
            // if there are no messages
            } else {

                $xtpl->parse("main.header.noMessages");

            }
            
            $xtpl->assign("totalMessages", $messages);
            
            // if there are no messages
            if ($messages == 0) {
                // parse the related template
                $xtpl->parse("main.messagesContainer.messagesContainer_Empty");
            }
            
            // parse the container templates for messages
            $xtpl->parse("main.messagesContainer");
            
            // parse watches
            $watches = 0;

            // if there is data about watches
            if (isset($this->debugInfo["watch"])) {

                // iterate through all the watches realted data
                foreach ($this->debugInfo["watch"] as $debugInfo) {

                    // assign output
                    $xtpl->assign("name", $debugInfo["name"]);
                    $xtpl->assign("watch", print_r($debugInfo["variable"], true));

                    // parse the related template
                    $xtpl->parse("main.watchContainer.watchEntry");

                    // incremenet watch counter
                    $watches++;

                }

            }

            $xtpl->assign("totalWatches", $watches);

            // if there are no watches
            if ($watches == 0) {
                // parse the related template
                $xtpl->parse("main.watchContainer.watchContainer_Empty");
            }

            // parse the container templates for watches
            $xtpl->parse("main.watchContainer");


            // parses template for global variables
            $xtpl->assign("globals", print_r($_COOKIE, true));
            $xtpl->assign("varname", "\$_COOKIE");
            $xtpl->assign("section", "cookie");
            $xtpl->parse("main.globalsContainer.globalsEntry");

            $xtpl->assign("globals", print_r($_FILES, true));
            $xtpl->assign("varname", "\$_FILES");
            $xtpl->assign("section", "files");
            $xtpl->parse("main.globalsContainer.globalsEntry");

            $xtpl->assign("globals", print_r($_GET, true));
            $xtpl->assign("varname", "\$_GET");
            $xtpl->assign("section", "get");
            $xtpl->parse("main.globalsContainer.globalsEntry");

            $xtpl->assign("globals", print_r($_POST, true));
            $xtpl->assign("varname", "\$_POST");
            $xtpl->assign("section", "post");
            $xtpl->parse("main.globalsContainer.globalsEntry");
            
            $xtpl->assign("globals", print_r($_SERVER, true));
            $xtpl->assign("varname", "\$_SERVER");
            $xtpl->assign("section", "server");
            $xtpl->parse("main.globalsContainer.globalsEntry");

            $xtpl->assign("globals", @print_r($_SESSION, true));
            $xtpl->assign("varname", "\$_SESSION");
            $xtpl->assign("section", "session");
            $xtpl->parse("main.globalsContainer.globalsEntry");

            $xtpl->parse("main.globalsContainer");
            
            // if the debug bar is to be shown minimized
            if ($this->minimizeDebugger) {
                $xtpl->assign("displayMiniBar", "block");
                $xtpl->assign("displayFullBar", "none");
            // if the debug bar is to be shown full size
            } else {
                $xtpl->assign("displayMiniBar", "none");
                $xtpl->assign("displayFullBar", "block");
            }

            // finish the parsing and output the result
            $xtpl->parse("main.header");
            $xtpl->parse("main");
            
            // if debug output is to be logged to file instead of being outputted to the screen
            if ($toLogFile && ($ipAddress == "" || ($ipAddress != "" && $ipAddress == $_SERVER["REMOTE_ADDR"]))) {
                $content = $xtpl->text("main");
                $handle = @fopen($this->classPath."/logs/log.html", "w");
                @fwrite($handle, $content);
                @fclose($handle);
            // output debug info to the screen
            } else {
                $xtpl->out("main");
            }
            
        }
        
    }
    
    /**
     *  Handles saving of debug information and halts the execution of the script upon fatal errors
     *
     *  @access private
     */
    function saveDebugInfo($category, $infoArray, $fatal = false)
    {

        // saves debug information
        $this->debugInfo[$category][] = $infoArray;

        // if the saved debug info is about a fatal error
        // and execution is to be stopped on fatal errors
        if ($fatal && $this->haltOnErrors) {

            // if debug window is to be displayed
            if ($this->debug) {
                $this->show_debug_info();
            }
            
            // halt execution
            die();

        }
        
    }

    /**
     *  Checks if the connection to a mySQL server has been previously estabilished by the connect() method
     *
     *  @access private
     */
    function _connected()
    {
        // if there's no connection to a mysql database
        if (!$this->link) {
        
            // return false
            return false;
            
        }
        
        // return true if there is no error
        return true;
        
    }
    
}
?>
Return current item: PHP mySQL Database Wrapper Class