MySQL Database Class

This is a set of classes which make communicating with a MySQL Database a lot easier.

It's divided into four classes:
- MySQLDatabase, the main class, which connects to your MySQL server and selects the database etc.

- MySQLResult, when you execute a query which returns rows, this object will be returned. From this object, you can fetch rows, and a lot more. This class also implements the Iterator interface, so you can use this class in a foreach statement.

- MySQLStatement, a class which can be used for parameter binding, repetitive mysql queries etc.

- MySQLException, an exception class, which will be thrown when an error occurs.

You can connect to multiple servers, and select multiple databases, also transactions are supported, but transactions only work with InnoDB as engine.

Download Download script as zip

Tag Tags: OOP MySQL PHP

Source

  • MySQLDatabase.php
  • MySQLResult.php
  • MySQLStatement.php
  • MySQLException.php
  • example.php
  1. <?php
  2. /**
  3.  * MySQL Class
  4.  * @package Database
  5.  * @author Lucas van Dijk (http://www.return1.net)
  6.  * @copyright (c) Copyright 2007 by Lucas van Dijk
  7.  * @license http://www.opensource.org/licenses/gpl-license.php
  8.  */
  9.  
  10. /**
  11.  * MySQL Database class
  12.  *
  13.  * Example:
  14.  * <code>
  15.  * $db = new MySQL($host, $user, $pass, $database);
  16.  * $result = $db -> query("SELECT * FROM table LIMIT 1");
  17.  * $row =  $result -> fetch();
  18.  * </code>
  19.  * @package Database
  20.  * @author Lucas van Dijk
  21.  */
  22. class MySQLDatabase
  23. {
  24.     protected $connection;
  25.     protected $in_transaction;
  26.     protected static $num_queries;
  27.    
  28.     /**
  29.      * Constructor, connects to the database, and selects the database
  30.      * @param string $host The IP of the database server
  31.      * @param string $database The database name to select
  32.      * @param string $user The username of the database
  33.      * @param string $pass the password of the database
  34.      */                     
  35.     public function __construct($host = '', $database = '', $user = '', $pass = '')
  36.     {
  37.         // Connect to database
  38.         if(!empty($host) && !empty($user))
  39.         {
  40.             $this -> connect($host, $user, $pass);
  41.         }
  42.        
  43.         // Select database
  44.         if(!empty($database))
  45.         {
  46.             $this -> select_database($database);
  47.         }
  48.        
  49.         $this -> in_transaction = false;
  50.     }
  51.    
  52.     /**
  53.      * Destructor, closes the connection to the database
  54.      */
  55.     public function __destruct()
  56.     {
  57.         mysql_close($this -> connection);
  58.     }
  59.    
  60.     /**
  61.      * Connect to the database
  62.      * @param string $host The server where MySQL is
  63.      * @param string $user The username of the MySQL server
  64.      * @param string $pass The password of the MySQL server
  65.      */
  66.     public function connect($host, $user, $pass = '')
  67.     {
  68.         $this -> connection = mysql_connect($host, $user, $pass);
  69.         if(!$this -> connection)
  70.         {
  71.             throw new MySQLException('Could not connect to MySQL Server');
  72.         }
  73.     }       
  74.    
  75.     /**
  76.      * Selects another database
  77.      * @param string $database_name The name of the database
  78.      */         
  79.     public function select_database($database_name)
  80.     {
  81.         if(!mysql_select_db($database_name, $this -> connection))
  82.         {
  83.             throw new MySQLException('Could not select database');
  84.         }
  85.     }
  86.    
  87.     /**
  88.      * Executes a query which returns rows (SELECT statements etc.), and returns a MySQLResult object
  89.      * @param string|MySQLStatement $query The query to execute
  90.      * @return MySQLResult A MySQLResult object containing the query result
  91.      */             
  92.     public function query($query)
  93.     {
  94.         if($query instanceof MySQLStatement)
  95.         {
  96.             $query = $query->__toString();
  97.         }
  98.        
  99.         if(!$result = mysql_query($query, $this -> connection))
  100.         {
  101.             if($this -> in_transaction)
  102.             {
  103.                 $this -> rollback();
  104.                 $this -> in_transaction = false;
  105.             }
  106.            
  107.             throw new MySQLException('Could not execute query', $query);
  108.         }
  109.        
  110.         self::$num_queries++;
  111.        
  112.         return new MySQLResult($result);
  113.     }
  114.    
  115.     /**
  116.      * Executes a query which doesn't return rows (UPDATE statement etc.), and returns the number of rows affected
  117.      * @param string $query The query to execute
  118.      * @return int The number of rows affected
  119.      */
  120.     public function execute($query)
  121.     {
  122.         if($query instanceof MySQLStatement)
  123.         {
  124.             $query = $query->__toString();
  125.         }
  126.        
  127.         if(!mysql_query($query, $this -> connection))
  128.         {
  129.             if($this -> in_transaction)
  130.             {
  131.                 $this -> rollback();
  132.                 $this -> in_transaction = false;
  133.             }
  134.            
  135.             throw new MySQLException('Could not execute query', $query);
  136.         }
  137.        
  138.         self::$num_queries++;
  139.        
  140.         return mysql_affected_rows($this -> connection);
  141.     }
  142.    
  143.     /**
  144.      * Starts a transaction
  145.      */
  146.     public function begin_transaction()
  147.     {
  148.         if(!$this -> in_transaction)
  149.         {
  150.             if(!mysql_query("START TRANSACTION", $this -> connection))
  151.             {
  152.                 throw new MySQLException('Could not start transaction');
  153.             }
  154.            
  155.             $this -> in_transaction = true;
  156.         }
  157.         else
  158.         {
  159.             throw new MySQLException('Already in transaction, call MySQL::end_transaction() first');
  160.         }
  161.     }
  162.    
  163.     /**
  164.      * Ends a transaction, and commits the queries
  165.      */
  166.     public function end_transaction()
  167.     {
  168.         if($this -> in_transaction)
  169.         {
  170.             $this -> in_transaction = false;
  171.             if(!mysql_query("COMMIT", $this -> connection))
  172.             {
  173.                 $this -> rollback();
  174.                 throw new MySQLException('Could not end transaction');
  175.             }          
  176.         }
  177.         else
  178.         {
  179.             throw new MySQLException('Not in a transaction');
  180.         }
  181.     }
  182.    
  183.     /**
  184.      * Rolls the transaction back
  185.      */
  186.     public function rollback()
  187.     {
  188.         if(!mysql_query("ROLLBACK", $this -> connection))
  189.         {
  190.             throw new MySQLException('Could not rollback');
  191.         }
  192.     }                                       
  193.    
  194.     /**
  195.      * Prepares an MySQL query
  196.      * @param string $query The query to prepare
  197.      * @return MySQLStatement A MySQLStatement object
  198.      */             
  199.     public function prepare($query)
  200.     {
  201.         return new MySQLStatement($query, $this -> connection);
  202.     }
  203.    
  204.     /**
  205.      * Returns the last ID from the last INSERT statement
  206.      * @return int
  207.      */
  208.     public function last_id()
  209.     {
  210.         return mysql_insert_id($this -> connection);
  211.     }
  212.    
  213.     /**
  214.      * Gets the number of queries executes
  215.      * @return int The number of queries
  216.      */
  217.     public function get_num_queries()
  218.     {
  219.         return self::$num_queries;
  220.     }
  221.    
  222.     /**
  223.      * Escapes the given text
  224.      * @return string The escaped text
  225.      */
  226.     public function escape($text)
  227.     {
  228.         return mysql_real_escape_string($text, $this -> connection);
  229.     }                      
  230. }
  231.  
  1. <?php
  2. /**
  3.  * MySQL Class
  4.  * @package Database
  5.  * @author Lucas van Dijk (http://www.return1.net)
  6.  * @copyright (c) Copyright 2007 by Lucas van Dijk
  7.  * @license http://www.opensource.org/licenses/gpl-license.php
  8.  */
  9.  
  10. /**
  11.  * The MySQL Result class
  12.  *
  13.  * Holds the result of an MySQL Query
  14.  * Example:
  15.  * <code>
  16.  * $result = $db -> query("SELECT * FROM table");
  17.  * echo "Num Rows: ".$result -> num_rows()."<br />";
  18.  * foreach($result as $row)
  19.  * {
  20.  *      echo $row['title']."<br />";
  21.  * }
  22.  * </code>
  23.  * @package Database
  24.  * @author Lucas van Dijk
  25.  */
  26. class MySQLResult implements Iterator
  27. {
  28.     protected $query_result;
  29.     protected $last_row;
  30.     protected $rowset;
  31.     protected $pointer;
  32.  
  33.     /**
  34.      * Inits our $query_result var
  35.      * @param resource $result a valid resource from mysql_query()
  36.      */
  37.     public function __construct(&$result)
  38.     {
  39.         if(!is_resource($result))
  40.         {
  41.             throw new MySQLException('$result is not a valid resource');
  42.         }
  43.  
  44.         $this -> query_result = $result;
  45.     }
  46.  
  47.     public function __destruct()
  48.     {
  49.         mysql_free_result($this -> query_result);
  50.     }
  51.  
  52.     /**
  53.      * Fetches a row
  54.      * @return array|bool Returns an array with the current row, or false if there aren't any rows left
  55.      */
  56.     public function fetch()
  57.     {
  58.         $this -> last_row = mysql_fetch_assoc($this -> query_result);
  59.         return $this -> last_row;
  60.     }
  61.  
  62.     /**
  63.      * Fetches all rows, and gives an array back
  64.      * @return array A multi dimensional array containing all rows
  65.      */
  66.     public function fetch_rowset()
  67.     {
  68.         if(is_array($this -> rowset))
  69.         {
  70.             return $this -> rowset;
  71.         }
  72.         else
  73.         {
  74.             $result = array();
  75.  
  76.             while($row = $this -> fetch())
  77.             {
  78.                 $result[] = $row;
  79.             }
  80.             $this -> rowset = $result;
  81.  
  82.             return $result;
  83.         }
  84.     }
  85.  
  86.     /**
  87.      * Fetches the value of an certain field
  88.      * @param string $field The name of the field to fetch
  89.      * @param int $rownr The number of the row, if you enter -1, the last row fetched will be used
  90.      * @return string The value of the field
  91.      */
  92.     public function fetch_field($field, $rownr = -1)
  93.     {
  94.         $result = false;
  95.         if($rownr > -1)
  96.         {
  97.             $result = mysql_result($this -> query_result, $rownr, $field);
  98.         }
  99.         else
  100.         {
  101.             if(!empty($this -> last_row))
  102.             {
  103.                 $result = $this -> last_row[$field];
  104.             }
  105.             else
  106.             {
  107.                 $this -> fetch();
  108.                 $result = $this -> last_row[$field];
  109.             }
  110.         }
  111.  
  112.         return $result;
  113.     }
  114.  
  115.     /**
  116.      * Gets the number of rows
  117.      * @return int The number of rows
  118.      */
  119.     public function num_rows()
  120.     {
  121.         return mysql_num_rows($this -> query_result);
  122.     }
  123.  
  124.     /**
  125.      * Changes the row pointer
  126.      * @param int $row The row number
  127.      */
  128.     public function row_seek($row)
  129.     {
  130.         if(!mysql_data_seek($this -> query_result, $row))
  131.         {
  132.             throw new MySQLException('Row not found');
  133.         }
  134.         $this -> pointer = $row;
  135.     }
  136.  
  137.     //
  138.     // Iteration functions
  139.     //
  140.  
  141.     /**
  142.      * Rewinds the current place
  143.      */
  144.     public function rewind()
  145.     {
  146.         if($this -> num_rows() > 0)
  147.         {
  148.             $this -> row_seek(0);
  149.         }
  150.     }
  151.  
  152.     /**
  153.      * Returns the current row
  154.      */
  155.     public function current()
  156.     {
  157.         if(!is_array($this -> last_row))
  158.         {
  159.             $this -> fetch();
  160.         }
  161.  
  162.         return $this -> last_row;
  163.     }
  164.  
  165.     /**
  166.      * Goes to the next row
  167.      */
  168.     public function next()
  169.     {
  170.         $this -> fetch();
  171.     }
  172.  
  173.     /**
  174.      * Goes to the previous row
  175.      */
  176.     public function previous()
  177.     {
  178.         $this -> row_seek($this -> pointer - 1);
  179.     }
  180.  
  181.     /**
  182.      * Checks of we're on the end of the list
  183.      */
  184.     public function valid()
  185.     {
  186.         return $this -> current() !== false;
  187.     }
  188.  
  189.     /**
  190.      * Returns the current pointer
  191.      */
  192.     public function key()
  193.     {
  194.         return $this -> pointer;
  195.     }
  196. }
  197.  
  1. <?php
  2. /**
  3.  * MySQL Class
  4.  * @package Database
  5.  * @author Lucas van Dijk (http://www.return1.net)
  6.  * @copyright (c) Copyright 2007 by Lucas van Dijk
  7.  * @license http://www.opensource.org/licenses/gpl-license.php
  8.  */
  9.  
  10. /**
  11.  * MySQL Statement class, used for parameter binding, and to simplify queries you must often execute
  12.  *
  13.  * Example:
  14.  * <code>
  15.  * try
  16.  * {
  17.  *      $db -> select_database('test');
  18.  *      $statement = $db->prepare("INSERT INTO test.persons (name, age) VALUES (?, ?)");
  19.  *      $statement -> bind_parameter(0, "Lucas van Dijk");
  20.  *      $statement -> bind_parameter(1, 16);
  21.  * 
  22.  *      $db -> execute($statement);
  23.  * 
  24.  *      // Set new value
  25.  *      $statement -> bind_parameter(0, "Ted's");
  26.  *      $statement -> bind_parameter(1, 19);
  27.  * 
  28.  *      $db -> execute($statement);
  29.  * }
  30.  * catch(MySQLException $e)
  31.  * {
  32.  *      die($e->getMessage().": ".$e->get_error());
  33.  * }
  34.  * </code>
  35.  * @package Database
  36.  * @author Lucas van Dijk
  37.  */              
  38. class MySQLStatement
  39. {
  40.     protected $query;
  41.     protected $parameters;
  42.     protected $parameters2;
  43.    
  44.     /**
  45.      * Constructor, sets the query
  46.      * @param string $query The query
  47.      */         
  48.     public function __construct($query)
  49.     {
  50.         $this -> query = $query;
  51.     }
  52.    
  53.     /**
  54.      * Binds a placeholder to the query
  55.      * @param string|array $key the key of the placeholder or an array with placeholders
  56.      * @param mixed $value the value of the placeholder
  57.      */             
  58.     public function bind_parameter($key, $value = '')
  59.     {
  60.         if(func_num_args() == 1 && is_array($key))
  61.         {
  62.             foreach($key as $place_holder => $value)
  63.             {
  64.                 if(ctype_digit((string) $place_holder))
  65.                 {
  66.                     $this -> parameters2[] = ctype_digit((string) $value) ? $value : "'".mysql_real_escape_string($value)."'";
  67.                 }
  68.                 else
  69.                 {
  70.                     $this -> parameters[$place_holder] = ctype_digit((string) $value) ? $value : "'".mysql_real_escape_string($value)."'";
  71.                 }
  72.             }
  73.         }
  74.         else if(func_num_args() == 1 && !is_array($key))
  75.         {
  76.             throw new Exception('First parameter is not an array.');
  77.         }
  78.         else
  79.         {          
  80.             if(ctype_digit((string) $key))
  81.             {
  82.                 $this -> parameters2[$key] = ctype_digit((string) $value) ? $value : "'".mysql_real_escape_string($value)."'";
  83.             }
  84.             else
  85.             {
  86.                 $this -> parameters[$key] = ctype_digit((string) $value) ? $value : "'".mysql_real_escape_string($value)."'";
  87.             }
  88.         }
  89.     }
  90.    
  91.     /**
  92.      * Loops through the complete string, and replaces all parameters
  93.      * @return string The query with the replaced parameters
  94.      */         
  95.     protected function replace_parameters()
  96.     {
  97.         $query = $this -> query;
  98.         if(!empty($this -> parameters))
  99.         {
  100.             $query = strtr($query, $this -> parameters);
  101.         }
  102.        
  103.         if(!empty($this -> parameters2))
  104.         {
  105.             $num_params = 0;
  106.            
  107.             for($i = 0, $j = strlen($query); $i < $j; $i++)
  108.             {
  109.                 if($query{$i} == '?')
  110.                 {
  111.                     if(!empty($this -> parameters2[$num_params]))
  112.                     {
  113.                         $query = substr_replace($query, $this -> parameters2[$num_params], $i, 1);
  114.                         $i += strlen($this -> parameters2[$num_params]);
  115.                         $j = strlen($query);
  116.                         $num_params++;
  117.                     }
  118.                 }
  119.             }
  120.         }
  121.        
  122.         return $query;
  123.     }
  124.    
  125.     /**
  126.      * Simply outputs the query
  127.      * @return string The query
  128.      */         
  129.     public function __toString()
  130.     {
  131.         return $this -> replace_parameters();
  132.     }
  133. }
  134.  
  1. <?php
  2. /**
  3.  * MySQL Class
  4.  * @package Database
  5.  * @author Lucas van Dijk (http://www.return1.net)
  6.  * @copyright (c) Copyright 2007 by Lucas van Dijk
  7.  * @license http://www.opensource.org/licenses/gpl-license.php
  8.  */
  9.  
  10. /**
  11.  * MySQL Exception class
  12.  *
  13.  * Retreives the last error, and err no. for debugging
  14.  * @package Database
  15.  * @author Lucas van Dijk
  16.  */    
  17. class MySQLException extends Exception
  18. {
  19.     protected $error;
  20.     protected $sql_query;
  21.    
  22.     public function __construct($message, $sql_query = '')
  23.     {
  24.         // Retreive the last error from MySQL
  25.         $this -> error = mysql_error();
  26.         $this -> code = mysql_errno();
  27.        
  28.         $this -> message = $message;
  29.         $this -> sql_query = $sql_query;
  30.         parent::__construct();
  31.     }
  32.    
  33.     public function get_error()
  34.     {
  35.         return $this -> error;
  36.     }
  37.    
  38.     public function get_sql_query()
  39.     {
  40.         return $this -> sql_query;
  41.     }
  42.    
  43.     public function __toString()
  44.     {
  45.         return "[#".$this -> code."] ".$this -> message.": ".$this -> error."<br /><br /><strong>StackTrace</strong>".$this -> getTraceAsString();
  46.     }
  47. }
  48.  
  1. <?php
  2. /**
  3.  * MySQL Class
  4.  * @package Database
  5.  * @author Lucas van Dijk (http://www.return1.net)
  6.  * @copyright (c) Copyright 2007 by Lucas van Dijk
  7.  * @license http://www.opensource.org/licenses/gpl-license.php
  8.  */
  9.  
  10. $start_time = microtime(true);
  11.  
  12. include 'MySQLDatabase.php';
  13. include 'MySQLException.php';
  14. include 'MySQLResult.php';
  15. include 'MySQLStatement.php';
  16.  
  17. // Connect
  18. try
  19. {
  20.     $db = new MySQLDatabase("localhost", "db", "user", "***");
  21. }
  22. catch(MySQLException $e)
  23. {
  24.     die($e->getMessage().": ".$e->get_error());
  25. }
  26.  
  27. // Pull something from our DB
  28. try
  29. {
  30.     $result = $db -> query("SELECT news_title FROM ln_news");
  31.     echo "Count: ".$result -> num_rows()."<br />";
  32.     foreach($result as $row)
  33.     {
  34.         echo $row['news_title']."<br />";
  35.     }
  36. }
  37. catch(MySQLException $e)
  38. {
  39.     die($e->getMessage().": ".$e->get_error());
  40. }
  41.  
  42. // Preparing
  43. try
  44. {
  45.     $db -> select_database('test');
  46.     $statement = $db->prepare("INSERT INTO test.persons (name, age) VALUES (?, ?)");
  47.  
  48.     $statement -> bind_parameter(0, "Lucas van Dijk?"); // First ? in the query
  49.     $statement -> bind_parameter(1, 16); // The second ?
  50.  
  51.     $db -> execute($statement);
  52.  
  53.     // Set new value
  54.     $statement -> bind_parameter(0, "Ted's");
  55.     $statement -> bind_parameter(1, 19);
  56.  
  57.     $db -> execute($statement);
  58. }
  59. catch(MySQLException $e)
  60. {
  61.     die($e->getMessage().": ".$e->get_error());
  62. }
  63.  
  64. // With our own MySQL Statement
  65. try
  66. {
  67.     $statement = new MySQLStatement("UPDATE persons SET age = :age WHERE name = :name");
  68.     $statement -> bind_parameter(':age', 17);
  69.     $statement -> bind_parameter(':name', 'Lucas van Dijk');
  70.  
  71.     $db -> execute($statement);
  72. }
  73. catch(MySQLException $e)
  74. {
  75.     die($e->getMessage().": ".$e->get_error());
  76. }
  77.  
  78. $end_time = microtime(true);
  79.  
  80. $parse_time = $end_time - $start_time;
  81.  
  82. echo "<br />Number of queries: ".$db -> get_num_queries()."<br />Parse time: ".$parse_time;
  83.