<?php
	class UniteMySql{
	
		// sqlite class constants:
		const TYPE_KEY = "INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT";
		const TYPE_STRING = "VARCHAR(120)";
		const TYPE_NUMBER = "INTEGER";
		const TYPE_TEXT = "TEXT";
		const TYPE_BOOLEAN = "BOOLEAN";
		
		//error codes:
		const CODE_ZERO = 0;
		const CODE_TABLE_NOT_EXISTS = 1;
		
		private $mysql_host = "";
		private $mysql_user = ""; 
		private $mysql_pass = "";
		private $mysql_database = "";
		
		public static $handle = null;
		public $lastRowID = -1;
		public $lastArr = array();
		
				
		
		function __construct($noConnect = false){
			
			if(!empty(self::$handle))
				return(false);
			
			if($noConnect == true)
				return(false);
			
			if(!defined("DB_NAME"))
				$this->throwError("Db name is not defined");
				
				try{
				
					$this->connect(DB_HOST, DB_UNAME, DB_PWD, DB_NAME);
				
				}  catch (Exception $e){										
				}				
				if(empty(self::$handle))
					$this->throwError("Can't connect the database, please check your database connection");
				
		}		
		
		//------------------------------------------------------------
		//connect to database
		public function connect($host,$user,$pass,$database){ 
			if(!function_exists("mysqli_connect")) throw new Exception("php mysql extension doesn't activated, please activate it in php.ini");
			$this->mysql_host = $host;
			$this->mysql_user = $user;
			$this->mysql_pass = $pass;
			$this->mysql_database = $database;
			$this->getCreateDatabase();
		}
		
		//------------------------------------------------------------
		// multiple queries.
		public function multiQueries($query){		
			$result = mysqli_multi_query(self::$handle, $query);
			$this->checkForErrors("Is table exists error");			
			return $result;
		}
		
		
		/**
		 * 
		 * throw new error
		 * 
		 */
		private function throwError($message, $code=null){

			if($code == null)
				throw new Exception($message);
			else 
				throw new Exception($message,$code);
		}		
		
		
		//------------------------------------------------------------
		public function confirmOutput($message=""){
			return(array("success"=>true,"message"=>$message));
		}
		
		//------------------------------------------------------------
		// validates if handle exists. if not - exit with error message
		public function validateHandle($functionName = ""){
			if(self::$handle == false){
				if($functionName) $this->exitWithMessage("$functionName error - no open database");
				else $this->throwError("sqlite error - no open database");
			}
			return($this->confirmOutput());
		}
		
		//------------------------------------------------------------
		// validate table name field if empty or not exists - write error and exit.
		public function validateTableName($tableName,$functionName=""){			
			if(trim($tableName) == ""){
				if($functionName) $this->throwError("$functionName error - no table found");
				else $this->throwError("sqlite error - no table found");
			}
		}
		
		//------------------------------------------------------------
		// validate if table is created. if not - write error message and exist
		public function validateTable($tableName,$functionName){
			$this->validateTableName($tableName,$functionName);
			if($this->isTableExists($tableName) == false){
				if($functionName) return($this->throwError("$functionName error - the table $tableName doesn't exists",self::CODE_TABLE_NOT_EXISTS));
				else $this->throwError("sqlite error - the table $tableName doesn't exists",self::CODE_TABLE_NOT_EXISTS);
			}
		}
		
		//------------------------------------------------------------
		// valiadte fields array. if empty or the type is not array - write error message and exit.
		public function validateFields($arrFields,$functionName=""){
			if(gettype($arrFields)!="array") $this->throwError("createTable error - the fields array isn't array type.");
			if(count($arrFields) == 0) $this->throwError("createTable error - the fields don't given.");
		}
		
		//------------------------------------------------------------
		// set database file (without path)
		public function setDbFile($dbFilepath){
			$this->dbFilepath = $dbFilepath;
			$response = $this->getCreateDatabase();
			return($response);
		}

		//------------------------------------------------------------
		
		public function setAbsolutePath($path){
			$this->databaseAbsolutePath = $path;
			$this->dbFilepath = "";
		}
		
		//------------------------------------------------------------
		
		public function getLastRowID(){
			return($this->lastRowID);
		}
		
		//------------------------------------------------------------
		// return if table exists or not.
		public function isTableExists($tableName){
			$sql = 'select * from '.$tableName;
			
			$numRows = mysqli_num_rows(mysqli_query(self::$handle,"SHOW TABLES LIKE '".$tableName."'"));
			$this->checkForErrors("Is table exists error",$sql);
			
			return($numRows != 0);
		}
		
		//------------------------------------------------------------
		// create database. if already created - get database.
		public function getCreateDatabase(){
						
			self::$handle = mysqli_connect($this->mysql_host, $this->mysql_user, $this->mysql_pass);
			
			if(!self::$handle){
				$this->checkForErrors("Mysql connect error");
				$this->throwError("can't connect mysql");
			}
			
			mysqli_select_db(self::$handle,$this->mysql_database);
			$this->checkForErrors("Mysql connect to database error");
			
			return($this->confirmOutput());
		}

		//------------------------------------------------------------
		// delete table
		public function deleteTable($tableName){
			// params validation
			$this->validateTableName($tableName,"deleteTable");
			
			$this->validateHandle("deleteTable");
			
			if(!$this->isTableExists($tableName)) return($this->confirmOutput());
			
			$query = "DROP TABLE $tableName";
			
			$this->executeQuery($query,"delete table error");
			
			return($this->confirmOutput());
		}
		
		//------------------------------------------------------------
		// validate for errors
		private function checkForErrors($prefix = "",$query=""){
			if(mysqli_error(self::$handle)){
				$message = mysqli_error(self::$handle);
				if($prefix) $message = $prefix.' - '.$message;
				if($query) $message .=  ' query: ' . $query;
				$this->throwError($message);
			}
		}
		
		//------------------------------------------------------------
		// create table in database from arrFields (array["fieldname","key"])
		public function createTable($tableName,$arrFields){
		
			// params validation
			$this->validateTableName($tableName,"createTable");
			
			$this->validateFields($arrFields,"createTable");
						
			$this->validateHandle("createTable");
			
			if($this->isTableExists($tableName)) return($this->confirmOutput());
			
			$strFieldscreate = "";
			foreach($arrFields as $field=>$type){
				if($strFieldscreate != "") $strFieldscreate .= ",";
				$strFieldscreate .= "$field $type";
			}
			
			$query = "CREATE TABLE $tableName($strFieldscreate)";
			
			$this->executeQuery($query,"create table");
				
			return($this->confirmOutput());
		}
		
		//------------------------------------------------------------
		//execute query with some error text
		private function executeQuery($query,$errorText=""){
			mysqli_query(self::$handle,$query);			
			$this->checkForErrors($errorText,$query);
			return(mysqli_affected_rows(self::$handle));
		}
		
		//------------------------------------------------------------
		// insert to table tablename, array([fieldname , data])
		public function insert($tableName,$arrData){
			$response = $this->validateTable($tableName,"insert");
			
			$this->validateFields($arrData,"insert");
			
			$insertFieldsQuery = "";
			$strFields = "";
			$strValues = "";
			foreach($arrData as $field=>$value){
				$value = "'".$this->escape($value)."'";
				if($field == "id") continue;
				if($strFields != "") $strFields .= ",";
				if($strValues != "") $strValues .= ",";
				$strFields .= $field;
				$strValues .= $value;
			}
			
			$insertQuery = "insert into $tableName($strFields) values($strValues)";						
						
			$this->executeQuery($insertQuery,"insert row");

			$this->lastRowID = mysqli_insert_id(self::$handle);
						
			return($this->lastRowID);
		}
		//------------------------------------------------------------
		// insert to table tablename, array([fieldname , data])
		public function insertid(){			
			$this->lastRowID = mysqli_insert_id(self::$handle);
						
			return($this->lastRowID);
		}
		//------------------------------------------------------------
		// fetch all rows in array from table
		public function fetch($tableName,$where="",$orderField="",$groupByField=""){
			$this->validateTable($tableName,"fetch");
			
			$query = "select * from $tableName";
			if($where) $query .= " where $where";
			if($groupByField) $query .= " group by $groupByField";
			if($orderField) $query .= " order by $orderField";
			
			$rows = $this->getQueryArr($query,"fetch error");
			return($rows);
		}
		
		//------------------------------------------------------------
		// fetch rows with paging
		public function fetchPage($page,$inpage,$tableName,$where="",$orderField="",$groupByField=""){
			$response = $this->validateTable($tableName,"fetchPage");
			
			$query = "select * from $tableName";
			if($where) $query .= " where $where";
			if($groupByField) $query .= " group by $groupByField";
			if($orderField) $query .= " order by $orderField";
			
			//get num rows:
			$response = $this->getNumRows($tableName,$where);
			
			$totalRows = $response["numRows"];
			if($totalRows == 0) return($this->fetchSql($query));
			
			$numPages = ceil($totalRows / $inpage);
			if($page>$numPages) $page = $numPages;
			if($page<1) $page = 1;
			
			$offset = ($page-1)*$inpage;
			$nextPage = $page+1;
			if($nextPage > $numPages) $nextPage = 0;
			$prevPage = $page-1;
			
			$query .= " limit $offset,$inpage";
			
			$response = $this->fetchSql($query);
			
			$response["page"] = $page;
			$response["numPages"] = $numPages;
			$response["nextPage"] = $nextPage;
			$response["prevPage"] = $prevPage;
			
			return($response);
		}
		
		//------------------------------------------------------------
		// fetch all rows in array from table
		public function fetchSql($query){
			$rows = $this->getQueryArr($query,"fetch sql");
			return($rows);	
		}
		
		//--------------------------------------------------------------------------------------------------------------
		//gets count of query response
		public function getCount($query){
			$errorText ="error in query";
			$rs = mysqli_query(self::$handle, $query);						
			$this->checkForErrors($errorText,$query);
			$num = mysqli_num_rows($rs);
			return($num);
		}
		
		//--------------------------------------------------------------------------------------------------------------
		//get array from query
		private function getQueryArr($query,$errorText){
			$rs = mysqli_query(self::$handle, $query);						
			$this->checkForErrors($errorText,$query);
			$arrRows = array();
			while($row = mysqli_fetch_assoc($rs)) $arrRows[] = $row;
			$this->lastArr = $arrRows;
			return($arrRows);  
		}
		
		//--------------------------------------------------------------------------------------------------------------
		// execute sql query
		public function runSql($query){		
			$num_rows = $this->executeQuery($query,"run sql error");			
			return($num_rows);
		}
		
		//--------------------------------------------------------------------------------------------------------------
		//get num rows of the query
		public function getNumRows($tableName,$where=""){
			$response = $this->validateTable($tableName,"get num rows");
			
			$query = "select count(*) as count from $tableName";
			if($where) $query .= " where $where";
			
			$rows = $this->getQueryArr($query,"get num rows error");
						
			$numRows = $rows[0]["count"];
			return($numRows);
		}
		
		//--------------------------------------------------------------------------------------------------------------
		// get max field value
		public function getMaxValue($tableName,$field,$where=""){
			$response = $this->validateTable($tableName,"getMaxValue");
			if($response["success"] == false) return($response);
			
			$query = "select MAX($field) as maxValue from $tableName";
			if($where) $query .= " where $where";
			
			$response = $this->fetchSql($query);
			
			$maxValue = 0;
			$rows = $response["rows"];
			if(count($rows)>0) $maxValue = $rows[0]["maxValue"];
			if($maxValue == "") $maxValue = 0;
			return($maxValue);
		}		
				
		//------------------------------------------------------------		
		//return escape database parameter string.
		public function escape($str){
						
			if(function_exists("mysql_real_escape_string")) 
				return mysqli_real_escape_string(self::$handle, $str);			
			return mysqli_escape_string($str);
		}
		
		//------------------------------------------------------------		
		//return if the row with some condition exists		
		public function isRowExists($tableName,$where=""){
			$response = $this->validateTable($tableName,"isRowExists");
			
			$response = $this->getNumRows($tableName,$where);
			$found = false;
			if($response["numRows"] > 0) $found = true;
			
			return($found);
		}
		
		//------------------------------------------------------------
		//replace some row in the table (insert, and then delete)
		public function replace($tableName,$arrData,$where=""){
			$response = $this->delete($tableName,$where);
			$response = $this->insert($tableName,$arrData,$where);
			return($response);
		}
		
		//------------------------------------------------------------
		// update some row in the table.
		public function update($tableName,$arrData,$where=""){
			$response = $this->validateTable($tableName,"update");
			
			$this->validateFields($arrData,"update");
			if($where == "") $this->throwError("update error, there must be a where parameter (3-th one)");
			
			$strFields = "";
			foreach($arrData as $field=>$value){
				$value = "'".$this->escape($value)."'";
				if($strFields != "") $strFields .= ",";
				$strFields .= "$field=$value";
			}
			$updateQuery = "update $tableName set $strFields where $where";
						
			$this->executeQuery($updateQuery,"update error");
			
			return($this->confirmOutput());
		}
		
		//------------------------------------------------------------
		// delete some row or rows from the table.
		public function delete($tableName,$where){
			$response = $this->validateTable($tableName,"delete");
			
			if($where == "") $this->throwError("delete error, there must be a where parameter (second one)");
			$deleteQuery = "delete from $tableName where $where";
			
			$this->executeQuery($deleteQuery,"delete error");
						
			return($this->confirmOutput());
		}
		
		//------------------------------------------------------------
		// delete all rows from the table
		public function clearTable($tableName){
			$response = $this->validateTable($tableName,"clear table");
			
			$response = $this->runSql("delete from ".$tableName);
			return($response);
		}
		
	}// Database class end.

?>