class.QueryDB.php

<?php
	
	class QueryDB implements Iterator {
		
		private static $QUOTE_MATCH = "/(\".*(?<!\\\)\")|('.*(?<!\\\)')/Ue";
		
		private $stored_procedure = false; 
		private $quote_store;
		private $query_strings = array();
		
		private $row;
		private $current;
		
		protected $database;
		protected $result; 
		protected $compiled_query;
		protected $query;
		
		public function __construct( IDB $database ) {
			$this->database = $database;
			$this->row = 0;
		}
		
		public function __get( $attribute ) {
			$attribute_function = 'get_' . $attribute;
				
			if ( method_exists($this, $attribute_function ) ) {
				return $this->$attribute_function();
			
			} else {
				throw new Exception('Undefined attribute called '.get_class($this).'::'.$attribute);
			
			}
		}
		
		public function __set( $attribute, $value ) {
			
			$attribute_function = 'set_' . $attribute;
			
			if ( method_exists($this, $attribute_function ) ) {
				return $this->$attribute_function($value);
			
			} else {
				
				if ( method_exists($this, 'get_' . $attribute ) ) {
					throw new Exception('Read-only attribute called for writing: '.get_class($this).'::'.$attribute);
			
				} else {
					throw new Exception('Undefined attribute called '.get_class($this).'::'.$attribute);
				
				}
			
			}
			
		}
		
		private function get_database() { return $this->database; }
		private function get_compiled_query() { return $this->compiled_query; }
		 
		public function prepare( $query ) {
			$this->stored_procedure = true;
			$this->quote_store = array(); 
			$this->query = preg_replace(self::$QUOTE_MATCH, '$this->sql_quote_replace("\1"?"\1":\'\2\')', $query);
		}
	
		public function compile($params = '') {
			
			if (! $this->stored_procedure) {
				throw new Exception("Stored procedure has not been initialized");
			}
	
			$params = func_get_args(); 
			$query = preg_replace("/(?<!\\\\)\:(\d+)([SINC])/e", '$this->compile_callback($params, \1, "\2")', $this->query);
			
			return ($this->compiled_query = $this->add_strings($query)); 
		} 
	
		public function execute($queryParams = ''){
			
			$args = func_get_args();
			
			if ($this->stored_procedure) {
				$query = call_user_func_array(array($this, 'compile'), $args);
				
			} else {
				$this->compiled_query = $queryParams;
				$query = $queryParams;
				
			}
			
			$result = $this->database->query($query);
	
			if (! $result ) {
				throw new QueryException($this);
				
			}
	
			$this->result = $result;
			return $this;
			
		}  
		
		private function sql_quote_replace( $match ) {   
			$number = count($this->query_strings);
			$this->query_strings[] = $match;        
			return "$||$$number";
		}  
		
		private function add_strings($string){
			$numbers = array_keys($this->query_strings);
			$count = count($numbers);
	
			$searches = array();
	
			for($x = 0; $x < $count; $x++) {
				$searches[$x] = "$||\${$numbers[$x]}";
			}
	
			return str_replace($searches, $this->query_strings, $string);
    	} 
		
		protected function compile_callback($params, $index, $type) {        
			$index--;
			
			if (! isset($params[$index])) {
				throw new Exception("Required number of arguments not sent to stored procedure.");
			}
			
			
			switch ($type) {
				case 'S':
					if ( is_array ( $params[$index] ) ) {
						return "(" . $this->implode_with_security(", ",$params[$index], array($this->database, 'escape_string'), true) . ")"; 
						
					} else {
						return '\'' . $this->database->escape_string($params[$index]) . '\'';
						
					}
					
					break;
				
				case 'I':
					if ( is_array ( $params[$index] ) ) {
						return "(" . implode(", ",$params[$index]) . ")"; 
						
					} else {
						return (int) $params[$index];
						
					}
					
					break;
				
				case 'N':
					if ( is_array ( $params[$index] ) ) {
						return "(" . implode(", ",$params[$index]) . ")"; 
						
					} else {
						return (float) $params[$index];
						
					}
					
					break;
				
				case 'C':
					if ( is_array ( $params[$index] ) ) {
						return $this->implode_with_security(", ",$params[$index], array($this->database, 'escape_string'), false); 
						
					} else {
						return $params[$index];
						
					}
				
				default:
					throw new Exception("Unrecognised data type '$type' specified in stored procedure.");
			}
    	} 
		
		public function fetch_array() {
			$this->evaluate_result();
			return $this->database->fetch_array($this->result);
		}
	
		public function fetch_row() {
			$this->evaluate_result();
			return $this->database->fetch_row($this->result);
		}
	
		public function fetch_assoc() {
			$this->evaluate_result();
			return $this->database->fetch_assoc($this->result);
		}
	
		public function fetch_object() {
			$this->evaluate_result();
			return $this->database->fetch_object($this->result);
		}
	
		public function getNumRows() {
			$this->evaluate_result();
			return $this->database->getNumRows($this->result);
		}  
		
		public function seekRow( $row = 0 ) {
			$this->evaluate_result();
			
			if ( $row < $this->getNumRows() ) {
				$this->database->seekRow($this->result, $row);
			}
		}
		
		public function fill_object ( $object ) {
			
			$row = $this->fetch_assoc();
			
			$keys = array_keys($row);
			
			if ( isset ( $object->overrule ) && is_array($object->overrule) ) {
				
				foreach ( $keys as $key ) {
					
					if ( array_key_exists ($key, $object->overrule ) ){
						$object->{$object->overrule[$key]} = $row[$key];
						
					} else {
						$object->{$key} = $row[$key];
					
					}
				}
				
			} else {
			
				foreach ( $keys as $key ) {
					$object->{$key} = $row[$key];
				}
			
			}
			
		}
		
		private function evaluate_result() {
			if (! is_resource($this->result)) {
				throw new Exception('Query not executed.');
			}
			
		}
		
		private function implode_with_security ( $glue, array $array, $func_escape, $quotes = true ) {
			
			$t = '';
			
			if ( $quotes ) {
				$quote = "'";
			} else {
				$quote = '';
			}
				
			foreach ( $array as $value ) {
				$t .=  $glue . $quote . call_user_func($func_escape,$value) . $quote;
			}
			
			$t = substr($t, strlen($glue),strlen($t));
			
			return $t;
		
		}
		
		// Iterator Implementation
		public function current() {
			$this->seekRow($this->row);
			$this->current = $this->fetch_assoc();
			
			return $this->current;
		}
		
		public function rewind() {
			$this->current = NULL;
			$this->row = 0;
			$this->seekRow($this->row);
		}
		
		public function key() {
			return $this->row;
		}
		
		public function next() {
			$this->row++;
			$var = $this->fetch_assoc();
			return $var;
		}
		
		public function valid() {
			return $this->current !== false;
		}
		
	}

?>

class.MySQLDB.php
<?php
	
	class MySQLDB implements IDB {
		
		private $con_id;
		
		private $db;
		private $host;
		private $user;
		private $password;
		
		private $query_count;
		
		public function __construct( $host, $user, $password, $database = '' ) {
			
			$this->host = $host;
			$this->user = $user;
			$this->password = $password;
			
			$this->connect();
			
			if ( strlen($database) > 0 ) {
				$this->set_db($database);
			}
			
		}
		
		public function __destruct() {
		}
		
		public function __get( $attribute ) {
			$attribute_function = 'get_' . $attribute;
				
			if ( method_exists($this, $attribute_function ) ) {
				return $this->$attribute_function();
			
			} else {
				
				if ( method_exists($this, 'set_' . $attribute ) ) {
					throw new Exception(get_class($this) . '::' . $attribute . ' is write-only');
			
				} else {
					throw new Exception( get_class($this) . '::' . $attribute . ' undifined');
				
				}
			
			}
		}
		
		public function __set( $attribute, $value ) {
			
			$attribute_function = 'set_' . $attribute;
			
			if ( method_exists($this, $attribute_function ) ) {
				return $this->$attribute_function($value);
			
			} else {
				
				if ( method_exists($this, 'get_' . $attribute ) ) {
					throw new Exception( get_class($this) . '::' . $attribute . ' is read-only');
			
				} else {
					throw new Exception(get_class($this) . '::' . $attribute . ' undifined');
				
				}
			
			}
			
		}
		
		public static function getInstance( $host, $user, $password, $database = '' ) {
			static $old;
			
			if ( ! isset ( $old ) ) {
				$object = __CLASS__;
				$old = new $object( $host, $user, $password, $database );
			}
			
			return $old;
		}
		
		private function get_db() { return $this->db; }
		private function get_host() { return $this->host; }
		private function get_user() { return $this->user; }
		
		private function set_host ( $host ) { $this->host = $host; }
		private function set_user ( $user ) { $this->user = $user; }
		private function set_password ( $pass ) { $this->password = $pass; }
		
		private function set_db( $db ) { 
			
			$this->db = $db;
			
			if ( ! mysql_select_db( $db, $this->con_id ) ) {
				throw new Exception("Unable to select database");
			}
		
		}
		
		public function connect() {
			
			if ( ! $this->con_id = mysql_connect( $this->host, $this->user, $this->password ) ) {
				throw new Exception("Unable to connect to the MySQL database");
			}
			
		}
		
		public function disconnect() {
			
			if ( ! is_null ( $this->con_id ) ) {
				if ( ! mysql_close( $this->con_id ) ) {
					throw new Exception("Unable to close connection");
				}
				
				$this->con_id = NULL;
			}
			
		}
		
		public function getLastInsertId() {
			
			if( ! $id = mysql_insert_id( $this->con_id ) ){
				throw new Exception("Unable to get last insertion id");
				
			}
			
			return $id;
		}
		
		public function newQuery() {
			return new QueryDB( $this );			
		}
		
		public function getQueryCount() {
			return $this->query_count;
		}
		
		public function getAffectedNumRows() {
			
			$rows = mysql_affected_rows($this->con_id);
			return $rows == 0 ? false : $rows;
			
		}
		
		public function getNumRows( $result ) {
			
			if( ! $rows = mysql_num_rows( $result ) ){
				return false;
			}
			
			return $rows;
		}
		
		public function getErrorNumber() {
			return mysql_errno($this->con_id); 
		}
		
		public function getError() {
			return mysql_error($this->con_id); 
		}
		
		public static function escape_string( $string ) {
            return mysql_real_escape_string( $string );
        } 
		
		public function fetch_array( $result, $array_type = MYSQL_BOTH ) {
            return mysql_fetch_array( $result, $array_type );
        }

        public function fetch_row( $result ) {
            return mysql_fetch_row( $result );
        }
        
        public function fetch_assoc( $result ) {
            return mysql_fetch_assoc( $result );
        }
        
        public function fetch_object( $result ) {
            return mysql_fetch_object( $result );
        } 
		
		public function query($query){
			$this->query_count++;
            return mysql_query($query, $this->con_id);
        } 
		
		public function seekRow ( $result, $row = 0 ) {
			return mysql_data_seek($result, $row);
		}
		
	}

?>

class.IDB.php

<?php
	
	interface IDB {
		
		public function connect();
		public function disconnect();
		
		public static function getInstance( $host, $user, $password );
		
		public function getLastInsertId();
		public function newQuery();
		
		public function query( $query );
		
		public function getQueryCount();
		
		public function getAffectedNumRows();
		public function getNumRows( $result );
		
		public function getErrorNumber();
		public function getError();
		
		public static function escape_string( $string );
		
		public function fetch_array( $result );
		public function fetch_row( $result );
		public function fetch_assoc( $result );
		public function fetch_object( $result ); 
		
		public function seekRow( $row );
	}

?>

class.QueryException.php

<?php
	
	class QueryException extends Exception {
		
		protected $query;
		
		protected $query_text;
		protected $error_number;
		protected $error_message; 
		
		protected $debug = false;
		
		public function __construct( QueryDB $query, $debug = false ) {
			
			$this->query = $query;
			
			$backtrace = $this->GetTrace();
			
			if (count($backtrace) > 0) {
				$x = 0;
	
				while((! isset($backtrace[$x]['line'])) ||
					  (isset($backtrace[$x]['class']) && is_subclass_of($backtrace[$x]['class'], 'QueryDB')) ||
					  (strpos(strtolower(@$backtrace[$x]['function']), 'call_user_func')) !== false ) {
	
					++$x;
					
					if (($x) >= count($backtrace)) {
						$x = count($backtrace);
						break;
					}
				}               
		
				if ($x != 0) {
					$x -= 1;
				}
				
				$this->line = $backtrace[$x]['line'];
				$this->file = $backtrace[$x]['file'];
        	} 
			
			$this->query_text = $query->compiled_query;
        	$this->error_number = $query->database->getErrorNumber();
        	$this->error_message = $query->database->getError();
            
        	parent::__construct('Query Error', 0); 
			
		}
		
		public function __destruct() {
		}
		
		public function __get ( $attribute ) {
			
			$attribute_function = 'get_' . $attribute;
				
			if ( method_exists($this, $attribute_function ) ) {
				return $this->$attribute_function();
			
			} else {
				throw new Exception('Undefined attribute called '.get_class($this).'::'.$attribute);
			
			}
		}
		
		public function __set( $attribute, $value ) {
			
			$attribute_function = 'set_' . $attribute;
			
			if ( method_exists($this, $attribute_function ) ) {
				return $this->$attribute_function($value);
			
			} else {
				
				if ( method_exists($this, 'get_' . $attribute ) ) {
					throw new Exception('Read-only attribute called for writing: '.get_class($this).'::'.$attribute);
			
				} else {
					throw new Exception('Undefined attribute called '.get_class($this).'::'.$attribute);
				
				}
			
			}
			
		}
		
		public function __toString() {
			
			if ( $this->debug ) {
			 	$output = "Query Error in <strong>{$this->file}</strong> on line <strong>{$this->line}</strong><br />\r\n\r\n";
			 	$output .= "Query: <em>{$this->query_text}</em><br /><br />\r\n\r\n";
			 	$output .= "Error: {$this->error_message} ({$this->error_number})\n\n";  
			
			} else {
				$output = "An error has occured during query executing.<br />\r\n" ;
				$output .= "We are sorry for the inconvenience and we will restore it asap.";
			
			}       
				
			 return $output;
		} 
		
		private function get_query_text() { return $this->query_text; }
		private function get_error_number() { return $this->error_number; }
		private function get_error_message() { return $this->error_message; }
		
		private function get_debug() { return $this->debug; }
		private function set_debug( $debug ) { $this->debug = $debug; }
		
	}

?>

index.php ( om te testen )

<?php
	
	function __autoload( $class ) {
		require ('class.' . $class . '.php');
	}
	
	$connection = MySQLDB::getInstance($host,$user,$password,$database);
	$query = $connection->newQuery();
	
	$query->prepare('SELECT id, username FROM users 
                                  WHERE username = :1S OR id = :2I OR id IN :3I 
                                  OR username IN :3I ORDER BY id ASC');
	
    try {
		$query->execute('test', 4, array(1,2,6,9,12,14,36)); 
		
	} catch (QueryException $e) { 
		$e->debug = true;
		echo($e); 
		
	}
	
	foreach ( $query as $t) {
		print_r($t);
	}
	
?>

Om te testen moeten nog wel $host, $user, $password en $database worden aangemaakt.