PHP Classes

File: database.php

Recommend this page to a friend!
  Classes of Aziz S. Hussain   Database Management Class   database.php   Download  
File: database.php
Role: Class source
Content type: text/plain
Description: Main Class
Class: Database Management Class
Execute MySQL queries using lists of parameters
Author: By
Last change:
Date: 13 years ago
Size: 10,876 bytes


Class file image Download
<?php /* Written by Aziz S. Hussain @ Produced under LGPL license @ # 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