File: mysqli_class.php

<?php
/**
 * @author atabak.h@gmail.com
 * ah_framework
 * Copyright 2010-2014 webicdesign.net
 * atabak hosein nia production
 * _mysqli.php mysqli database connection and execute statement class
 */
class _ah_mysqli extends _ah_boot
{
	# mysqli connection link
	private $con;
	
	# mysqli query result
	private $res;
	
	# kill class function
	function __destruct() {
		
		# kill connection
		self::close();
		
	}
	
	# connect to databae
	private function connect () {
		
		# check for first time connection create
		if ( !isset ( $this->con ) || ! is_resource ( $this->con )) {
			
			# include configration for database connection
			include _CFG;
			
			try { // try connect to database
				
				# connect to database
				$this->con = new mysqli ( 
									$_ah_config ['mysql'] ['host'], 
									$_ah_config ['mysql'] ['user'], 
									$_ah_config ['mysql'] ['pass'], 
									$_ah_config ['mysql'] ['db'] 
				);
				
				# set utf-8 for charset
				$this->con->set_charset ( "utf8" );
				
			} catch ( Exception $e ) { // connection problem
				
				# create error in main class
				$this->error  = array ( 'type' => 'mysqlconnect', 'error' => $e->getMessage () );
				
				# return false for not connect
				return FALSE;
			}
		}
		
		# return tru if connection live
		return TRUE;
	}
	
	# close database connection
	private function close () {
		
		# check for connection first
		if ( isset ( $this->con ) && is_resource ( $this->con ) ) {
			
			# close mysql link
			mysqli_close ( $this->con );
			
			# null connection link
			$this->con = NULL;
			
			# unset connection
			unset ( $this->con );
		}
		
		return TRUE;
	}
	
	# cleare variable
	public  function civ ( $var ) {
				
		if ( is_numeric ( $var ) ) { # if variable is number then return that
			
			# put number variable to exit
			$string = $var;
			
		} else { #variable not number
			
			// variable prepear
			
			# if magic quotes is enable
			if ( get_magic_quotes_gpc () ) {
				
				# add stri bslash function to variable
				$var = stripslashes ( $var );
			}
			// scape hack 
			if ( !isset ( $this->con ) ) { # connection link not valid or dead
				
				# connect to database
				self::connect ();
				
				# scape dangrous string 
				$string = $this->con->real_escape_string ( $var );
				
				# close connection
				//self::close ();
				
			} else { # connection link id live
				
				# scape dangrous string
				$string = $this->con->real_escape_string ( $var );
			}
		}
		# return clean string
		return $string;
	}
	
	# free mysqli memory from server
	private function free () {
		
		# check for database connection
		if ( !isset ( $this->con ) ) {
			self::connect ();
		}
		
		# free memory
		$this->res->free ();
		
		# close connection after free memory
		self::close();
		
		return TRUE;
	}
	
	# field scpae main function
	public function field_scape ( $fields ) {
		
		// chekc field type
		if ( !is_array ( $fields ) ) { # field pass is not array
			
			# check for , for multi field
			if ( strpos ( $fields, ',' ) !== false ) { # string have multi fields
				
				# create array of field
				$field_array = explode( ',', $fields );
				
				# run function again for array
				self::field_scape ( $field_array );
				
			} else { # string have only 1 field
				
				# return curent field with seperator
				return self::field_creator ( $fields );
				
			}
			
		} else { # field pass is array
			
			$fld = '';
			
			# add seperator to field
			foreach ( $fields as $field ) {
				
				$fld .= self::field_creator ( $field ) . ', ';
			}
			
			# return field list
			return substr ( $fld, 0, - 2 );
			
		}
	}
	
	# field generate
	private function field_creator ( $fld ) {
		
		# add ' after annd before the field
		return " `" . str_replace ( ".", "`.`", self::civ ( $fld ) ) . "` ";
		
	}
	
	# value scpae main function
	private function value_scape ( $value ) {
		
		# check for multidimensional or flat array
		if ( is_array( $value[0] ) ) { # multidimensional array
			
			# define return string
			$return = '';
			
			# check inside array
			foreach ( $value as $val ) {
				
				# add ( for first of each array
				$return .= '(';
				
				# define field string
				$flds = '';
				
				# create field string from inside array
				foreach ( $val as $fld ) {					
					
					$flds .= "'" . self::civ ( $fld ) . "', ";
				}
				$return .= substr ( $flds, 0, - 2 ) . '),';
			}
			$return = substr ( $return, 0, - 1 );
			return $return;
		} else { # flat array
			
			# add ( for first of string
			$return .= '(';
			
			# create field string from array value
			foreach ( $value as $val ) {
				
				$flds .= "'" . self::civ ( $val ) . "', ";
			}
			
			# close string with )
			$return .= substr ( $flds, 0, - 2 ) . ')';
		}
		
		# return result
		return $return;
	}
	
	# where create
	private function where_scape ( $where ) {
		
		# define where string
		$whr = '';
		
		# check for array type
		if ( !is_array ( $where[0] ) ) { # flat array
			foreach ( $where as $wr ) {
				$whr .= ' ' . trim ( $wr [0] ) . ' ';
				$whr .= strlen ( $wr [1] ) ? self::field_creator ( $wr [1] ) : "";
				$whr .= ' ' . trim ( $wr [2] ) . ' ';
				$whr .= strlen ( $wr [3] ) ? self::value_creator ( $wr [3] ) : '';
				$whr .= ' ' . trim ( $wr [4] ) . ' ';
			}		
		} else { # multidimensional array
			$whr .= ' ' . trim ( $where [0] ) . ' ';
			$whr .= strlen ( $where [1] ) ? self::field_creator($where [1]) : '';
			$whr .= ' ' . trim ( $where [2] ) . ' ';
			$whr .= strlen ( $where [3] ) ? self::value_creator($where [3]) : '';
			$whr .= ' ' . trim ( $where [4] ) . ' ';
		}
		
		return $whr;
	}
	
	# variable generate
	private function value_creator ( $var ) {
		return " '" . str_replace ( ",", "','", self::civ ( $var ) ) . "' ";
	}
	
	# direct query execute (not recomment)
	public function direct ( $query ) {
		self::connect ();
		$this->res = self::query_exec ( $query );
		return $this->res;
	}
	
	# execute query
	private function query_exec ( $query ) {
		
		try { # try to run query
			 
			#return query result if all correct
			return $this->res = $this->con->query ( $query );
			
		} catch ( Exception $e ) { # query run problem
			
			# create error in main class
			$this->error  = array ( 'type' => 'mysqlqueryexec', 'error' => $e->getMessage () );
			
			# return false to function
			return FALSE;
			
		}
	}
	
	# normal select query
	public  function select( $field, $table, $where = NULL, $order = NULL, $ordertype = NULL, $limit1 = NULL, $limit2 = NULL, $group = NULL ) {
		
		# query result
		$this->res = self::query_exec ( self::select_prepear ( $field, $table, $where, $order, $ordertype, $limit1, $limit2, $group ) );
		
		# return query result
		return $this->res;
		
	}
	
	# return select result as array
	public  function result( $field, $table, $where = NULL, $order = NULL, $ordertype = NULL, $limit1 = NULL, $limit2 = NULL, $group = NULL ) {
		
		# query result
		$this->res = self::query_exec ( self::select_prepear ( $field, $table, $where, $order, $ordertype, $limit1, $limit2, $group ) );
		
		# get query return row number
		$count = self::is_query_result();
		if ( self::is_query_result() ) {
			$return = self::fetch_result();
			self::free();
			self::close();
			return $return;
		} else {
			return FALSE;
		}
	}
	
	# fetch select result as array
	private function fetch_result () {
		if ( method_exists ( 'mysqli_result', 'fetch_all' ) ) { # php > 5.3
			return $this->res->fetch_all();
		} else { # php < 5.3
			$rows = array();
			while( $row = $this->res->fetch_assoc() ) {
				$rows[] = $row;
			}
			return $rows;
		}
	}
	
	# check for query result is done
	private function is_query_result () {
		
		return ( isset ( $this->res ) && ( @$this->res->num_rows ) ) ? $this->res->num_rows : 0 ;

	}
	
	# select prepear query
	private function select_prepear (  $field, $table, $where = NULL, $order = NULL, $ordertype = NULL, $limit1 = NULL, $limit2 = NULL, $group = NULL ) {
		$query = "SELECT " . self::field_scape ( $field ) . " FROM " . $table;
		if (isset ( $where )) {
			$query .= ' WHERE ' . self::where_scape ( $where );
		}
		if (isset ( $order ) && isset ( $ordertype ) ) {
			$query .= ' ORDER BY ' . self::civ ( $order ) . ' ' . $ordertype;
		}
		if (isset ( $order ) && ! isset ( $ordertype ) ) {
			$query .= ' ORDER BY ' . self::civ ( $order ) . ' ';
		}
		if (isset ( $limit1 ) && isset ( $limit2 ) ) {
			$query .= ' LIMIT ' . ( int ) $limit1 . ', ' . ( int ) $limit2;
		}
		if (isset ( $limit1 ) && ! isset ( $limit2 ) ) {
			$query .= ' LIMIT 0, ' . ( int ) $limit1;
		}
		if (isset ( $group )) {
			$query .= ' GROUP BY ' . $group;
		}
		return $query;
	}
	
	# simple mysqli count
	public  function count ( $table, $where = NULL ) {
		
		# connect to db
		self::connect();
		
		# create query 
		$query = 'SELECT COUNT(*) FROM ' . $table;
		
		if (isset ( $where )) {
			$query .= ' WHERE ' . self::where_scape ( $where );
		}
		
		# execute query
		self::query_exec ( $query );
		
		# define count
		$count = self::is_query_result() ? $this->res->fetch_row () : 0;
		
		# check for result
		if ( $count ) {
			 
			# free mysqli store space
			self::free ();
		}
		
		# kill mysqli connection
		self::close ();
		
		# return number
		return $count [0];
		
	}
	
	# return mysqli last insert id
	public  function _insert_id() {
		
		return $this->con->insert_id;
		
	}
	
	# return mysqli affected rows
	public  function _affected_rows() {
		
		# return mysqli effected rows
		return $this->con->affected_rows;
		
	}
	
	# simple delete query
	public  function delete ( $table, $where = NULL ) {
		
		# connect to db
		self::connect ();
		
		# create query
		$query = 'DELETE FROM ' . $table;
		
		# create where query
		if (isset ( $where )) {
			$query .= ' WHERE ' . self::where_scape ( $where );
		}
		
		# wxecute query 
		self::query_exec ( $query );
		
		# get mysqli effected rows
		$result = self::_affected_rows();
		
		# kill database connection
		$this->close ();
		
		# return mysqli effected rows 
		return $result;
		
	}
	
	# simple insert query
	public  function insert ( $table, $fields, $values ) {
		
		# connect to db
		self::connect ();
		
		# create query result
		self::query_exec ( 'INSERT INTO `' . $table . '` ( ' . self::field_scape ( $fields ) . ' ) VALUES ' . self::value_scape ( $values ) . ' ' );
		
		# get last insert id
		$iid = $this->con->insert_id;
		
		# kill connection
		self::close ();
		
		# return insert id
		return $iid;
		
	}
	
	# simple update query
	public  function update ( $table, $fieldval, $where ) {
		
		# connect to db
		self::connect();
		
		#create query
		$fld = '';
		foreach ( $fieldval as $val ) {
			$fld .= $val[0]." = '" . self::civ ( $val[1] ) . "', ";
		}
		$fld =  substr ( $fld, 0, -2 );
		
		# execute query
		self::query_exec ( 'UPDATE '.$table.' SET '.$fld.' WHERE '.self::where_scape ( $where ) );
		
		# get effecteted rows		
		$effected = self::_affected_rows ();
		
		# kill connection
		$this->close();
		
		# return effected rows
		return $effected;
	}
}