<?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);
}
}
?>