This class is a database abstraction class for MySQL, based on PHPLib.
<?php
/*************************************************************************
* *
* class.db.inc *
* *
*************************************************************************
* *
* MySQL database abstraction class adapted from PHPLib *
* *
* Copyright (c) 2008 Jon Abernathy <jon@chuggnutt.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, jon@chuggnutt.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 <jon@chuggnutt.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;
}
}
?>