File: database.php

<?php
/*
Written by Aziz S. Hussain
@
www.AzizSaleh.com
Produced under LGPL license
@
http://www.gnu.org/licenses/lgpl.html


# Class structure
class database ()
{
	protected databaseName, databaseHost, databaseUser, databasePassword
	protected cLink, mysqlError
	protected lastInsertID
	protected curReplace, arrayReplace
	
	
Public	void __construct(string databaseHost,string databaseUser,string databasePassword,string databaseName)
									# Connect to database based on constructed variables and store link in cLink
				
Public	resource query(sql as string, arrayReplace as array)
									# Executes mysql_query and stores lastInsertID, if any
									# If arrayReplace is set, it will search sql for any question marks
									# and replace them with matching index of arrayReplace
									
Private	string	doSwitch(string result)
									# This function returns the curReplace subset, used in query function
									
Public	array result(query as (string|object),isObject as boolean = false)
									# Querys the (query string|query object) and returns array or object result
									
Public	(array|string) cleanInput(array as (array|string)
									# Executes mysql_real_escape_string on the (array|string)
									# (array|string) also is escaped for inserting/adding using the ''

Public	(number|string) escape(fieldValue as (number|string), isNumber as boolean = false)
									# Execute mysql_real_escape_string if isNumber is true
									# Runs preg_replace instead
									
Public	void showError(string theError)
									# Outputs MySQL Error along with an end of line
	
Public	string getPrimaryField(string tableName)
									# Returns the primary field name of the specified table
									
Public	resource delete(string tableName,(string|array) keyDelete,string keyField = NULL)
									# This function is overloaded in three ways as:
Public	resource delete(string tableName,string keyDelete)
									# Will delete record value keyDelete using primary field
Public	resource delete(string tableName,string keyDelete,string keyField)
									# Will delete record value keyDelete using keyField field
Public	resource delete(string tableName,array keyDelete)
									# Will delete record based on multiple fieldname with associated fieldvalues
									
Public	resource insert(string tableName,array ARRAY_VARS)
									# This function will insert or update values of tableName from an array 
									# Like POST, GET, SESSION,...
									# The way this works, is that it retrieves a lits of fields for that table and checks
									# Them against the ARRAY_VARS. If the primary key exist in ARRAY_VARS, then it updates,
									# Otherwise it does an insert to the table
	
Public	string (mysql query) findMatches(string tableName,array ARRAY_VARS)	
									# Find relationship between tablename and ARRAY_VARS, create MySQL statement
									
Public	array (resource,string tablename) createTable(array ARRAY_VARS, string tableName = NULL, string uniqueName = null)
									# Create a database table based on ARRAY_VARS, if tableName is NULL a random string is
									# Generated to use for the table name
									# If unique name is specified, it is used as the unique key, otherwise, it will be primaryKey
									
Public	void close()				# Closes the database connection
	
Private	string generateRandom(integer strLen)
									# Generate a random string of strLen length
Public	resource getLink()			# Return current database connection link								
}																	
*/

// Check if class already called
if(class_exists('database')){ return;}

class database
{
	# Variables needed to connect
	protected $databaseName, $databaseHost, $databaseUser, $databasePassword;
	# Connection link and error holder
	public $cLink, $mysqlError;
	# Last insert ID (primary keys)
	protected $lastInsertID;
	# Holds the current ? replace
	protected $curReplace, $arrayReplace;
	
	# Connect to database upon creation
	public function __construct($databaseHost,$databaseUser,$databasePassword,$databaseName)
	{
		$this->cLink = @mysql_connect($databaseHost,$databaseUser,$databasePassword) 
				or $this->showError(mysql_error());

		# Select database
		if(is_resource($this->cLink))
		{
			@mysql_select_db($databaseName,$this->cLink) 
				or $this->showError(mysql_error($this->cLink));
		}
	}
	
	# Regular query with ? replace style
	public function query($sql,$arrayReplace = NULL)
	{		
		$this->curReplace = -1;
		
		$arrayReplace = $this->cleanInput($arrayReplace);
		$this->arrayReplace = $arrayReplace;
		
		if(isset($arrayReplace))
		{			
			if(is_array($arrayReplace))
			{
				$arrayReplaces = array_fill(0,count($arrayReplace),'?');
				$query = preg_replace_callback('/\?/Uism','database::doSwitch',$sql);				
			} else {
				$query = str_replace('?',$arrayReplace,$sql);
			}
		} else {
			$query = $sql;	
		}

		$queryLink = @mysql_query($query,$this->cLink) or $this->showError(mysql_error($this->cLink));
		return $queryLink;
	}
	
	# This is a helper function to preg_replace all question marks
	private function doSwitch($result)
	{
		$this->curReplace++;
		return $this->arrayReplace[$this->curReplace];		
	}
	
	# Returns array or object result set assoc of an object or string query
	public function result($query,$isObject = false)
	{
		if(!is_resource($query)){ $query = $this->query($query);}		

		$records = array();
		while($eachRecord = @mysql_fetch_assoc($query)){ $records[] = $eachRecord;}

		if($isObject == true)
		{
			$records = (object) $records;
		} 
		return $records;
	}
	
	# Sanitize an array & organize into mysql style ''
	public function cleanInput($array)
	{
		if(!isset($array)){ return;}
		if(!is_array($array)){ return "'".$this->escape($array)."'";}
		
		$newArray = array();
		foreach($array as $item)
		{
			$newArray[] = "'".$this->escape($item)."'";	
		}
		return $newArray;
	}
	
	# Sanitize input for Query
	public function escape($fieldValue,$isNumber = false)
	{
		if($isNumber == true)
		{
			return preg_replace('/[^0-9\.]/iUsm','',$fieldValue);
		} else {
			return mysql_real_escape_string($fieldValue,$this->cLink);
		}
	}
	
	# Show error sent
	public function showError($theError)
	{		
		echo $this->mysqlError = $theError;
		echo PHP_EOL;
	}
	
	# Return the primary key of the specified table
	public function getPrimaryField($tableName)
	{
		$result = $this->query("SELECT k.column_name
			FROM   information_schema.key_column_usage as k
			WHERE  table_schema = schema()
			AND    constraint_name = 'PRIMARY'
			AND    table_name = '".$tableName."'");
		list($theKeyField) = $this->result($result);
		return $theKeyField['column_name'];
	}
	
	# Delete a record based on one keyfield (leave empty to do primary) that matches keyDelete
	# To delete a record based on a number of fields, pass the array to keyDelete
	public function delete($tableName,$keyDelete,$keyField = NULL)
	{
		# if keyDelete in form of array = array('fieldname' => 'fieldvalue','fieldname' => 'fieldvalue'...)
		if(is_array($keyDelete))
		{
			$queryAdd = 'WHERE ';
			foreach($keyDelete as $field => $value)
			{
				$queryAdd .= "`$field` = '$value' AND";
			}
			$queryAdd = substr($queryAdd,0,strlen($queryAdd)-4);
			$query = "DELETE FROM `$tableName` $queryAdd";
			return $this->query($query);
		}
		if($keyField == NULL){ $keyField = $this->getPrimaryField($tableName);}
		$query = "DELETE FROM `$tableName` WHERE `$keyField`='$keyDelete'";
		return $this->query($query);
	}
	
	# Insert/update into table values from ARRAY
	# variable names must match those found on table
	# Primary must exist for an update
	public function insert($tableName,$ARRAY_VARS)
	{
		# Check if we are sending anything?
		if($ARRAY_VARS == NULL){ return;}		
		# Return fields matches between array and table structure
		$queryDo = $this->findMatches($tableName,$ARRAY_VARS,'INSERT');
		# Do query		
		return $this->query($queryDo);
	}

	# Find matches
	public function findMatches($tableName,$ARRAY_VARS)
	{
		# We need two arrays to store fields/values
		$arrayFields = array();
		$arrayFieldValue = array();
		$arrayUpdates = array();
		
		# Primary field (will update if found, otherwise insert)
		$primaryField = $this->getPrimaryField($tableName);
		$primaryFound = false;
		
		# Get list of fields for the table
		$tableFields = $this->result("SHOW COLUMNS FROM `$tableName`",'assoc');

		foreach($tableFields as $fieldInfo)
		{
			# Check if the field exist in ARRAY_VARS
			if(array_key_exists($fieldInfo['Field'],$ARRAY_VARS))
			{
				$fieldNameFormatted = "`".$fieldInfo['Field']."`";
				$fieldValueFormatted = "'".$this->escape($ARRAY_VARS[$fieldInfo['Field']])."'";
				$arrayFields[] = $fieldNameFormatted;
				$arrayFieldValue[] = $fieldValueFormatted;
				if($fieldInfo['Field'] == $primaryField && $ARRAY_VARS[$primaryField] > 0){
					# Insure parimary field is a number
					settype($ARRAY_VARS[$primaryField],"integer");
					$primaryFound = true;
				}
				$arrayUpdates[] = $fieldNameFormatted.' = '.$fieldValueFormatted;
			}
		}
		
		# Are we doing insert or update
		if($primaryFound == TRUE)
		{
			$finalQuery = "UPDATE `$tableName` SET ".implode(',',$arrayUpdates);
			$finalQuery .= " WHERE `$primaryField`='".$ARRAY_VARS[$primaryField]."'";
		} else {
			$finalQuery = "INSERT INTO `$tableName` (".implode(',',$arrayFields).") VALUES (".implode(',',$arrayFieldValue).")";
		}
		return $finalQuery;
	}
	
	# Create table based on Array Schema
	public function createTable($ARRAY_VARS,$tableName = NULL, $uniqueName = NULL)
	{
		# If no table name sent, generate one
		if($tableName == NULL){ $tableName = $this->generateRandom(4);}
		
		# Check unique field name
		if($uniqueName == NULL){ $uniqueName = 'primaryKey';}
		# Setup query
		$createQuery = "CREATE TABLE `$tableName` (
		`$uniqueName` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,";
		
		# Get fieldnames to add to creation query
		foreach(array_keys($ARRAY_VARS) as $fieldName)
		{
			$createQuery .= "
			`$fieldName` VARCHAR( 255 ) NOT NULL ,";		
		}
		$createQuery = substr($createQuery,0,strlen($createQuery)-1);
		$createQuery .= "
		);";

		$result = $this->query($createQuery);
		return array($result,$tableName);
	}
	
	# Close database connection
	public function close()
	{
		@mysql_close($this->cLink) or $this->showError(mysql_error($this->cLink));
	}
	
	# Generate a random number at x length
	private function generateRandom($strLen)
	{
  		return substr(md5(uniqid(rand(),1)),1,$strLen);
	}
	
	# Get current link
	public function getLink()
	{
		return $this->cLink;
	}
}

// End of file /Database.php