Location: PHPKode > scripts > MySQL Database Abstraction > mysql-database-abstraction/class.db.inc
<?php

/*************************************************************************
 *                                                                       *
 * class.db.inc                                                          *
 *                                                                       *
 *************************************************************************
 *                                                                       *
 * MySQL database abstraction class adapted from PHPLib                  *
 *                                                                       *
 * Copyright (c) 2008 Jon Abernathy <hide@address.com>                  *
 * All rights reserved.                                                  *
 *                                                                       *
 * This script is free software; you can redistribute it and/or modify   *
 * it under the terms of the GNU General Public License as published by  *
 * the Free Software Foundation; either version 2 of the License, or     *
 * (at your option) any later version.                                   *
 *                                                                       *
 * The GNU General Public License can be found at                        *
 * http://www.gnu.org/copyleft/gpl.html.                                 *
 *                                                                       *
 * This script is distributed in the hope that it will be useful,        *
 * but WITHOUT ANY WARRANTY; without even the implied warranty of        *
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the          *
 * GNU General Public License for more details.                          *
 *                                                                       *
 * Author(s): Jon Abernathy, hide@address.com                           *
 *    Based on work by: Boris Erdmann, Kristian Koehntopp                *
 *                                                                       *
 * Last modified: 04/10/08                                               *
 *                                                                       *
 *************************************************************************/

/**
 *  Provides an abstraction layer for MySQL.
 *
 *  This class is based on the original PHPLib distribution, although
 *  I've eschewed much of the original functionality of the PHPLib version,
 *  mostly in order to get "as close to the metal" as possible and provide
 *  only those functions which I've found I use in all my PHP coding. The
 *  main purpose is to encapsulate and simplify the details of connecting
 *  to a MySQL database and performing queries.
 *
 *  That's right, MySQL and MySQL only. I'm not currently interested in
 *  developing an all-purpose database abstraction tool; in general, I
 *  don't like to be abstracted that far away from the DB.
 *
 *  This class is designed for a single database; to use it, extend the
 *  class in your code, with the given database/user/password you will
 *  connect to. For multiple databases, derive additional classes.
 *
 *  Example of usage:
 *    class DB_Mysql extends DB {
 *        var $host = 'host.db.com';
 *        var $database = 'dbname';
 *        var $user = 'username';
 *        var $password = 'password';
 *        // etc...
 *    }
 *    $db =& new DB_Mysql;
 *    $db->query('SELECT * FROM table');
 *    while ( $db->next_record() ) {
 *        $field = $db->f('field');
 *        // etc...
 *    }
 *
 *  @author Jon Abernathy <hide@address.com>
 *  @version 2.0
 */
class DB
{
	/**
	 *  Host name of the server to connect to.
	 *
	 *  @var string $host
	 *  @access public
	 */
	var $host = '';

	/**
	 *  Name of the database to connect to.
	 *
	 *  @var string $database
	 *  @access public
	 */
	var $database = '';

	/**
	 *  User name used to connect to the server.
	 *
	 *  @var string $user
	 *  @access public
	 */
	var $user = '';

	/**
	 *  Password used to connect to the server.
	 *
	 *  @var string $password
	 *  @access public
	 */
	var $password = '';

	/**
	 *  Whether to automatically open a new link on mysql_connect().
	 *
	 *  @var bool $new_link
	 *  @access public
	 */
	var $new_link = false;

	/**
	 *  Flags whether to automatically call mysql_free_result().
	 *
	 *  @var bool $auto_free
	 *  @access public
	 */
	var $auto_free = false;

	/**
	 *  Toggle debug mode on or off.
	 *
	 *  @var bool $debug
	 *  @access public
	 */
	var $debug = false;

	/**
	 *  Defines what action to take when an error is encountered.
	 *
	 *  The following values are recognized:
	 *      yes: halt with message
	 *      no: ignore errors quietly
	 *      report: ignore error, but spit a warning
	 *      email: ignore error, but send email warning - supplemental
	 *          value as a comma-separated list - "yes,email"
	 *
	 *  @var string $halt_on_error
	 *  @see $email_on_error
	 *  @access public
	 */
	var $halt_on_error = 'yes';

	/**
	 *  Where to send error messages to.
	 *
	 *  @var string $email_on_error
	 *  @see $halt_on_error
	 *  @access public
	 */
	var $email_on_error;

	/**
	 *  Flags whether or not to use persistent database connections.
	 *
	 *  @var bool $pconnect
	 *  @access public
	 */
	var $pconnect = false;

	/**
	 *  Contains record result array.
	 *
	 *  @var array $Record
	 *  @access public
	 */
	var $Record = array();

	/**
	 *  Contains current error code from MySQL.
	 *
	 *  @var int $errno
	 *  @access public
	 */
	var $errno = 0;

	/**
	 *  Contains current error message from MySQL.
	 *
	 *  @var string $error
	 *  @access public
	 */
	var $error = '';

	/**
	 *  Contains count of how many queries have been performed.
	 *
	 *  @var int $query_count
	 *  @access public
	 */
	var $query_count = 0;

	/**
	 *  Contains current database link identifier.
	 *
	 *  @var int $__link_id
	 *  @access private
	 */
	var $_link_id;

	/**
	 *  Contains current database query identifier.
	 *
	 *  @var int $__query_id
	 *  @access private
	 */
	var $_query_id;


	/**
	 *  Constructor.
	 *
	 *  @param string $sql Optional SQL query to execute
	 */
	function DB( $sql = '' )
	{
		if ( !empty($sql) ) {
			$this->q($sql);
		}
	}

	/**
	 *  Connects to the database.
	 *
	 *  @param string $database Optional name of the database to connect to
	 *  @param string $host Optional database host address
	 *  @param string $user Optional database user
	 *  @param string $password Optional database password
	 *  @return mixed Link ID resource on success, FALSE on fail
	 */
	function connect( $database = '', $host = '', $user = '', $password = '' )
	{
		if ( !is_resource($this->_link_id) ) {
			if ( empty($database) ) {
				$database = $this->database;
			}
			if ( empty($host) ) {
				$host = $this->host;
			}
			if ( empty($user) ) {
				$user = $this->user;
			}
			if ( empty($password) ) {
				$password = $this->password;
			}
			
			if ( !$this->pconnect ) {
				$this->_link_id = mysql_connect($host, $user, $password, $this->new_link);
			} else {
				$this->_link_id = mysql_pconnect($host, $user, $password);
			}
			if ( !is_resource($this->_link_id) ) {
				$this->_halt('connect(' . $host . ', ' . $user .', [password]) failed.');
				return false;
			}
			if ( !mysql_select_db($database, $this->_link_id) ) {
				$this->_halt('cannot use database ' . $this->database);
				return false;
			}
		}
		
		return $this->_link_id;
	}

	/**
	 *  Closes the current database connection.
	 */
	function close()
	{
		if ( !$this->pconnect ) {
			mysql_close($this->_link_id);
		}
		$this->_link_id = null;
		return;
	}

	/**
	 *  Frees resources used by recent query.
	 */
	function free()
	{
		if ( is_resource($this->_query_id) ) {
			mysql_free_result($this->_query_id);
		}
		$this->_query_id = null;
		return;
	}

	/**
	 *  Performs the SQL query, updates the query count.
	 *
	 *  @param string $sql SQL query
	 *  @param bool $unbuffered Run query unbuffered?
	 *  @return mixed Query ID resource or false on problem
	 */
	function q( $sql, $unbuffered = false )
	{
		if ( $this->query($sql, $unbuffered) ) {
			$this->query_count++;
			return $this->_query_id;
		}
		return false;
	}

	/**
	 *  Performs the SQL query.
	 *
	 *  @param string $sql SQL query
	 *  @param bool $unbuffered Run query unbuffered?
	 *  @return mixed Query ID resource or false on problem
	 */
	function query( $sql, $unbuffered = false )
	{
		// No empty queries, please, since PHP4 chokes on them.
		if ( empty($sql) ) {
			return false;
		}
		
		if ( !$this->connect() ) {
			return false; // we already complained in connect() about that.
		}
		
		// New query, discard previous result.
		if ( $this->auto_free ) {
			$this->free();
		}
		
		if ( $this->debug ) {
			echo '<b>debug:</b> query = ' . $sql . '<br />';
		}
		
		if ( !$unbuffered ) {
			$this->_query_id = mysql_query($sql, $this->_link_id);
		} else {
			$this->_query_id = mysql_unbuffered_query($sql, $this->_link_id);
		}
		if ( !$this->_query_id ) {
			$this->_halt('Invalid SQL: ' . $sql);
		}
		$this->errno = mysql_errno($this->_link_id);
		$this->error = mysql_error($this->_link_id);
		
		// Will return nada if it fails. That's fine.
		return $this->_query_id;
	}

	/**
	 *  Passes the SQL query as unbuffered, updates the query count.
	 *
	 *  @param string $sql SQL query
	 *  @return mixed Query ID resource or false on problem
	 */
	function uq( $sql )
	{
		return $this->q($sql, true);
	}

	/**
	 *  Passes the SQL query as unbuffered.
	 *
	 *  @param string $sql SQL query
	 *  @return mixed Query ID resource or false on problem
	 */
	function uquery( $sql )
	{
		return $this->query($sql, true);
	}

	/**
	 *  Fetches next row in result set from query as an array, storing
	 *  result into the $Record array.
	 *
	 *  @param int $result_type One of the 3 MySQL constants indicating
	 *    the type of array to fetch:
	 *    MYSQL_ASSOC: Get associative array only
	 *    MYSQL_NUM: Get numeric-indexed array only
	 *    MYSQL_BOTH: Get combined (numeric & associative) array
	 *  @return bool True if successful, False if at the end
	 *    of the result set (or no results returned)
	 */
	function next_record( $result_type = MYSQL_ASSOC )
	{
		if ( !$this->_query_id ) {
			$this->_halt('next_record() called with no query pending.');
			return false;
		}
		
		switch ( $result_type ) {
			case MYSQL_ASSOC:
				$this->Record = mysql_fetch_assoc($this->_query_id);
				break;
			case MYSQL_NUM:
				$this->Record = mysql_fetch_row($this->_query_id);
				break;
			default: // MYSQL_BOTH
				$this->Record = mysql_fetch_array($this->_query_id, $result_type);
		}
		$this->errno = mysql_errno($this->_link_id);
		$this->error = mysql_error($this->_link_id);
		
		$status = is_array($this->Record);
		if ( !$status && $this->auto_free ) {
			$this->free();
		}
		return $status;
	}

	/**
	 *  Returns the number of affected rows from a query of type
	 *  UPDATE, INSERT, REPLACE, and DELETE.
	 *
	 *  @return int
	 */
	function affected_rows()
	{
		return mysql_affected_rows($this->_link_id);
	}

	/**
	 *  Returns a count of rows from the result of a SELECT query.
	 *
	 *  @return int
	 */
	function num_rows()
	{
		return mysql_num_rows($this->_query_id);
	}

	/**
	 *  Returns a count of fields from the result of a SELECT query.
	 *
	 *  @return int
	 */
	function num_fields()
	{
		return mysql_num_fields($this->_query_id);
	}

	/**
	 *  Returns the value of the field from the current row of the result
	 *  set.
	 *
	 *  @param string $name Name of the field
	 *  @return mixed
	 */
	function f( $name )
	{
		return $this->Record[$name];
	}

	/**
	 *  Returns the ID generated from a previous INSERT query.
	 *
	 *  @return int
	 */
	function insert_id()
	{
		return mysql_insert_id($this->_link_id);
	}
    
	/**
	 *  Escapes special characters according to the database's current
	 *  character set.
	 *
	 *  @param string $string Value to escape
	 *  @return string
	 */
	function escape( $string )
	{
		return mysql_real_escape_string($string, $this->_link_id);
	}

	/**
	 *  Returns database link ID resource.
	 *
	 *  @return resource
	 */
	function link_id()
	{
		return $this->_link_id;
	}

	/**
	 *  Returns database query ID resource.
	 *
	 *  @return resource
	 */
	function query_id()
	{
		return $this->_query_id;
	}

	/**
	 *  Generates/handles an error message and, if applicable, halts
	 *  the program.
	 *
	 *  @param string $msg Message to display
	 */
	function _halt( $msg )
	{
		$this->error = mysql_error($this->_link_id);
		$this->errno = mysql_errno($this->_link_id);
		
		if ( strpos($this->halt_on_error, 'email') !== false && !empty($this->email_on_error) ) {
			$message = strip_tags($this->_haltmsg($msg));
			mail($this->email_on_error, 'MySQL Database Error', $message);
		}
		
		if ( $this->halt_on_error == 'no' ) {
			return;
		}
		
		echo $this->_haltmsg($msg);
		
		if ( $this->halt_on_error != 'report' ) {
			die('<b>Halted.</b>');
		}
		return;
	}

	/**
	 *  Formats the given error message with additional system values.
	 *
	 *  @param string $msg Message to display
	 */
	function _haltmsg( $msg )
	{
		$output = '<br /><br /><b>Database error:</b> ' . $msg . '<br />' . "\n" .
			'<b>MySQL Error</b>: ' . $this->errno . ' (' . $this->error . ')<br />' . "\n\n" .
			'<b>Host:</b> ' . $this->host . '<br />' . "\n" .
			'<b>Database:</b> ' . $this->database . '<br />' . "\n" .
			'<b>User:</b> ' . $this->user . '<br />' . "\n" .
			'<b>Date:</b> ' . date('Y-m-d H:i:s') . '<br />' . "\n";
		return $output;
	}
}

?>
Return current item: MySQL Database Abstraction