Location: PHPKode > scripts > MySQLConnection > mysqlconnection/database.php
<?php
require_once('validator.php');

/**
* MySQLConnection class
*
* Encapsulates php's MySQL connectivity functions.
* 
* You might use these classes under you own risk, there is no warranty!
* Using this class in a commercial applications is not recommended at this beta version
* 
* Your comments and bug reports are highly appreciated. Just drop me an email ;)
* 
* I did my tests using MySQL version 4.0.18-nt
*
* @version beta
* @author Hamid Hossain <hide@address.com>
*/
class MySQlConnection {
	/**
	* Holds connection's link identifier.
	*
	* @access private
	* @var resource
	*/
	var $link_id;

	/**#@+
	* @access private
	* @var string
	*/
	var $hostname;
	var $username;
	var $password;
	/**#@-*/

	/**
	* @access private
	* @var boolean
	*/
	var $persistent;

	/**
	* @see FetchDatabase()
	* @see RewindDatabaseCursor()
	* @access private
	* @var integer
	*/
	var $database_cursor = 0;

	/**
	* Constructs and connects to the MySQL server.
	* 
	* @param string
	* @param string
	* @param string
	* @param boolean
	*/
	function MySQLConnection($hostname, $username, $password, $persistent=true) {
		$this->hostname   = $hostname;
		$this->username   = $username;
		$this->password   = $password;
		$this->persistent = $persistent;
		
		$this->Connect();
	}

	/**
	* Establishes a connection to a MySQL server.
	* 
	* This function encapsulates mysql_connect() and mysql_pconnect() functions.
	* If the function successed in connecting, the link identifier returned will be
	* saved in link_id private property. Otherwise, an error message will appear
	* and the page execution will be terminated.
	* 
	* @see IsConnected()
	* @see Disconnect()
	* @uses mysql_connect()
	* @uses mysql_pconnect()
	*/
	function Connect() {
		if ($this->persistent) {
			$this->link_id = @mysql_pconnect($this->hostname, $this->username, $this->password);
		} else {
			$this->link_id = @mysql_connect ($this->hostname, $this->username, $this->password);
		}
		
		if (!$this->IsConnected()) {
			die('Unable to connect to server. ' . mysql_error());
		}
	}

	/**
	* Disconnects the server connection and frees the reserved memory.
	*
	* Works only if the connection is not persistent.
	*
	* @see Connect
	* @see IsConnected()
	* @uses mysql_close()
	* @return boolean TRUE on success or FALSE on failure
	*/
	function Disconnect() {
		if (!$this->persistent) {
			$temp = mysql_close($this->link_id);
			$this->link_id = false;
			return $temp;
		}
	}

	/**
	* Determines if connected to the server or not.
	* 
	* @see Connect()
	* @see Disconnect()
	* @return boolean
	*/
	function IsConnected() {
		return is_resource($this->link_id);
	}
	
	
	/**
	* Returns MySQL version.
	* 
	* @return string
	*/
	function Version() {
		$rs = $this->Query('SELECT VERSION()');
		return $rs->Result();
	}

	/**
	* Eexecutes a SQL statment and returns the result as a resource.
	*
	* Only for SELECT,SHOW,EXPLAIN or DESCRIBE statements Query() returns a resource
	* identifier or FALSE if the query was not executed correctly. For other type of
	* SQL statements, Query() returns TRUE on success and FALSE on error.
	* 
	* A non-FALSE return value means that the query was legal and could be executed
	* by the server. It does not indicate anything about the number of rows affected
	* or returned. It is perfectly possible for a query to succeed but affect no rows
	* or return no rows.
	*
	* WARNING: read about mysql_unbuffered_Query() in the PHP documentation before
	* setting unbuffered parameter to true.
	* 
	* @uses mysql_query()
	* @uses mysql_unbuffered_query()
	* @param string
	* @param boolean
	* @return RecordSet|boolean
	*/
	function & Query($sql, $unbuffered=false) {
		if ($unbuffered) {
			$query_result = mysql_unbuffered_query($sql, $this->link_id);
		} else {
			$query_result = mysql_query($sql, $this->link_id);
		}
		
		if (is_bool($query_result)) {
			return $query_result;
		}

		$rs = new RecordSet($this, $query_result);
		return $rs;
	}

	/**
	* Selects the given database.
	* 
	* @uses mysql_select_db()
	* @param string
	* @return Database
	*/
	function & UseDatabase($database) {
		if (mysql_select_db($database, $this->link_id)) {
			$db = new Database($this, $database);
		} else {
			die('Unable to select database: ' . mysql_error());
		}
		
		return $db;
	}
	
	function Databases() {
		$rs = $this->Query('SHOW DATABASES', true);
		while($row = $rs->FetchArray()){
			$dbs[] = $row[0];
		}
		$rs->FreeResult();
		
		return $dbs;
	}

	/**
	* Determines if database name is already exists in the server or not.
	* 
	* @return boolean
	*/
	function DatabaseExists($database) {
		if (array_search($database, $this->Databases()) > -1) {
			return true;
		}
	}
	
	/**
	* Returns a Database() object. This should be used with a while loop
	* to retreive all databases in the connected server.
	* 
	* @return Database
	*/
	function & FetchDatabase() {
		$arr = $this->Databases();

		if ($this->database_cursor > count($arr)-1) {
			return false;
		}

		$db = new Database($this, $arr[$this->database_cursor]);
		$this->database_cursor++;
		$this->UseDatabase($db->name);
		return $db;
	}

	/**
	* Reset the cursor of the FetchDatabase method to the begining.
	* 
	* @see FetchDatabase()
	*/
	function RewindDatabaseCursor() {
		$this->database_cursor = 0;
	}

	/**
	* Creates a new database
	* 
	* @return boolean TRUE if success, FALSE on failure
	*/
	function CreateDatabase($database)	{
		return $this->Query("CREATE DATABASE `$database`");
	}
	
	function ErrorMessage() {
		return mysql_error($this->link_id);
	}
	
	function ErrorNumber() {
		return mysql_errno($this->link_id);
	}
}

class Database {
	/**#@+
	* @access private
	*/

	/**
	* @var string
	*/
	var $name;
	
	/**
	* @var object MySQLConnection
	*/
	var $con;

	/**
	* @var integer
	*/
	var $table_cursor = 0;
	
	/**#@-*/
	
	/**
	* indicates if an error occures in validation.
	*
	* @access private
	* @var boolean
	*/
	var $validation_error;

	/**#@+
	* @access private
	* @var string
	*/
	var $format_date = 'Y-m-d';
	var $fromat_time = 'H:i:s';
	var $format_datetime = 'Y-m-d H:i:s';
	/**#@-*/

	/**
	* Constructs the class. loads the private properties with values.
	*
	* @param object MySQLConnection
	* @param string database name
	*/
	function Database(&$con, $name) {
		$this->con  = &$con;
		$this->name = $name;
	}
	
	/**
	* Alias to MySQLConnection::Query()
	* 
	* @param string
	* @param boolean
	* @return RecordSet|boolean
	*/
	function Query($sql, $unbuffered=false) {
		return $this->con->Query($sql, $unbuffered);
	}

	/**
	* Returns an Array of tables' names for the connected database
	* 
	* @return array
	*/
	function & Tables() {
		$rs = $this->con->Query('SHOW TABLES', true);
		while($row = $rs->FetchArray()){
			$tables[] = $row[0];
		}
		$rs->FreeResult();
		
		return $tables;
	}
	
	/**
	* Determines if table name is already exists in the database or not.
	* 
	* @return boolean
	*/
	function TableExists($table) {
		if (array_search($table, $this->Tables())>-1) {
			return true;
		}
	}
	
	/**
	* Returns a Table() object. This should be used with a while loop
	* to retreive all tables in the database.
	* 
	* @return object
	*/
	function & FetchTable() {
		$arr = $this->Tables();

		if ($this->table_cursor > count($arr)-1) {
			return false;
		}

		$table = new Table($this->con, $arr[$this->table_cursor]);
		$this->table_cursor++;
		return $table;
	}
	
	/**
	* Reset the cursor of the FetchTable method to the begining.
	*/
	function RewindTableCursor() {
		$this->table_cursor = 0;
	}
}

class Table {
	var $name;
	var $con;
	var $properties;
	var $field_cursor = 0;
	
	function Table(&$con, $name) {
		$this->con = &$con;
		$this->name = $name;
		
		$rs = $this->con->Query("SHOW TABLE STATUS LIKE '$this->name'");
		if ($rs->NumRows()) {
			$this->properties = $rs->FetchAssoc();
		}
	}
	
	function Type() {
		return $this->properties['Type'];
	}
	
	function DataSize() {
		return $this->properties['Data_length'];
	}
	
	function RowFormat() {
		return $this->properties['Row_format'];
	}
	
	function RecordsCount() {
		return $this->properties['Rows'];
	}
	
	function CreateTime() {
		return $this->properties['Create_time'];
	}
	
	function UpdateTime() {
		return $this->properties['Update_time'];
	}
	
	function Comments() {
		return $this->properties['Comment'];
	}
	
	/**
	* Converts an array to a delimited string.
	*
	* Give an array variable
	*
	* @access private 
	* @param array indexed array values
	* @param string quote to surround list items with
	* @param boolean whether to return quoted numbers or not
	* @return string
	*/
	/*
	function ArrayToList($array, $delimiter = ',', $quote = "'", $quote_numbers = false) {
		$result = '';
		foreach($array as $value) {
			// if not empty then put the delimiter string
			if (strlen($result)) {
				$result .= $delimiter;
			}
			// if $quote_numbers is false then don't use quotes with numeric values
			if (is_numeric($value) && !$quote_numbers) {
				$result .= $value;
			} else {
				$result .= $quote . $value . $quote;
			}
		}
		return $result;
	}
	*/
	
	/**
	* Returns an array contains table's primary fields.
	* 
	* @return array
	*/
	function PrimaryKeys() {
		$rs = $this->con->Query("DESC $this->name", true);
		while ($row = $rs->FetchAssoc()) {
			if ($row['Key'] == 'PRI') {
				$primary[] = $row['Field'];
			}
		}
		$rs->FreeResult();
		
		return $primary;
	}

	/**
	* Returns an array contains all tables' fields.
	*
	* @return array
	*/
	function & Fields() {
		$rs = $this->con->Query("SHOW COLUMNS FROM $this->name", true);
		while ($row = $rs->FetchAssoc()) {
			$fields[] = $row['Field'];
		}
		$rs->FreeResult();

		return $fields;
	}
	
	/**
	* Determines if field name is already exists in the table or not.
	* 
	* @param string
	* @return boolean
	*/
	function FieldExists($field) {
		if (array_search($field, $this->Fields())>-1) {
			return true;
		}
	}

	function FetchField() {
		$fields = $this->Fields();
		$count = count($fields);

		if ($this->field_cursor > $count-1) {
			return false;
		}

		$rs = $this->con->Query("SELECT * FROM $this->name LIMIT 0,1");				
		if ($rs->NumRows()) {
			$field = new Field($this->con, $this->name, $fields[$this->field_cursor]);
			$this->field_cursor++;
			return $field;
		}
	}

	/**
	* indecates if a record primary key is already in use.
	* 
	* Use this function to findout if a primary-key value is already inserted.
	* 
	* @param array|string primary key value(s)
	*/
	function RecordExists($primary_values) {
		if (!is_array($primary_values)) {
			$primary_values = array($primary_values);
		}

		$primary_keys = $this->PrimaryKeys();
		if (count($primary_values) != count($primary_keys)) {
			trigger_error('Primary fields are not correct');
			return false;
		}
	
		$stack = '';
		for ($i = 0; $i < count($primary_keys); $i++) {
			if (strlen($stack)) {
				$stack .= ' AND ';
			}
			$stack .= "UPPER(". $primary_keys[$i] .")=UPPER('" . $primary_values[$i] . "')";
		}

		$rs = $this->con->Query("SELECT COUNT(*) FROM $this->name WHERE $stack");

		return $rs->Result();
	}

	/**
	* Inserts a new record into a table existing in the selected database.
	* 
	* <code>
	* $db = new Database('localhost', 'mydatabase' ,'root' ,'');
	* 
	* $table = new Table('tablename');
	* $table->RecordInsert($_POST);
	* </code>
	* 
	* The previous example will insert all fields in the POST global array
	* into the table.
	* 
	* If the primary key of the table is an auto-number, this function will return
	* the last inserted autonumber.
	* 
	* @param array associative array contains fieldname=value
	* @return integer 
	*/
	function RecordInsert($fields_values) {
		$key_list = '';
		$val_list = '';
		$allowed_fields = $this->Fields(); 
		// validate all fields
		$validator = new Validator($fields_values);

		if (!$validator->ValidationError()) {
			foreach ($fields_values as $key => $value) {
				if (array_search($key, $allowed_fields) > -1) {
					if (strlen($key_list)) {
						$key_list .= ', ';
						$val_list .= ', ';
					}

					$key_list .= $key;
					$val_list .= "'$value'";
				}
			}

			$sql = "INSERT INTO $this->name ($key_list) VALUES ($val_list)";
			$rs = $this->con->Query($sql);

			return $rs->LastInsertedID();
		}
	}

	/**
	* Updates an existing record from a table.
	* 
	* <code>
	* $db = new Database('localhost', 'mydatabase' ,'root' ,'');
	* $table = new Table('tablename');
	* $table->RecordUpdate($_POST);
	* </code>
	* 
	* The previous example will update all fields in the post global variable into the database.
	* 
	* No need to worry about the primary key field. Just pass it within the array parameter.
	* It will be detected automatically.
	* 
	* @param array associative array contains fieldname=value
	*/
	function RecordUpdate($fields_values) {
		$primary = $this->PrimaryKeys();
		$allowed_fields = $this->Fields();
		$update_fields = '';
		// validate all fields
		$validator = new Validator($fields_values);

		if (!$validator->ValidationError()) {
			foreach ($fields_values as $key => $value) {
				if (array_search($key, $allowed_fields) > -1) {
					if (array_search($key, $primary) > -1) {
						if (strlen($where)) {
							$where .= ' AND ';
						}
						$where = "$key = '$value'";
					} else {
						if (strlen($update_fields)) {
							$update_fields .= ', ';
						}
						$update_fields .= "$key = '$value'";
					}
				}
			}

			$this->con->Query("UPDATE $this->name SET $update_fields WHERE $where");
		}
	}	
	
	/**
	* Deletes one or more records from the table.
	* 
	* @param string|array primary key value(s)
	*/
	function RecordDelete($primary_values) {
		if (!is_array($primary_values)) {
			$primary_values = array($primary_values);
		}

		$primary_keys = $this->PrimaryKeys();
		if (count($primary_values) != count($primary_keys)) {
			trigger_error('Primary fields are not correct');
			return false;
		}
	
		$stack = '';
		for ($i = 0; $i < count($primary_keys); $i++) {
			if (strlen($stack)) {
				$stack .= ' AND ';
			}
			$stack .= "UPPER(". $primary_keys[$i] .")=UPPER('" . $primary_values[$i] . "')";
		}

		$this->con->Query("DELETE FROM $this->name WHERE $stack");
	}
}

class Field {
	var $name;
	var $con;
	var $table;
	var $properties;
	
	function Field(&$con, &$table, $name) {
		$this->con   = &$con;
		$this->table = $table;
		$this->name  = $name;
		
		$rs = $this->con->Query("SHOW COLUMNS FROM $this->table LIKE '$this->name'");
		if ($rs->NumRows() == 1) {
			$this->properties = $rs->FetchAssoc();
		}
	}
	
	/**
	* @return boolean
	*/
	function IsPrimaryKey() {
		return $this->properties['Key'] == 'PRI';
	}

	/**
	* @return string
	*/
	function Type() {
		return $this->properties['Type'];
	}
	
	/**
	* @return boolean
	*/
	function IsAutoIncrement() {
		return $this->properties['Extra'] == 'auto_increment';
	}
	
	/**
	* @return boolean
	*/
	function IsNull() {
		return $this->properties['Null'] == 'YES';
	}
	
	/**
	* @return boolean
	*/
	function IsUnique() {
		return $this->properties['Key'] == 'UNI';
	}
	
	/**
	* @return string
	*/
	function DefaultValue() {
		return $this->properties['Default'];
	}

	/**
	* Returns an array contains all possible values of an enumerated field (enum).
	* 
	* @param string Column name
	* @return array
	*/
	function & EnumValues() {
		if (strpos($this->properties['Type'], 'enum') < 0) {
			return false;
		}
		
		$enum_values = $this->properties['Type'];
		$enum_values = substr($enum_values, 6, strlen($enum_values)-8);
		$enum_values = str_replace("','", ",", $enum_values);
		$enum_values = explode(',', $enum_values);

		// if options found then return the array
		if (count($enum_values) > 1) {
			return $enum_values;
		}
	}
}

class RecordSet {
	/**#@+
	* @access private
	* @var resource
	*/
	var $con;
	var $resource;
	/**#@-*/

	/**
	* Class constructor function
	*
	* @param resource Database connection identifier.
	* @param resource Database::Query() or Database::UnbufferedQuery result
	*/
	function RecordSet(&$con, &$resource) {
		$this->con      = &$con;
		$this->resource = &$resource;
	}

	/**
	* Returns detailed information about the RecordSet
	* 
	* @uses mysql_info()
	* @return string
	*/
	function info() {
		return mysql_info($this->con->link_id);
	}
	
	/**
	* Returns an array contains all fields in the RecordSet associated with
	* their properties' details.
	* 
	* @uses mysql_fetch_field()
	* @return array
	*/
	function & FetchFields() {
		while ($meta = mysql_fetch_field($this->resource)) {
			$fields[$meta->name] = array (
				'table'        =>$meta->table,
				'primary_key'  =>$meta->primary_key,
				'multiple_key' =>$meta->multiple_key,
				'unique_key'   =>$meta->unique_key,
				'type'         =>$meta->type,
				'unsigned'     =>$meta->unsigned,
				'numeric'      =>$meta->numeric,
				'max_length'   =>$meta->max_length,
				'not_null'     =>$meta->not_null,
				'zerofill'     =>$meta->zerofill,
				'blob'         =>$meta->blob
			);
		}
		return $fields;
	}

	/**
	* Frees memory associated with the RecordSet.
	* 
	* @uses mysql_free_result()
	* @return boolean
	*/
	function FreeResult() {
		$free = mysql_free_result($this->resource);
		$this->resource = false;
		return $free;
	}

	/**
	* Returns the number of rows in the RecordSet.
	*
	* This function is only valid for SELECT statements. To retrieve the number
	* of rows affected by INSERT, UPDATE or DELETE query, use AffectedRows().
	*
	* @see AffectedRows()
	* @uses mysql_num_rows()
	* @return integer number of rows in the result
	*/
	function NumRows() {
		return mysql_num_rows($this->resource);
	}

	/**
	* Returns the number of rows affected by the last INSERT, UPDATE or DELETE statement.
	*
	* If used with SELECT statement, -1 will be returned.
	* If the last query was a DELETE query with no WHERE clause, all of the records will
	* be deleted from the table but this function will return zero.
	*
	* @see NumRows()
	* @uses mysql_affected_rows()
	* @return integer the number of rows in a result set
	*/
	function AffectedRows() {
		return mysql_affected_rows($this->con->link_id);
	}

	/**
	* Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
	*
	* Use this function to fetch a resource into an associative array, numeric array or both.
	*
	* @see FetchObject()
	* @see FetchAssoc()
	* @see Result()
	* @uses mysql_fetch_array()
	* @param integer a value of MYSQL_ASSOC, MYSQL_NUM, or MYSQL_BOTH
	* @return array
	*/
	function & FetchArray($fetch_type = MYSQL_NUM) {
		return mysql_fetch_array($this->resource, $fetch_type);
	}
	
	/**
	* Returns an object with properties that correspond to the fetched row,
	* or FALSE if there are no more rows.
	*
	* FetchObject() is similar to FetchArray(), with one difference - an object is returned
	* instead of an array. Indirectly, that means that you can only access the data by the field names,
	* and not by their offsets (numbers are illegal property names).
	*
	* @see FetchArray()
	* @see FetchAssoc()
	* @see Result()
	* @uses mysql_fetch_object()
	* @return object
	*/
	function & FetchObject() {
		return mysql_fetch_object($this->resource);
	}

	/**
	* Returns an associative array that corresponds to the fetched row, or FALSE 
	* if there are no more rows.
	*
	* @see FetchArray()
	* @see FetchObject()
	* @see Result()
	* @uses mysql_fetch_assoc()
	* @return array
	*/
	function & FetchAssoc() {
		return mysql_fetch_assoc($this->resource);
	}

	/**
	* Returns the contents of one cell from a MySQL result set.
	* 
	* Warning: Don't use it with Database::UnbufferedQuery() function
	*
	* @see FetchArray()
	* @see FetchObject()
	* @see FetchAssoc()
	* @uses mysql_result()
	* @param integer numeric offset of a record. Starts from 0
	* @return string
	*/
	function Result($row = 0) {
		return mysql_result($this->resource, $row);
	}

	/**
	* Returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query
	*
	* LastInsertedID() returns 0 if the previous query does not generate
	* an AUTO_INCREMENT value.
	*
	* @return integer
	*/
	function LastInsertedID() {
		return mysql_insert_id($this->con->link_id);
	}
}
?>
Return current item: MySQLConnection