<?php
/*
* Mysql database class - only one connection alowed
//https://gist.github.com/jonashansen229/4534794
//$db = Database::getInstance();
//$mysqli = $db->getConnection(); 
//$sqlbuildQuery = "SELECT foo FROM .....";
//$result = $mysqli->query($sqlbuildQuery);  
*/
class Database {
	private $_connection;
	private static $_instance; //The single instance
	private $_host     = dbhost;
	private $_database = dbname;
	private $_username = dbuser;
	private $_password = dbpass;
	
	/*
	Get an instance of the Database
	@return Instance
	*/
	public static function getInstance() {
		if(!self::$_instance) { // If no instance then make one
			self::$_instance = new self();
		}
		return self::$_instance;
	}
	// Constructor
	private function __construct() {
		$this->_connection = new mysqli($this->_host, $this->_username, 
			$this->_password, $this->_database);
	
		// Error handling
		if(mysqli_connect_error()) {
			trigger_error("Failed to conencto to MySQL: " . mysqli_connect_error(),
				 E_USER_ERROR);
		}
	}
	// Magic method clone is empty to prevent duplication of connection
	private function __clone() { }
	// Get mysqli connection
	public function getConnection() {
		return $this->_connection;
	}
	/*
	 * @return (int) get last affected rows
	 */
	public function getFunction($function, $data){
		
		return $this->_connection->$function($data);
	}
	
	/*
	 * @return (int) get last affected rows
	 * Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query
	 */
	public function getAffectedRows(){
		if ($affectedRows = $this->_connection->affected_rows){
			return $affectedRows;
		}
		return 0;
	}
	
	
	/*
	 * @return (int) get last number of rows
	 */
	public function getNumRows($q){
		if ($numRows = $q->num_rows){
			return $numRows;
		}
		return 0;
	}
	 /**
	 * Retrieve latest autoincrement insert id
	 *
	 * @return	@e integer
	 */
	public function getInsertId()
	{
		if ($insertId = $this->_connection->insert_id){
			return $insertId;
		}
		return 0;
	}
	
	
	
	
	
	/*
	 * return = array/object as use wish
	 * @query = array
	 * @usage
	 * $query = "SELECT * FROM xx WHERE _id=$id";
	 * $result = $this->DB->query($query); 
	*/
	public function buildQuery($query){
		if ($res = $this->_connection->query($query)){
			return $res;
		}
		return false;
	}
	
	//-----------------------------------------------
	//@Usage:
	//$fetchQuery = array (
	//				'select' => 'name,screen_name,tweeted',//string
	//				'table' => 'users', //string
	//				'where' => "{$field}='{$value}'",
	//				'order' => '',
	//				'limit' => 10,
	//				);
	//$this->DB->fetchQuery($fetchQuery);
	//--------------------------------------------			
	public function fetchQuery($q=array(),$method='object'){ 
		
		if($q['where']){$where = " WHERE {$q['where']} ";}
		if($q['order']){$order = " ORDER {$q['order']} ";}
		if($q['limit']){$limit = " LIMIT {$q['limit']} ";}
		$query = "SELECT {$q['select']} FROM {$q['table']}{$q['from']} {$where} {$order} {$limit}";
		
		if ($result = $this->_connection->query($query)) {
			 if ($method === 'object')
			 {
				$rows = $this->resultToObject($result);
			 }
			 else
			 {
				$rows = $this->resultToArray($result);
			 }
			 $result->free();
			 return $rows;
		} 
		 return false;
	}
	
	//------------------------------------
	// Return Object Oriented Data
	//------------------------------------
	public function resultToObject($result) {
		$rows = array();
		while($row = $result->fetch_assoc()) {
			$rows[] = (object) $row;
		}
		return $rows;
	}
	//------------------------------------
	// Return Array Data
	//------------------------------------
	public function resultToArray($result) {
		$rows = array();
		while($row = $result->fetch_assoc()) {
			$rows[] = (array) $row;
		}
		return $rows;
	}
	
	/*
	 * @return (int) get last number of rows
	 $fetchQuery = array (
	//				'select' => 'name',//string
	//				'from' => 'users', //string
	//				'where' => "{$field}='{$value}'",
	//				'order' => '',
	//				'limit' => 10,
	//				);
	 */
	public function countQuery($q){
		if($q['where']){$where = " WHERE {$q['where']} ";}
		if($q['order']){$order = " ORDER {$q['order']} ";}
		if($q['limit']){$limit = " LIMIT {$q['limit']} ";}
		$query = "SELECT {$q['select']} FROM {$q['table']}{$q['from']} {$where} {$order} {$limit}";
		
		if ($count = $this->buildQuery($query))
		{
			if ($numRows = $this->getNumRows($count))
			{
				return $numRows;
			}
		}
		return 0;
	}
	
	/*
	@return insertedId
	@usage								   
	$table = 'users';
	$insert = array(
					'name' => $name,
					'screen_name' => $screen_name
					);
	$this->DB->insert($table, $insert);
	*/
	public function insert($table='', $insert=array()){
		$keys = implode(',', array_keys($insert));
		$values = array_values($insert);
		$count=0;
		$setCount = count($values);
		foreach($values as $value)
		{
			$count++;
			//$value = $this->_connection->real_escape_string($value);
			$valuesarray .= "'$value'";
				if($count < $setCount){
					$valuesarray .= ',';
				}
		}
		
		$query = "INSERT INTO {$table} ({$keys}) 
								VALUES({$valuesarray})";
		
		if ($this->_connection->query($query)){
			return $this->_connection->insert_id;
		}
		return false;
	}
	
	
	
	/*
	update database
	@usage: 
	$update = array (
					'set' => array('tweeted' => $this->member->tweeted + 1, 'email' => 'ao@3ara.be'),
					'where' => "twitter_id = {$this->member->twitter_id} AND field='value'"
					);
	$this->DB->update('table', $update);
	@return boolen
	@data array
	*/
	public function update($table='', $q=array()){
		
		$setCount = count($q['set']);
		$count=0;
		foreach($q['set'] as $field => $value){
		$count++;
		//$value = $this->_connection->real_escape_string($value);
			$setarray .= " {$field}='{$value}' ";
			if($count < $setCount){
				$setarray .= ',';
			}
		}
		if($q['where'])
		{ 
			$where = " WHERE {$q['where']} ";
		}
		

		if ( $doUpdate = $this->_connection->query("UPDATE {$table} SET {$setarray} {$where}") ){

			return $this->getAffectedRows();
		}
			return false;
	}
	/*
	update database
	@usage: 
	$delete = array (
					'where' => "twitter_id = {$this->member->twitter_id} AND field='value'"
					);
	$this->DB->delete('table', $delete);
	
	@return boolen
	@data array
	*/
	public function delete($table='', $q=array())
	{
		if($q['where']){$where = " WHERE {$q['where']} ";}
		if ($delete = $this->buildQuery("DELETE FROM {$table} {$where}")) 
		{
			return $this->_connection->affected_rows;
		}
		return false;
	}

}