Một đoạn code chuẩn để nghiên cứu mysql (ok)

SELECT user.email, user.fullname, user_join.testjoin, user_join.testaddress FROM user LEFT JOIN user_join ON user.id = user_join.user_id WHERE user.email = 'phamngoctuong1805@gmail.com' GROUP BY user_join.testjoin ORDER BY user.email ASC, user.fullname ASC LIMIT 1, 5

<?php
/**
* PHP Simple Model Driver
* 
* @author BuiTrung<trungbq06@gmail.com>
* @version 1.0
*/
define('DATABASE_HOST', 'localhost');
define('DATABASE_PORT', '3306');
define('DATABASE_USERNAME', 'root');
define('DATABASE_PASSWORD', '');
define('DATABASE_NAME', 'ticket');
class Mysql {
	/**
	 * Enter description here...
	 *
	 * @var unknown_type
	 */
	var $startQuote = "`";
	
	/**
	 * Enter description here...
	 *
	 * @var unknown_type
	 */
	var $endQuote = "`";
	
	/**
	 * The DataSource configuration
	 *
	 * @var array
	 * @access public
	 */
	var $config = array ();
	
	/**
	 * Log file
	 */
	var $logFile = null;
	
	var $logFileName = '';
	
	/**
	* List of table engine specific parameters used on table creating
	*
	* @var array
	* @access public
	*/
	var $tableParameters = array(
		'charset' => array('value' => 'DEFAULT CHARSET', 'quote' => false, 'join' => '=', 'column' => 'charset'),
		'collate' => array('value' => 'COLLATE', 'quote' => false, 'join' => '=', 'column' => 'Collation'),
		'engine' => array('value' => 'ENGINE', 'quote' => false, 'join' => '=', 'column' => 'Engine')
	);
	
	var $keywords = array('>=', '<=', '>', '<', 'IN', 'NOT', 'IS', 'LIKE', '!=', '<>');
	
	/**
	 * Mysqli column definition
	 *
	 * @var array
	 */
	var $columns = array('primary_key' => array('name' => 'int(11) DEFAULT NULL auto_increment'),
						'string' => array('name' => 'varchar', 'limit' => '255'),
						'text' => array('name' => 'text'),
						'integer' => array('name' => 'int', 'limit' => '11', 'formatter' => 'intval'),
						'float' => array('name' => 'float', 'formatter' => 'floatval'),
						'datetime' => array('name' => 'datetime', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
						'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
						'time' => array('name' => 'time', 'format' => 'H:i:s', 'formatter' => 'date'),
						'date' => array('name' => 'date', 'format' => 'Y-m-d', 'formatter' => 'date'),
						'binary' => array('name' => 'blob'),
						'boolean' => array('name' => 'tinyint', 'limit' => '1'));
	
	static $instance = null;
	
	public function __construct($config = array()) {
		$this->config = $config;
		
		return $this->connect();
	}
	
	public static function getInstance()
	{
		$config = array('host'=>DATABASE_HOST, 'login' => DATABASE_USERNAME, 'password' => DATABASE_PASSWORD, 'port' => DATABASE_PORT, 'database' => DATABASE_NAME, 'encoding'=>'utf8');
		if (null === Mysql::$instance) {
			Mysql::$instance = new Mysql($config);
		}
	
		return Mysql::$instance;
	}
	
	/**
	 * Connects to the database using options in the given configuration array.
	 *
	 * @return boolean True if the database could be connected, else false
	 */
	function connect() {
		$config = $this->config;
		$this->connected = false;
		$this->connection = mysqli_connect($config['host'], $config['login'], $config['password'], $config['database']);

		if ($this->connection !== false) {
			$this->connected = true;
		}
		
		// Important row to return utf8 results
		if (!empty($config['encoding'])) {
			$this->setEncoding($config['encoding']);
		}
		
		return $this->connected;
	}
	
	/**
	 * Disconnects from database.
	 *
	 * @return boolean True if the database could be disconnected, else false
	 */
	function disconnect() {
		@mysqli_free_result($this->results);
		$this->connected = !@mysqli_close($this->connection);
		return !$this->connected;
	}
	
	function setLogFile($name) {
		$this->logFileName = $name;
		
		// Init log file
		$this->logFile = fopen($this->logFileName, 'a+');
	}
	
	/**
	 * Executes given SQL statement.
	 *
	 * @param string $sql SQL statement
	 * @return resource Result resource identifier
	 * @access protected
	 */
	function _execute($sql) {
		if (preg_match('/^\s*call/i', $sql)) {
			return $this->_executeProcedure($sql);
		} else {
			return mysqli_query($this->connection, $sql);
		}
	}
	
	function execute($sql) {
		if (! function_exists ( 'getMicrotime' )) {
			/**
			 * Returns microtime for execution time checking
			 *
			 * @return float Microtime
			 */
			function getMicrotime() {
				list ( $usec, $sec ) = explode ( " ", microtime () );
				return (( float ) $usec + ( float ) $sec);
			}
		}
		
		$t = getMicrotime ();
		$this->_result = $this->_execute ( $sql );
		$this->affected = $this->lastAffected ();
		$this->took = round ( (getMicrotime () - $t) * 1000, 0 );
		$this->error = $this->lastError ();
		$this->numRows = $this->lastNumRows ( $this->_result );
		
		// Log query to files to debug
		$this->logQuery($sql);
		if (!empty($this->error)) {
			$this->logQuery($this->error);
		}
		
		return $this->_result;
	}
	
	/**
	 * Log SQL to output files to debug
	 */
	function logQuery($sql) {
		
	}
	
	/**
	 * Executes given SQL statement (procedure call).
	 *
	 * @param string $sql SQL statement (procedure call)
	 * @return resource Result resource identifier for first recordset
	 * @access protected
	 */
	function _executeProcedure($sql) {
	    $answer = mysqli_multi_query($this->connection, $sql);

	    $firstResult = mysqli_store_result($this->connection);

        if (mysqli_more_results($this->connection)) {
            while($lastResult = mysqli_next_result($this->connection));
        }

        return $firstResult;
	}
	
	/**
	 * Returns a quoted and escaped string of $data for use in an SQL statement.
	 */
	function value($data) {
		return "'" . mysqli_real_escape_string($this->connection, $data) . "'";
	}
	
	/**
	 * Returns a formatted error message from previous database operation.
	 *
	 * @return string Error message with error number
	 */
	function lastError() {
		if (mysqli_errno($this->connection)) {
			return mysqli_errno($this->connection).': '.mysqli_error($this->connection);
		}
		
		return null;
	}
	
	/**
	 * Returns number of affected rows in previous database operation. If no previous operation exists,
	 * this returns false.
	 *
	 * @return integer Number of affected rows
	 */
	function lastAffected($source = null) {
		if ($this->_result) {
			return mysqli_affected_rows($this->connection);
		}
		
		return null;
	}
	
	/**
	 * Returns number of rows in previous resultset. If no previous resultset exists,
	 * this returns false.
	 *
	 * @return integer Number of rows in resultset
	 */
	function lastNumRows($source = null) {
		if ($this->_result and is_object($this->_result)) {
			return @mysqli_num_rows($this->_result);
		}
		return null;
	}
	
	/**
	 * Returns the ID generated from the previous INSERT operation.
	 *
	 * @param unknown_type $source
	 * @return in
	 */
	function lastInsertId($source = null) {
		$id = $this->fetchRow('SELECT LAST_INSERT_ID() AS insertID', false);
		if ($id !== false && !empty($id) && !empty($id[0]) && isset($id[0]['insertID'])) {
			return $id[0]['insertID'];
		}

		return null;
	}

	/**
	 * Gets the length of a database-native column description, or null if no length
	 *
	 * @param string $real Real database-layer column type (i.e. "varchar(255)")
	 * @return integer An integer representing the length of the column
	 */
	function length($real) {
		$col = str_replace(array(')', 'unsigned'), '', $real);
		$limit = null;

		if (strpos($col, '(') !== false) {
			list($col, $limit) = explode('(', $col);
		}

		if ($limit != null) {
			return intval($limit);
		}
		return null;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $results
	 */
	function resultSet(&$results) {
		$this->results =& $results;
		$this->map = array();
		$num_fields = mysqli_num_fields($results);
		$index = 0;
		$j = 0;
		while ($j < $num_fields) {
			$column = mysqli_fetch_field_direct($results, $j);
			if (!empty($column->table)) {
				$this->map[$index++] = array($column->table, $column->name);
			} else {
				$this->map[$index++] = array(0, $column->name);
			}
			$j++;
		}
	}
	
	/**
	 * Returns an array of all result rows for a given SQL query.
	 * Returns false if no rows matched.
	 *
	 * @param string $sql
	 *        	SQL statement
	 * @param boolean $cache
	 *        	Enables returning/storing cached query results
	 * @return array Array of resultset rows, or false if no rows matched
	 */
	function fetchAll($sql) {
		if ($this->execute ( $sql )) {
			$out = array ();
			
			while ( $item = $this->fetchRow () ) {
				$out [] = $item;
			}
			return $out;
		} else {
			return false;
		}
	}
	
	/**
	 * Returns a row from current resultset as an array .
	 *
	 *
	 * @return array The fetched row as an array
	 */
	function fetchRow($sql = null) {
		if (! empty ( $sql ) && is_string ( $sql ) && strlen ( $sql ) > 5) {
			if (! $this->execute ( $sql )) {
				return null;
			}
		}
		
		if (is_resource ( $this->_result ) || is_object ( $this->_result )) {
			$this->resultSet ( $this->_result );
			$resultRow = $this->fetchResult ();
			return $resultRow;
		} else {
			return null;
		}
	}
	
	/**
	 * Fetches the next row from the current result set
	 *
	 * @return unknown
	 */
	function fetchResult() {
		if ($row = mysqli_fetch_row($this->results)) {
			$resultRow = array();
			$i = 0;
			foreach ($row as $index => $field) {
				@list($table, $column) = $this->map[$index];
				$resultRow[$table][$column] = $row[$index];
				$i++;
			}
			return $resultRow;
		} else {
			return false;
		}
	}
	
	/**
	 * Sets the database encoding
	 *
	 * @param string $enc Database encoding
	 */
	function setEncoding($enc) {
		return $this->_execute('SET NAMES ' . $enc) != false;
	}
	
	/**
	 * Gets the database encoding
	 *
	 * @return string The database encoding
	 */
	function getEncoding() {
		return mysqli_client_encoding($this->connection);
	}

	/*
	 * build condition
	 *
	 * @param <array> $condition
	 * @example
	 * $condition = array(
	 * array('member_id' => '50'),
	 * array('member_dept_id' => 'IN (1,2,3)')
	 *
	 * @return String
	 * @example
	 * "WHERE member_id >= 50 AND member_dept_id IN (1,2,3)
	 */
	protected function buildConditions($condition) {
		if (empty($condition)) return;
		
		$sql = '';
		$index = 0;
		$conditionArray = array();
		
		foreach ( $condition as $field => $sub ) {
			$math = '';
			$subVal = explode(' ', $sub);
			if (!in_array($subVal[0], $this->keywords)) {
				$math = '=';
			}
			
			$conditionArray[] = " " . $field . " " . $math . " '" . $sub . "'";
		}
		
		return " WHERE " . implode(' AND ', $conditionArray);
	}
	
	/**
	 *
	 * @param
	 *	Array or String orders like $orders => array('my_name'=>'ASC', 'colum_id'=>'DESC') or $orders => "my_name ASC, column DESC"
	 */
	protected function buildOrders($orders) {
		$sql = '';
		if (isset ( $orders )) {
			if (! is_array ( ! $orders ) && $orders != "") {
				$ordersCache = preg_replace ( '/,\s+/', ',', $orders );
				if (!is_array($ordersCache)) $ordersCache = explode ( ",", $ordersCache );
				$orders = array ();
				foreach ( $ordersCache as $item ) {
					$itemArr = explode ( " ", $item );
					if (isset ( $itemArr [1] )) {
						$orders [$itemArr [0]] = $itemArr [1];
					} else {
						$orders [$itemArr [0]] = 'ASC';
					}
				}
			}
			
			if (count ( $orders )) {
				$str = "";
				foreach ( $orders as $k => $v ) {
					$str .= ($k . " " . $v . ", ");
				}
				if ($str != "") $str = substr ( $str, 0, (strlen ( $str ) - strlen ( $str ) - 2) );
				$sql = "ORDER BY " . $str;
			}
		}
		return $sql;
	}
	
	/**
	 *
	 * @param <String> $mainTable
	 *        	manin table name
	 * @param <type> $joins        	
	 * @return string
	 */
	protected function buildJoins($mainTable, $joins) {
		$sql = '';
		if ($mainTable != "" && !empty( $joins )) {
			foreach ( $joins as $k => $v ) {
				$sql .= " " . (isset ( $v ['type'] ) ? $v ['type'] : "") . " JOIN " . $k . " ON " . $mainTable . "." . $v ['main_key'] . " = " . $k . "." . $v ['join_key'] . " ";
			}
		}
		return $sql;
	}
	
	/**
	 *
	 * @param <type> $groups
	 *        	is an array of name all fields for group
	 * @return string
	 */
	protected function buildGroups($groups) {
		$sql = '';
		if (isset ( $groups )) {
			if (! is_array ( ! $groups ) && $groups != "") {
				$groups = preg_replace ( '/,\s+/', ',', $groups );
				$groups = explode ( ",", $groups );
			}
			
			if (count ( $groups )) {
				$str = "";
				foreach ( $groups as $v ) {
					$str .= ($v . ", ");
				}
				if ($str != "")
					$str = substr ( $str, 0, (strlen ( $str ) - strlen ( $str ) - 2) );
				$sql = "GROUP BY " . $str;
			}
		}
		return $sql;
	}
	
	// default for mysql
	public function setLimit($sql, $limit = false, $offset = false) {
		if ($limit) {
			$rt = '';
			if (! strpos ( strtolower ( $limit ), 'limit' ) || strpos ( strtolower ( $limit ), 'limit' ) === 0) {
				$rt = ' LIMIT';
			}
			
			if ($offset) {
				$rt .= ' ' . $offset . ',';
			}
			
			$rt .= ' ' . $limit;
			$sql = $sql . $rt;
		}
		return $sql;
	}
	
	/**
	 * Set LIMIT for a SQL
	 * 
	 * @param unknown $limit
	 * @param string $offset
	 * @return string|NULL
	 */
	function limit($limit, $offset = null) {
		if ($limit) {
			$rt = '';
			if (! strpos ( strtolower ( $limit ), 'limit' ) || strpos ( strtolower ( $limit ), 'limit' ) === 0) {
				$rt = ' LIMIT';
			}
			
			if ($offset) {
				$rt .= ' ' . $offset . ',';
			}
			
			$rt .= ' ' . $limit;
			return $rt;
		}
		return null;
	}
	
	/**
	 * Render a SQL statement
	 * 
	 * @param unknown $type
	 * @param unknown $data
	 * @return string
	 */
	public function renderStatement($type, $data) {
		extract ( $data );
		$aliases = null;
		
		switch (strtolower ( $type )) {
			case 'select' :
				return "SELECT {$fields} FROM {$table} {$joins} {$conditions} {$group} {$order} {$limit}";
				break;
			case 'create' :
				return "INSERT INTO {$table} ({$fields}) VALUES ({$values})";
				break;
			case 'update' :
				if (! empty ( $alias )) {
					$aliases = "{$this->alias}{$alias} {$joins} ";
				}
				return "UPDATE {$table} {$aliases}SET {$fields} {$conditions}";
				break;
			case 'delete' :
				if (! empty ( $alias )) {
					$aliases = "{$this->alias}{$alias} {$joins} ";
				}
				return "DELETE {$alias} FROM {$table} {$aliases}{$conditions}";
				break;
		}
	}
	
	/**
	 * Perform insert query
	 */
	function insert($myTable, $data) {
		$query = array ();
		$query ['table'] = $myTable;
		
		$fields = array ();
		$values = array ();
		
		foreach ( $data as $key => $val ) {
			$fields [] = $key;
			$values [] = $this->value($val);
		}
		$query ['fields'] = implode ( ', ', $fields );
		$query ['values'] = implode ( ', ', $values );
		
		if ($this->execute ( $this->renderStatement ( 'create', $query ) )) {
			return true;
		} else {
			return false;
		}
	}
	
	public function update($myTable, $myBean, $conditions = null) {
		if (isset($myBean['id']))
			unset($myBean['id']);
		$query = array ();
		$table = $myTable;
		
		$myUpdateContent = "";
		
		foreach ( $myBean as $key => $val ) {
			$field = $key;
			$value = $this->value($val);
			$myUpdateContent .= $field . " = " . $value . ", ";
		}
		$fields = substr ( $myUpdateContent, 0, (strlen ( $myUpdateContent ) - 2) );
		$conditions = $this->buildConditions ( $conditions );
		$alias = $joins = null;
		$query = compact ( 'table', 'alias', 'joins', 'fields', 'conditions' );
		
		if (! $this->execute ( $this->renderStatement ( 'update', $query ) )) {
			return false;
		}
		return true;
	}
	
	public function delete($myTable, $conditions = null) {
		$alias = $joins = null;
		$table = $myTable;
		$conditions = $this->buildConditions ( $conditions );
		
		if ($conditions === false) {
			return false;
		}
		
		if ($this->execute ( $this->renderStatement ( 'delete', compact ( 'alias', 'table', 'joins', 'conditions' ) ) ) === false) {
			return false;
		}
		return true;
	}
	
	/*
	 * Ham truu tuong hoa: lay du lieu, tuong duong cau truy van select dang don gian
	 * Tham so:
	 * $$tTableName: ten doi tuong du lieu, co the hieu la ten bang
	 * $tNameProperties: mang ten thuoc tinh doi tuong, co the hieu la ten cua cac truong can truy van
	 * $$tWhereClause: chuoi dieu kien cua ham, hay cua cau truy van
	 */
	function select($myTable, $options = array(), $isCount = false) {
		// Get options by parameters
		$myFields = isset($options['fields']) ? $options['fields'] : '*';
		$conditions = isset($options['conditions']) ? $options['conditions'] : null;
		$orders = isset($options['orders']) ? $options['orders'] : null;
		$groups = isset($options['groups']) ? $options['groups'] : null;
		$mJoins = isset($options['joins']) ? $options['joins'] : null;
		$mlimit = isset($options['limit']) ? $options['limit'] : false;
		$moffset = isset($options['offset']) ? $options['offset'] : false;
		
		try {
			$returnArr = array ();
			$table = $myTable;
			$alias = $joins = $order = $group = $limit = "";
			$fields = "";
			if (is_array ( $myFields )) {
				$fields = implode ( ', ', $myFields );
			} else {
				$fields = $myFields;
			}
			
			if (isset ( $mJoins ) && is_array ( $mJoins )) {
				foreach ( $mJoins as $jTable => $join ) {
					if (empty ( $jTable ) || empty ( $join ['join_key'] ) || empty ( $join ['main_key'] ) || ! isset ( $join ['join_fields'] ) || !isset ( $join ['join_fields'] [1] ))
						continue;
					$fields .= ", " . $jTable . "." . $join ['join_fields'] [0] . ", " . $jTable . "." . $join ['join_fields'] [1];
				}
			}
			
			$conditions = $this->buildConditions ( $conditions );
			$order = $this->buildOrders ( $orders );
			$group = $this->buildGroups ( $groups );
			$joins = $this->buildJoins ($myTable, $mJoins);
			$tmpTable = explode('_', $table);
			$alias = array();
			foreach ($tmpTable as $tmp) {
				$alias[] = ucfirst($tmp);
			}
			
			$alias = implode($alias);
			
			$query = compact ( 'table', 'alias', 'joins', 'fields', 'conditions', 'joins', 'group', 'order', 'limit' );
			$sql = $this->renderStatement ( 'select', $query );
			$sql = $this->setLimit ( $sql, $mlimit, $moffset );
			echo $sql; 
			if ($isCount) 
				$returnArr = $this->fetchRow ( $sql );
			else
				$returnArr = $this->fetchAll ( $sql );
		} catch (Exception $ex) {
			var_dump($ex);
		}
		
		return $returnArr;
	}
}
$options = array(
  "conditions" => array(
    "user.email" => "phamngoctuong1805@gmail.com"
  ),
  "orders" => "user.email,user.fullname",
  'groups' => "user_join.testjoin",
  "limit" => "5",
  "offset" => "1",
  "fields" => array(
    "user.email",
    "user.fullname"
  ),
  'joins' => array(
    "user_join" => array(
      "join_fields" => array(
        "testjoin",
        "testaddress"
      ),
      "type" => "LEFT",
      "main_key" => "id",
      "join_key" => "user_id"
    )
  )
);
$pi = Mysql::getInstance();
$test = $pi->select("user",$options,true);
echo '<pre>';
var_export($test);
echo '<pre>';

<?php
  class Mysql {
    public static $instance = null;
    public function __construct($config) {
      $this->config = $config;
      $this->connect();
    }
    public static function getInstance() {
      $config = array(
        "host" => "localhost",
        "username" => "root",
        "password" => "",
        "dbname" => "ticket",
        "encoding" => "utf8"
      );
      if(self::$instance === null) {
        self::$instance = new self($config);
      }
      return self::$instance;
    }
    public function connect() {
      $config = $this->config;
      $this->connected = false;
      $this->connection = mysqli_connect($config['host'], $config['username'], $config['password'], $config['dbname']);
      if(!$this->connection)  {
        $this->connected = true;
      }
      return $this->connected;
    }
    public function buildCondition($condition) {
      if(empty($condition)) return null;
      $match  = " = ";
      $returnArr = array();
      foreach ($condition as $key => $value) {
        $returnArr[] = $key . $match . "'$value'";
      }
      return " WHERE " . implode(" AND ", $returnArr);
    }
    public function renderStatement($type,$data)  {
      extract($data);
      switch ($type) {
        case 'select':
          return "SELECT {$fields} FROM {$table} {$joins} {$conditions} {$group} {$order}";
        break;
      }
    }
    public function select($myTable, $option, $isCount = false) {
      $table = $myTable;
      
      $myFields = isset($option['fields']) ? $option['fields'] : " * ";
      $condition = isset($option['conditions']) ? $option['conditions'] : null;
      $myJoins = isset($option['joins']) ? $option['joins'] : null;
      $orders = isset($option['orders']) ? $option['orders'] : null;
      $groups = isset($option['groups']) ? $option['groups'] : null;
      $conditions = $this->buildCondition($condition);
      $mylimit = isset($option['limit']) ? $option['limit'] : false;
      $moffset = isset($option['offset']) ? $option['offset'] : false;
      try {
        $fields = "";
        if(is_array($myFields)) {
          foreach ($myFields as $keyf => $myField) {
            $myFields[$keyf] = $table . "." . $myField;
          }
          $fields .= implode(", ",$myFields);
        }else {
          $fields .= $myFields;
        }
        if(isset($myJoins) && is_array($myJoins)) {
          foreach ($myJoins as $jTable => $joins) {
            foreach ($joins['join_fields'] as $key => $value) {
              $joins['join_fields'][$key] = $jTable . "." . $value;
            }
            $fields .=  ", " . implode(", ",$joins['join_fields']) . " ";
          }
        }
        $joins = $this->buildJoin($myTable, $myJoins);
        $order = $this->buildOrders ( $orders );
        $group = $this->buildGroups ( $groups );
        $query  = compact("fields","table","joins","conditions","group", "order");
        $sql = $this->renderStatement("select",$query);
        // $sql = $this->setLimit ( $sql, $mylimit, $moffset );
        echo $sql;
        if($isCount) {
          return $this->fetchRow($sql);
        }else {
          return $this->fetchAll($sql);
        }
      }
      catch(Exception $e) {
        echo 'Message: ' . $e->getMessage();
      }
    }
    public function setLimit($sql, $limit = false, $offset = false) {
      if ($limit) {
        $limitoffset  = '';
        if (! strpos ( strtolower ( $limit ), 'limit' ) || strpos ( strtolower ( $limit ), 'limit' ) === 0) {
          $limitoffset .= ' LIMIT ';
        }
        if ($offset) {
          $limitoffset .= ' ' . $offset . ',';
        }
        $limitoffset .= ' ' . $limit;
			  $sql = $sql . $limitoffset;
      }
      return $sql;
    }
    public function buildGroups($groups) {
      $sql = '';
      if (! is_array ( ! $groups ) && !empty($groups)) {
        $groups = preg_replace ( '/,\s+/', ',', $groups );
        $groups = explode ( ",", $groups );
      }
      if(!empty($groups)) {
        $str = "";
        foreach ( $groups as $value ) {
					$str .= ($value . ", ");
				}
        if (!empty($str)) $str = substr ( $str, 0, (strlen ( $str ) - strlen ( $str ) - 2) );
      }
      $sql = " GROUP BY " . $str;
      return $sql;
    }
    public function buildOrders ( $orders ) {
      $sql = '';
      if (! is_array ( ! $orders ) && $orders != "") {
        $match1ormore = preg_replace ( '/,\s+/', ',', $orders );
        $match1ormore = explode ( ",", $match1ormore);
        $orders = array ();
        foreach ( $match1ormore as $item ) {
          $itemArr = explode ( " ", $item );
          if (isset ( $itemArr [1] )) {
            $orders[$itemArr [0]] = $itemArr [1];
          }else {
            $orders[$itemArr [0]] = 'ASC';
          }
        }
        if (!empty($orders)) {
          $str = "";
          foreach ($orders as $key => $value) {
            $str .= ($key . " " . $value . ", ");
          }
          if ($str != "") $str = substr ( $str, 0, (strlen ( $str ) - strlen ( $str ) - 2) );
          $sql .= "ORDER BY " . $str;
        }
      }
      return $sql;
    }
    public function buildJoin($myTable, $myJoins) {
      $sql = "";
      if(!empty($myTable) && !empty($myJoins)) {
        foreach ($myJoins as $table => $joins) {
          $sql .= (isset($joins['type']) ? $joins['type'] : "") . " JOIN " . $table . " ON "  . $myTable . "." . $joins['main_key'] . " = " . $table . "." . $joins['join_key'];
        }
      }
      return $sql;
    }
    public function fetchRow($sql = null) {
      if(!empty($sql) && is_string($sql)) {
        if(!$this->execute($sql)) {
          return null;
        }
      }
      if(is_object($this->_result)) {
        $this->setResult($this->_result);
        return $this->fetchResult();
      }
    }
    public function fetchAll($sql) {
      $returnArr = array();
      if($this->execute($sql)) {
        while($row = $this->fetchRow()) {
          $returnArr[] = $row;
        }
      }
      return $returnArr;
    }
    public function execute($sql) {
      $this->_result = $this->_execute($sql);
      return $this->_result;
    }
    public function _execute($sql) {
      return mysqli_query($this->connection,$sql);
    }
    public function setResult($result) {
      $this->result = $result;
      $num_fields = mysqli_num_fields($result);
      $this->map = array();
      $i = 0;
      while($i < $num_fields) {
        $column = mysqli_fetch_field_direct($result,$i);
        $this->map[] = array($column->table,$column->name);
        $i++;
      }
    }
    public function fetchResult()  {
      $returnArr = array();
      if ($row = mysqli_fetch_row($this->result)) {
        foreach ($row as $index => $value) {
          list($table,$colunm) = $this->map[$index];
          $returnArr[$table][$colunm] = $value;
        }
      }
      return $returnArr;
    }
  }
  $options = array(
    "conditions" => array(
      "user.email" => "phamngoctuong1805@gmail.com"
    ),
    "orders" => "user.email,user.fullname",
    'groups' => "user_join.testjoin",
    "limit" => "5",
    "offset" => "1",
    "fields" => array(
      "user.email",
      "user.fullname"
    ),
    'joins' => array(
      "user_join" => array(
        "join_fields" => array(
          "testjoin",
          "testaddress"
        ),
        "type" => "LEFT",
        "main_key" => "id",
        "join_key" => "user_id"
      )
    )
  );
  $pi = Mysql::getInstance();
  $test = $pi->select("user",$options,true);
  echo '<pre>';
  var_export($test);
  echo '<pre>';
?>

<?php
  class Mysql {
    public static $instance = null;
    public function __construct($config) {
      $this->config = $config;
      $this->connect();
    }
    public static function getInstance() {
      $config = array(
        "host" => "localhost",
        "username" => "root",
        "password" => "",
        "dbname" => "ticket",
        "encoding" => "utf8"
      );
      if(self::$instance === null) {
        self::$instance = new self($config);
      }
      return self::$instance;
    }
    public function connect() {
      $config = $this->config;
      $this->connected = false;
      $this->connection = mysqli_connect($config['host'], $config['username'], $config['password'], $config['dbname']);
      if(!$this->connection)  {
        $this->connected = true;
      }
      return $this->connected;
    }
    public function buildCondition($condition) {
      if(empty($condition)) return null;
      $match  = " = ";
      $returnArr = array();
      foreach ($condition as $key => $value) {
        $returnArr[] = $key . $match . "'$value'";
      }
      return " WHERE " . implode(" AND ", $returnArr);
    }
    public function renderStatement($type,$data)  {
      extract($data);
      switch ($type) {
        case 'select':
          return "SELECT {$fields} FROM {$table} {$joins} {$conditions} {$group} {$order}";
        break;
      }
    }
    public function select($myTable, $option, $isCount = false) {
      $table = $myTable;
      
      $myFields = isset($option['fields']) ? $option['fields'] : " * ";
      $condition = isset($option['conditions']) ? $option['conditions'] : null;
      $myJoins = isset($option['joins']) ? $option['joins'] : null;
      $orders = isset($option['orders']) ? $option['orders'] : null;
      $groups = isset($option['groups']) ? $option['groups'] : null;
      $conditions = $this->buildCondition($condition);
      $mylimit = isset($option['limit']) ? $option['limit'] : false;
      $moffset = isset($option['offset']) ? $option['offset'] : false;
      try {
        $fields = "";
        if(is_array($myFields)) {
          $fields .= implode(", ",$myFields);
        }else {
          $fields .= $myFields;
        }
        if(isset($myJoins) && is_array($myJoins)) {
          foreach ($myJoins as $jTable => $joins) {
            foreach ($joins['join_fields'] as $key => $value) {
              $joins['join_fields'][$key] = $jTable . "." . $value;
            }
            $fields .=  ", " . implode(", ",$joins['join_fields']) . " ";
          }
        }
        $joins = $this->buildJoin($myTable, $myJoins);
        $order = $this->buildOrders ( $orders );
        $group = $this->buildGroups ( $groups );
        $query  = compact("fields","table","joins","conditions","group", "order");
        $sql = $this->renderStatement("select",$query);
        $sql = $this->setLimit ( $sql, $mylimit, $moffset );
        echo $sql;
        if($isCount) {
          return $this->fetchRow($sql);
        }else {
          return $this->fetchAll($sql);
        }
      }
      catch(Exception $e) {
        echo 'Message: ' . $e->getMessage();
      }
    }
    public function setLimit($sql, $limit = false, $offset = false) {
      if ($limit) {
        $limitoffset  = '';
        if (! strpos ( strtolower ( $limit ), 'limit' ) || strpos ( strtolower ( $limit ), 'limit' ) === 0) {
          $limitoffset .= ' LIMIT ';
        }
        if ($offset) {
          $limitoffset .= ' ' . $offset . ',';
        }
        $limitoffset .= ' ' . $limit;
			  $sql = $sql . $limitoffset;
      }
      return $sql;
    }
    public function buildGroups($groups) {
      $sql = '';
      if (! is_array ( ! $groups ) && !empty($groups)) {
        $groups = preg_replace ( '/,\s+/', ',', $groups );
        $groups = explode ( ",", $groups );
      }
      if(!empty($groups)) {
        $str = "";
        foreach ( $groups as $value ) {
					$str .= ($value . ", ");
				}
        if (!empty($str)) $str = substr ( $str, 0, (strlen ( $str ) - strlen ( $str ) - 2) );
      }
      $sql = " GROUP BY " . $str;
      return $sql;
    }
    public function buildOrders ( $orders ) {
      $sql = '';
      if (! is_array ( ! $orders ) && $orders != "") {
        $match1ormore = preg_replace ( '/,\s+/', ',', $orders );
        $match1ormore = explode ( ",", $match1ormore);
        $orders = array ();
        foreach ( $match1ormore as $item ) {
          $itemArr = explode ( " ", $item );
          if (isset ( $itemArr [1] )) {
            $orders[$itemArr [0]] = $itemArr [1];
          }else {
            $orders[$itemArr [0]] = 'ASC';
          }
        }
        if (!empty($orders)) {
          $str = "";
          foreach ($orders as $key => $value) {
            $str .= ($key . " " . $value . ", ");
          }
          if ($str != "") $str = substr ( $str, 0, (strlen ( $str ) - strlen ( $str ) - 2) );
          $sql .= "ORDER BY " . $str;
        }
      }
      return $sql;
    }
    public function buildJoin($myTable, $myJoins) {
      $sql = "";
      if(!empty($myTable) && !empty($myJoins)) {
        foreach ($myJoins as $table => $joins) {
          $sql .= (isset($joins['type']) ? $joins['type'] : "") . " JOIN " . $table . " ON "  . $myTable . "." . $joins['main_key'] . " = " . $table . "." . $joins['join_key'];
        }
      }
      return $sql;
    }
    public function fetchRow($sql = null) {
      if(!empty($sql) && is_string($sql)) {
        if(!$this->execute($sql)) {
          return null;
        }
      }
      if(is_object($this->_result)) {
        $this->setResult($this->_result);
        return $this->fetchResult();
      }
    }
    public function fetchAll($sql) {
      $returnArr = array();
      if($this->execute($sql)) {
        while($row = $this->fetchRow()) {
          $returnArr[] = $row;
        }
      }
      return $returnArr;
    }
    public function execute($sql) {
      $this->_result = $this->_execute($sql);
      return $this->_result;
    }
    public function _execute($sql) {
      return mysqli_query($this->connection,$sql);
    }
    public function setResult($result) {
      $this->result = $result;
      $num_fields = mysqli_num_fields($result);
      $this->map = array();
      $i = 0;
      while($i < $num_fields) {
        $column = mysqli_fetch_field_direct($result,$i);
        $this->map[] = array($column->table,$column->name);
        $i++;
      }
    }
    public function fetchResult()  {
      $returnArr = array();
      if ($row = mysqli_fetch_row($this->result)) {
        foreach ($row as $index => $value) {
          list($table,$colunm) = $this->map[$index];
          $returnArr[$table][$colunm] = $value;
        }
      }
      return $returnArr;
    }
  }
  $options = array(
    "conditions" => array(
      "user.email" => "phamngoctuong1805@gmail.com"
    ),
    "orders" => "user.email,user.fullname",
    'groups' => "user_join.testjoin",
    "limit" => "5",
    "offset" => "1",
    "fields" => array(
      "user.email",
      "user.fullname"
    ),
    'joins' => array(
      "user_join" => array(
        "join_fields" => array(
          "testjoin",
          "testaddress"
        ),
        "type" => "LEFT",
        "main_key" => "id",
        "join_key" => "user_id"
      )
    )
  );
  $pi = Mysql::getInstance();
  $test = $pi->select("user",$options,true);
  echo '<pre>';
  var_export($test);
  echo '<pre>';
?>