/core/mysqlquery.php

<?php
namespace Core;
use \mysqli as mysqli;

class MySQLQuery extends mysqli{
	public $db = null;
	
	protected $_dbHandle;
	protected $_result;
	protected $_query;
	protected $_table;

	protected $_describe = array();

	protected $_extraConditions = array();
	protected $_orderConditions = array();
	protected $_hO;
	protected $_hM;
	protected $_hMABTM;
	protected $_page;
	protected $_limit;
	
	function __construct() {
		
		$address = MySQL_DB_HOST;
		$account = MySQL_DB_USER;
		$pwd = MySQL_DB_PASSWORD;
		$name = MySQL_DB_NAME;
	  	return	$this->s_connect($address, $account, $pwd, $name);
	}

    /** Connects to database **/
	
    function s_connect($address, $account, $pwd, $name) 
    {
    	
        $this->_dbHandle = mysql_connect($address, $account, $pwd);
        if ($this->_dbHandle != 0) 
        {
            if (mysql_select_db($name, $this->_dbHandle)) 
            {
                return TRUE;
            }
            else 
            {
                return FALSE;
            }
        }
        else 
        {
            return FALSE;
        }
    }
 
    /** Disconnects from database **/

    function disconnect() {
        if (@mysql_close($this->_dbHandle) != 0) {
            return 1;
        }  else {
            return 0;
        }
    }

    /** Select Query **/

	function where($field, $value, $compare_sign='=')
	{
		array_push($this->_extraConditions, '`'.$this->_model.'`.`'.$field.'` '.$compare_sign.' '.mysql_real_escape_string($value));
		return $this;
	}


	function where_in($field, $value)
	{
			if (!is_array($value))
			{
				$value = mysql_real_escape_string($value);
			}
			else
			{
				foreach ($value as $k=>$v)
				{
					$value[$k] = mysql_real_escape_string($value);
				}
				$value = implode(',',$value);
			}
			array_push($this->_extraConditions, '`'.$this->_model.'`.`'.$field.'` IN ( '.$value.')');
		
		return $this;
	}

	function like($field, $value) {
		$this->_extraConditions .= '`'.$this->_model.'`.`'.$field.'` LIKE \'%'.mysql_real_escape_string($value).'%\' AND ';
		return $this;
	}

 function fetch_associations($associations)
	{
		if ((isset($associations['one'])) && ($associations['one'] === TRUE))
		{
			$this->_hO = 1;
		}
		if ((isset($associations['many'])) && ($associations['many'] === TRUE))
		{
			$this->_hM = 1;
		}
		if ((isset($associations['manytomany'])) && ($associations['manytomany'] === TRUE))
		{
			$this->_hMABTM = 1;
		}
		return $this;
	}

	function limit($limit) {
		$this->_limit = $limit;
		return $this;
	}

	function page($page) {
		$this->_page = $page;
		return $this;
	}

 	function order_by($orderBy, $order = 'ASC', $binary = FALSE, $model = NULL)
	{
		$orderBinary = '';
		if ($binary === TRUE)
		{
			$orderBinary = 'BINARY ';
		}
		if ($model === NULL)
		{
			$model = $this->_model;
			array_push($this->_orderConditions, $orderBinary.'`'.$model.'`.`'.$orderBy.'` '.$order);
		}
		return $this;
	}

	function find() {

		global $inflect;

		$from = '`'.$this->_table.'` as `'.$this->_model.'` ';
		$conditions = '\'1\'=\'1\'';
		$conditionsChild = '';
		$fromChild = '';

		if ($this->_hO == 1 && isset($this->hasOne)) {
			
			foreach ($this->hasOne as $alias => $model) {
				$table = strtolower($inflect->pluralize($model));
				$singularAlias = strtolower($alias);
				$from .= 'LEFT JOIN `'.$table.'` as `'.$alias.'` ';
				$from .= 'ON `'.$this->_model.'`.`'.$singularAlias.'_id` = `'.$alias.'`.`id`  ';
			}
		}
	
		if ($this->id) {
			$conditions .= ' AND `'.$this->_model.'`.`id` = \''.mysql_real_escape_string($this->id).'\'';
		}

		if (!empty($this->_extraConditions)) {
			$conditions .= ' AND '.implode(' AND', $this->_extraConditions);
		}

	
		
		if (!empty($this->_orderConditions)) {
			$conditions .= ' ORDER BY '.implode(',',$this->_orderConditions);
		}

		if (isset($this->_page)) {
			$offset = ($this->_page-1)*$this->_limit;
			$conditions .= ' LIMIT '.$this->_limit.' OFFSET '.$offset;
		}
		
		$this->_query = 'SELECT * FROM '.$from.' WHERE '.$conditions;
		#echo '<!--'.$this->_query.'-->';
		$this->_result = mysql_query($this->_query, $this->_dbHandle);
		$result = array();
		$table = array();
		$field = array();
		$tempResults = array();
		$numOfFields = mysql_num_fields($this->_result);
		for ($i = 0; $i < $numOfFields; ++$i) {
		    array_push($table,mysql_field_table($this->_result, $i));
		    array_push($field,mysql_field_name($this->_result, $i));
		}
		if (mysql_num_rows($this->_result) > 0 ) {
			while ($row = mysql_fetch_row($this->_result)) {
				for ($i = 0;$i < $numOfFields; ++$i) {
					$tempResults[$table[$i]][$field[$i]] = $row[$i];
				}

				if ($this->_hM == 1 && isset($this->hasMany)) {
					foreach ($this->hasMany as $aliasChild => $modelChild) {
						$queryChild = '';
						$conditionsChild = '';
						$fromChild = '';

						$tableChild = strtolower($inflect->pluralize($modelChild));
						$pluralAliasChild = strtolower($inflect->pluralize($aliasChild));
						$singularAliasChild = strtolower($aliasChild);

						$fromChild .= '`'.$tableChild.'` as `'.$aliasChild.'`';
						
						$conditionsChild .= '`'.$aliasChild.'`.`'.strtolower($this->_model).'_id` = \''.$tempResults[$this->_model]['id'].'\'';
	
						$queryChild =  'SELECT * FROM '.$fromChild.' WHERE '.$conditionsChild;	
						#echo '<!--'.$queryChild.'-->';
						$resultChild = mysql_query($queryChild, $this->_dbHandle);
				
						$tableChild = array();
						$fieldChild = array();
						$tempResultsChild = array();
						$resultsChild = array();
						
						if (mysql_num_rows($resultChild) > 0) {
							$numOfFieldsChild = mysql_num_fields($resultChild);
							for ($j = 0; $j < $numOfFieldsChild; ++$j) {
								array_push($tableChild,mysql_field_table($resultChild, $j));
								array_push($fieldChild,mysql_field_name($resultChild, $j));
							}

							while ($rowChild = mysql_fetch_row($resultChild)) {
								for ($j = 0;$j < $numOfFieldsChild; ++$j) {
									$tempResultsChild[$tableChild[$j]][$fieldChild[$j]] = $rowChild[$j];
								}
								array_push($resultsChild,$tempResultsChild);
							}
						}
						
						$tempResults[$aliasChild] = $resultsChild;
						
						mysql_free_result($resultChild);
					}
				}


				if ($this->_hMABTM == 1 && isset($this->hasManyAndBelongsToMany)) {
					foreach ($this->hasManyAndBelongsToMany as $aliasChild => $tableChild) {
						$queryChild = '';
						$conditionsChild = '';
						$fromChild = '';

						$tableChild = strtolower($inflect->pluralize($tableChild));
						$pluralAliasChild = strtolower($inflect->pluralize($aliasChild));
						$singularAliasChild = strtolower($aliasChild);

						$sortTables = array($this->_table,$pluralAliasChild);
						sort($sortTables);
						$joinTable = implode('_',$sortTables);

						$fromChild .= '`'.$tableChild.'` as `'.$aliasChild.'`,';
						$fromChild .= '`'.$joinTable.'`,';
						
						$conditionsChild .= '`'.$joinTable.'`.`'.$singularAliasChild.'_id` = `'.$aliasChild.'`.`id` AND ';
						$conditionsChild .= '`'.$joinTable.'`.`'.strtolower($this->_model).'_id` = \''.$tempResults[$this->_model]['id'].'\'';
						$fromChild = substr($fromChild,0,-1);

						$queryChild =  'SELECT * FROM '.$fromChild.' WHERE '.$conditionsChild;	
						#echo '<!--'.$queryChild.'-->';
						$resultChild = mysql_query($queryChild, $this->_dbHandle);
				
						$tableChild = array();
						$fieldChild = array();
						$tempResultsChild = array();
						$resultsChild = array();
						
						if (mysql_num_rows($resultChild) > 0) {
							$numOfFieldsChild = mysql_num_fields($resultChild);
							for ($j = 0; $j < $numOfFieldsChild; ++$j) {
								array_push($tableChild,mysql_field_table($resultChild, $j));
								array_push($fieldChild,mysql_field_name($resultChild, $j));
							}

							while ($rowChild = mysql_fetch_row($resultChild)) {
								for ($j = 0;$j < $numOfFieldsChild; ++$j) {
									$tempResultsChild[$tableChild[$j]][$fieldChild[$j]] = $rowChild[$j];
								}
								array_push($resultsChild,$tempResultsChild);
							}
						}
						
						$tempResults[$aliasChild] = $resultsChild;
						mysql_free_result($resultChild);
					}
				}

				array_push($result,$tempResults);
			}

			if (mysql_num_rows($this->_result) == 1 && $this->id != null) {
				mysql_free_result($this->_result);
				$this->clear();
				return($result[0]);
			} else {
				mysql_free_result($this->_result);
				$this->clear();
				return($result);
			}
		} else {
			mysql_free_result($this->_result);
			$this->clear();
			return $result;
		}

	}

    /** Custom SQL Query **/

	function custom($query) {

		global $inflect;

		$this->_result = mysql_query($query, $this->_dbHandle);

		$result = array();
		$table = array();
		$field = array();
		$tempResults = array();
		if (mysql_num_rows($this->_result) > 0)
		{
			if(substr_count(strtoupper($query),"SELECT")>0)
			{
				$numOfFields = mysql_num_fields($this->_result);
				for ($i = 0; $i < $numOfFields; ++$i)
				{
					array_push($table,mysql_field_table($this->_result, $i));
					array_push($field,mysql_field_name($this->_result, $i));
				}
				while ($row = mysql_fetch_row($this->_result))
				{
					for ($i = 0;$i < $numOfFields; ++$i)
					{
						$table[$i] = ucfirst($inflect->singularize($table[$i]));
						$tempResults[$table[$i]][$field[$i]] = $row[$i];
					}
					array_push($result,$tempResults);
				}
			}
			else
			{
				$numOfFields = mysql_num_fields($this->_result);
				for ($i = 0; $i < $numOfFields; ++$i)
				{
					array_push($field,mysql_field_name($this->_result, $i));
				}
				while ($row = mysql_fetch_row($this->_result))
				{
					for ($i = 0;$i < $numOfFields; ++$i)
					{
						$tempResults[$field[$i]] = $row[$i];
					}
					array_push($result,$tempResults);
				}
			}
		}
		mysql_free_result($this->_result);
		$this->clear();
		return($result);
	}

    /** Describes a Table **/

	protected function _describe() {
		global $cache;

		$this->_describe = $cache->get('describe'.$this->_table);

		if (!$this->_describe) {
			$this->_describe = array();
			$query = 'DESCRIBE '.$this->_table;
			$this->_result = mysql_query($query, $this->_dbHandle);
			while ($row = mysql_fetch_row($this->_result)) {
				 array_push($this->_describe,$row[0]);
			}

			mysql_free_result($this->_result);
			$cache->set('describe'.$this->_table,$this->_describe);
		}

		foreach ($this->_describe as $field) {
			$this->$field = null;
		}
	}

    /** Delete an Object **/

	function delete() {
		if ($this->id) {
			$query = 'DELETE FROM '.$this->_table.' WHERE `id`=\''.mysql_real_escape_string($this->id).'\'';		
			$this->_result = mysql_query($query, $this->_dbHandle);
			$this->clear();
			if ($this->_result == 0) {
			    /** Error Generation **/
				return -1;
		   }
		} else {
			/** Error Generation **/
			return -1;
		}
		
	}

    /** Saves an Object i.e. Updates/Inserts Query **/

	function save() {
		$query = '';
		if (isset($this->id)) {
			$updates = '';
			foreach ($this->_describe as $field) {
				if ($this->$field) {
					$updates .= '`'.$field.'` = \''.mysql_real_escape_string($this->$field).'\',';
				}
			}

			$updates = substr($updates,0,-1);

			$query = 'UPDATE '.$this->_table.' SET '.$updates.' WHERE `id`=\''.mysql_real_escape_string($this->id).'\'';			
		} else {
			$fields = '';
			$values = '';
			foreach ($this->_describe as $field) {
				if ($this->$field) {
					$fields .= '`'.$field.'`,';
					$values .= '\''.mysql_real_escape_string($this->$field).'\',';
				}
			}
			$values = substr($values,0,-1);
			$fields = substr($fields,0,-1);

			$query = 'INSERT INTO '.$this->_table.' ('.$fields.') VALUES ('.$values.')';
		}
		$this->_result = mysql_query($query, $this->_dbHandle);
		$this->clear();
		if ($this->_result == 0) {
            /** Error Generation **/
			return -1;
        }
	}
	
	function save2() {
		$query = '';
		if (isset($this->id)) {
			$updates = '';
			
			foreach ($this->_describe as $key=>$value) {
				if ($value) {
					$updates .= '`'.$key.'` = \''.mysql_real_escape_string($value).'\',';
				}
			}
	
			$updates = substr($updates,0,-1);
	
			$query = 'UPDATE '.$this->_table.' SET '.$updates.' WHERE `id`=\''.mysql_real_escape_string($this->id).'\'';
			
		} else {
			$fields = '';
			$values = '';
			foreach ($this->_describe as $key=>$value) {
				if ($value) {
					$fields .= '`'.$key.'`,';
					$values .= '\''.@mysql_real_escape_string($value).'\',';
				}
			}
			$values = substr($values,0,-1);
			$fields = substr($fields,0,-1);
			$query = 'INSERT INTO '.$this->_table.' ('.$fields.') VALUES ('.$values.')';
		}
		$this->_brada = $query;;
		$this->_result = mysql_query($query, $this->_dbHandle);
		$this->clear();
		if ($this->_result == 0) {
		/** Error Generation **/
			return -1;
		 }
		$this->clear();
	}
 
	/** Clear All Variables **/

	function clear() {
		foreach($this->_describe as $field) {
			$this->$field = null;
		}

		$this->_orderby = null;
		$this->_extraConditions = null;
		$this->_hO = null;
		$this->_hM = null;
		$this->_hMABTM = null;
		$this->_page = null;
		$this->_order = null;
	}

	/** Pagination Count **/

 function total_pages() {
		global $inflect;

		$from = '`'.$this->_table.'` as `'.$this->_model.'` ';
		$conditions = '\'1\'=\'1\'';
		$conditionsChild = '';
		$fromChild = '';

		if ($this->_hO == 1 && isset($this->hasOne)) {

			foreach ($this->hasOne as $alias => $model) {
				$table = strtolower($inflect->pluralize($model));
				$singularAlias = strtolower($alias);
				$from .= 'LEFT JOIN `'.$table.'` as `'.$alias.'` ';
				$from .= 'ON `'.$this->_model.'`.`'.$singularAlias.'_id` = `'.$alias.'`.`id`  ';
			}
		}

		if (($this->id) && ($this->_dbHandle !== NULL))
		{
			$conditions .= 'AND `'.$this->_model.'`.`id` = '.$this->_dbHandle->quote($this->id);
		}

		if (!empty($this->_extraConditions)) {
			$conditions .= ' AND '.implode(' AND', $this->_extraConditions);
		}

		if (!empty($this->_orderConditions)) {
			$conditions .= ' ORDER BY '.implode(',',$this->_orderConditions);
		}

		if (isset($this->_page)) {
			$offset = ($this->_page-1)*$this->_limit;
			$conditions .= ' LIMIT '.$this->_limit.' OFFSET '.$offset;
		}
		$this->_query = 'SELECT * FROM '.$from.' WHERE '.$conditions;

		if ($this->_limit && ($this->_dbHandle !== NULL)) {
			$pattern = '/SELECT (.*?) FROM (.*)LIMIT(.*)/i';
			$replacement = 'SELECT COUNT(*) FROM $2';
			$countQuery = preg_replace($pattern, $replacement, $this->_query);
			$this->_result = $this->_dbHandle->prepare($countQuery);
			$this->_result->execute();
			$count = $this->_result->fetch(PDO::FETCH_BOTH);
			$totalPages = ceil($count[0]/$this->_limit);
			return $totalPages;
		} else {
			/* Error Generation Code Here */
			return -1;
		}
	}

    /** Get error string **/

    function getError() {
        return mysql_error($this->_dbHandle);
    }
	
	function getErrorNo()
	{
		 return mysql_errno($this->_dbHandle);
	}
}