// Open the base (construct the object):
$db = new Database($base, $server, $user, $pass);
// You can also open a second connection
$db2 = new Database($base2, $server, $user, $pass);
 
/************************** Basic Queries: **********************************/
 
// Do a query (return the same as mysql_query):
$result = $db->query("SELECT * FROM People");
$result = $db->query("SELECT * FROM People", true); // With DEBUG
 
// Browse results of a query (return a line as an object):
while ($line = $db->fetchNextObject($result)) {}
while ($line = $db->fetchNextObject()) {} // From the LAST query
 
// Commonly, you can copy/paste this code for one query at a time:
$db->query("SELECT * FROM People");
while ($line = $db->fetchNextObject()) {
  echo $line->aField;
}
 
// But for sub-queries you should store the result of the first queries:
$result = $db->query("SELECT * FROM Towns");
while ($line = $db->fetchNextObject($result)) {
  $db->query("SELECT * FROM People WHERE town='{$line->name}'");
  while ($line2 = $db->fetchNextObject()) {
    echo $line->aField, $line2->anotherField;
  }
}
 
// If you have a query that doesn't return any result such as INSERT, UPDATE, DELETE... use this:
$db->execute("UPDATE People SET name=name");
 
/************************** Convenient Query Functions: *********************/
// Do a query and return the unique resulting cell from it (or NULL if there is no result):
// Equivalent to a call to query() and then to fetchNextObject() and then a fetching of the first cell.
$age = $db->queryUniqueValue("SELECT age FROM People WHERE id=2");
 
// Get the id of the last inserted line:
$lastId = $db->lastInsertedId();
 
// The execution time of the script (from "new DB();"):
$time = $db->getExecTime();
 
// The number of executed queries (from "new DB();"):
$queriesCount = $db->getQueriesCount();
 
/************************** Useless Functions: ******************************/
 
// Number of lines of a result (return an integer):
$numLines = $db->numRows($result);
$numLines = $db->numRows(); // From the LAST query
 
// Go back to the first line in a fetch:
$db->resetFetch($result);
 
// Close the connexion to the base:
$db->close();

<?
/**
* Database.class.php
* A PHP class to access MySQL database with convenient methods
* in an object oriented way, and with a powerful debug system.
* Modified from http://slaout.linux62.org/.
* Original author Sébastien Laoût
* @version 1.0
* @author Marc-Andre Caron
* @license http://www.gnu.org/copyleft/lesser.html LGPL
*/
class Database
{
  //------------------------------------------------------------
  // PROPERTIES
  //------------------------------------------------------------
 
  //--Protected properties--//
 
  /**
  * @var array The database connection variable.
  * @access protected
  */
  protected $_connection;
 
  /**
  * @var bool Put this variable to true if you want ALL queries to be debugged by default.
  * @access protected
  */
  protected $_default_debug = false;
 
  /**
  * @var float The start time, in miliseconds.
  * @access protected
  */
  protected $_mt_start;
 
  /**
  * @var int The number of executed queries.
  * @access protected
  */
  protected $_nb_queries;
 
  /**
  * @var array The last result ressource of a query.
  * @access protected
  */
  protected $_last_result;
 
  //------------------------------------------------------------
  // METHODS
  //------------------------------------------------------------
 
  //--Public methods--//
 
  /**
  * This is the class constructor. 
  * It allows to connect to the database
  * @param string $base Database name.
  * @param string $server Server location.
  * @param string $user Database user.
  * @param string $pass Database password.
  * @access public
  */
  public function __construct($base, $server, $user, $pass)
  {
    $this->_mt_start = $this->getMicroTime();
    $this->_nb_queries = 0;
    $this->_last_result = NULL;
    $this->_connection = mysql_connect($server, $user, $pass, true) or die('Server connection not possible.');
    mysql_select_db($base,$this->_connection) or die('Database connection not possible.');
  }
 
  /** Method to query the database.
  * @param $query The query.
  * @param $debug If true, output the query and the resulting table.
  * @return The result of the query, to use with fetchNextObject().
  * @access public
  */
  public function query($query, $debug = -1)
  {
    $this->_nb_queries++;
    $this->_last_result = mysql_query($query, $this->_connection) or $this->debugAndDie($query);
 
    $this->debug($debug, $query, $this->_last_result);
 
    return $this->_last_result;
  }
 
  /** Method to execute a query\n
  * Should be used for INSERT, UPDATE, DELETE...
  * @param $query The query.
  * @param $debug If true, it output the query and the resulting table.
  * @access public
  */
  public function execute($query, $debug = -1)
  {
    $this->_nb_queries++;
    mysql_query($query, $this->_connection) or $this->debugAndDie($query);
 
    $this->debug($debug, $query);
  }
 
  /** Method for mysql_fetch_object().
  * @param $result The ressource returned by query(). If NULL, the last result returned by query() will be used.
  * @return An object representing a data row.
  * @access public
  */
  public function fetchNextObject($result = NULL)
  {    
    if ($result == NULL) {
      $result = $this->_last_result;
    }
    if ($result == NULL || mysql_num_rows($result) < 1) {
      return NULL;
    } else {
      return mysql_fetch_object($result);
    }
  }
 
  /** Method to get the number of rows of a query.
  * @param $result The ressource returned by query(). If NULL, the last result returned by query() will be used.
  * @return The number of rows of the query (0 or more).
  * @access public
  */
  public function numRows($result = NULL)
  {
    if ($result == NULL) {
      return mysql_num_rows($this->_last_result);
    } else {
      return mysql_num_rows($result);
    }
  }
 
  /** Method to get the result of the query as value. The query should return a unique cell.\n
  * Note: no need to add "LIMIT 1" at the end of your query because
  * the method will add that (for optimisation purpose).
  * @param $query The query.
  * @param $debug If true, it output the query and the resulting value.
  * @return A value representing a data cell (or NULL if result is empty).
  * @access public
  */
  public function queryUniqueValue($query, $debug = -1)
  {
    $query = "$query LIMIT 1";
 
    $this->_nb_queries++;
    $result = mysql_query($query, $this->_connection) or $this->debugAndDie($query);
    $line = mysql_fetch_row($result);
 
    $this->debug($debug, $query, $result);
 
    return $line[0];
  }
 
  /** Method to get the count of rows in a table, with a condition.
  * @param $table The table where to compute the number of rows.
  * @param $where The condition before to compute the number or rows.
  * @return The number of rows (0 or more).
  * @access public
  */
  public function countOf($table, $where)
  {
    return $this->queryUniqueValue("SELECT COUNT(*) FROM `$table` WHERE $where");
  }
 
  /** Method to get the count of rows in a table.
  * @param $table The table where to compute the number of rows.
  * @return The number of rows (0 or more).
  * @access public
  */
  public function countOfAll($table)
  {
    return $this->queryUniqueValue("SELECT COUNT(*) FROM `$table`");
  }
 
  /** Method to get how many time the script took from the begin of this object.
  * @return The script execution time in seconds since the
  * creation of this object.
  * @access public
  */
  public function getExecTime()
  {
    return round(($this->getMicroTime() - $this->_mt_start) * 1000) / 1000;
  }
 
  /** Method to get the number of queries executed from the begin of this object.
  * @return The number of queries executed on the database server since the
  * creation of this object.
  * @access public
  */
  public function getQueriesCount()
  {
    return $this->_nb_queries;
  }
 
  /** Method to get the id of the very last inserted row.
  * @return The id of the very last inserted row (in any table).
  * @access public
  */
  public function lastInsertedId()
  {
    return mysql_insert_id($this->_connection);
  }
 
  /** Method to close the connection with the database server.\n
  * It's usually unneeded since PHP do it automatically at script end.
  * @access public
  */
  public function close()
  {
    mysql_close($this->_connection);
  }
 
  //--Protected methods--//
 
  /** Method to get the current time.
  * @return The current time in seconds with microseconds (in float format).
  * @access protected
  */
  protected function getMicroTime()
  {
    list($msec, $sec) = explode(' ', microtime());
    return floor($sec / 1000) + $msec;
  }
 
  /** Method to debug when MySQL encountered an error,
  * even if debug is set to Off.
  * @param $query The SQL query to echo before diying.
  * @access protected
  */
  protected function debugAndDie($query)
  {
    $this->debugQuery($query, 'Error');
    die('<p style="margin: 2px;">' . mysql_error($this->_connection) . '</p></div>');
  }
 
  /** Method to debug a MySQL query.\n
  * Show the query and output the resulting table if not NULL.
  * @param $debug The parameter passed to query() functions. Can be boolean or -1 (default).
  * @param $query The SQL query to debug.
  * @param $result The resulting table of the query, if available.
  * @access protected
  */
  protected function debug($debug, $query, $result = NULL)
  {
    if ($debug === -1 && $this->_default_debug === false)
      return;
    if ($debug === false)
      return;
 
    $reason = ($debug === -1 ? 'Default Debug' : 'Debug');
    $this->debugQuery($query, $reason);
    if ($result == NULL) {
      echo '<p style="margin: 2px;">Number of affected rows: ' . mysql_affected_rows($this->_connection) . '</p></div>';
    } else {
      $this->debugResult($result);
    }
  }
 
  /** Method to output a query for debug purpose.\n
  * Should be followed by a call to debugResult() or an echo of '</div>' . 
  * @param $query The SQL query to debug.
  * @param $reason The reason why this function is called: 'Default Debug', 'Debug' or 'Error' . 
  * @access protected
  */
  protected function debugQuery($query, $reason = 'Debug')
  {
    $color = ($reason == 'Error' ? 'red' : 'orange');
    echo '<div style="border: solid ' . $color . ' 1px; margin: 2px;">' . 
    '<p style="margin: 0 0 2px 0; padding: 0; background-color: #DDF;">' . 
    '<strong style="padding: 0 3px; background-color: ' . $color . '; color: white;">' . $reason . ':</strong> ' . 
    '<span style="font-family: monospace;">' . htmlentities($query) . '</span></p>';
  }
 
  /** Method to output a table representing the result of a query, for debug purpose.\n
  * Should be preceded by a call to debugQuery().
  * @param $result The resulting table of the query.
  * @access protected
  */
  protected function debugResult($result)
  {
    echo '<table border="1" style="margin: 2px;">' . 
    '<thead style="font-size: 80%">';
    $numFields = mysql_num_fields($result);
    // Begin header
    $tables    = array();
    $nbTables  = -1;
    $lastTable = '';
    $fields    = array();
    $nbFields  = -1;
    while ($column = mysql_fetch_field($result)) {
      if ($column->table != $lastTable) {
        $nbTables++;
        $tables[$nbTables] = array('name' => $column->table, 'count' => 1);
      } else {
        $tables[$nbTables]['count']++;
      }
      $lastTable = $column->table;
      $nbFields++;
      $fields[$nbFields] = $column->name;
    }
    for ($i = 0; $i <= $nbTables; $i++)
      echo '<th colspan=' . $tables[$i]['count'] . '>' . $tables[$i]['name'] . '</th>';
    echo '</thead>';
    echo '<thead style="font-size: 80%">';
    for ($i = 0; $i <= $nbFields; $i++)
      echo '<th>' . $fields[$i] . '</th>';
    echo '</thead>';
    // End header
    while ($row = mysql_fetch_array($result)) {
      echo '<tr>';
      for ($i = 0; $i < $numFields; $i++)
        echo '<td>' . htmlentities($row[$i]) . '</td>';
      echo '</tr>';
    }
    echo '</table>';
     //Execution time
    echo 'Query executed in ' . $this->getExecTime() . ' ms';
    echo '</div>';
    $this->resetFetch($result);
  }
 
  /** Go back to the first element of the result line.
  * @param $result The resssource returned by a query() function.
  * @access protected
  */
 
  protected function resetFetch($result)
  {
    if (mysql_num_rows($result) > 0) mysql_data_seek($result, 0);
  }
}
?>