Bước 10: Giải thích thêm phương thức renderStatement trong class Mysql (ok)

<?php  
	require_once 'database.php';
	class Mysql {
		var $config = array();
		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', 
	  	'!=', 
	  	'<>'
	  );
	  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;
	  }
	  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;
	    }
	    if (!empty($config['encoding'])) {
	      $this->setEncoding($config['encoding']);
	    }
	    return $this->connected;
	  }
    function setEncoding($enc) {
	    return $this->_execute('SET NAMES ' . $enc) != false;
	  }
	  function _execute($sql) {
	    if (preg_match('/^\s*call/i', $sql)) {
	      return $this->_executeProcedure($sql);
	    } else {
	      return mysqli_query($this->connection, $sql);
	    }
	  }
	  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;
	  }
	  function select($myTable, $options = array(), $isCount = false) {
	  	// $options is
	  	// 	array (
			//   'conditions' => 
			//   array (
			//     'email' => '[e-mail]',
			//     'password' => '7c4a8d09ca3762af61e59520943dc26494f8941b',
			//   ),
			// )
	    // 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;
	      // $table is user
	      $alias     = $joins = $order = $group = $limit = "";
	      $fields    = "";
	      if (is_array($myFields)) {
	        $fields = implode(', ', $myFields);
	      } else {
	        $fields = $myFields;
	      }
	      // $fields is *
	      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);
	      // $conditions is " WHERE  email = '[e-mail]' AND  password = '7c4a8d09ca3762af61e59520943dc26494f8941b' "
	      $order      = $this->buildOrders($orders);
	      // $order is NULL
	      $group      = $this->buildGroups($groups);
	      // $group is NULL
	      $joins      = $this->buildJoins($myTable, $mJoins);
	      // $myTable is 'user'
	      // $mJoins is NULL
	      // $joins is NUll
	      $tmpTable   = explode('_', $table);
	   		// array (
				//   0 => 'user',
				// )
	      $alias      = array();
	      foreach ($tmpTable as $tmp) {
	        $alias[] = ucfirst($tmp);
	      }
	      $alias = implode($alias);
	      // $alias is User
	      $query = compact('table', 'alias', 'joins', 'fields', 'conditions', 'joins', 'group', 'order', 'limit');
	      $sql   = $this->renderStatement('select', $query);
	      // $sql is " SELECT * FROM user User WHERE  email = '[e-mail]' AND  password = '7c4a8d09ca3762af61e59520943dc26494f8941b' "
	      $sql   = $this->setLimit($sql, $mlimit, $moffset);
	      // $sql is " SELECT * FROM user User WHERE  email = '[e-mail]' AND  password = '7c4a8d09ca3762af61e59520943dc26494f8941b' "
	      if ($isCount)
	        $returnArr = $this->fetchRow($sql);
	      else
	        $returnArr = $this->fetchAll($sql);
	    		//   $returnArr is array (
					//   0 => 
					//   array (
					//     'User' => 
					//     array (
					//       'id' => '1',
					//       'email' => '[e-mail]',
					//       'password' => '7c4a8d09ca3762af61e59520943dc26494f8941b',
					//       'fullname' => 'Admin',
					//       'address' => 'Ha Nam Viet Nam',
					//       'is_admin' => '1',
					//       'created' => '2020-04-02 18:59:20',
					//       'modified' => '2020-04-02 18:59:20',
					//     ),
					//   ),
					// )
	    }
	    catch (Exception $ex) {
	      var_dump($ex);
	    }
	    return $returnArr;
	  }
	  public function renderStatement($type, $data) {
	    extract($data);
	    $aliases = null;
	    switch (strtolower($type)) {
	      case 'select':
	      	// SELECT u.`email`, m.title FROM `user` as u JOIN movie_category as m WHERE u.id =1 LIMIT 1
	        return "SELECT {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order} {$limit}";
	        break;
	    }
	  }
	  protected function buildConditions($condition) {
	  	// $condition is
	  	// 	array (
			//   'email' => '[e-mail]',
			//   'password' => '7c4a8d09ca3762af61e59520943dc26494f8941b',
			// )
	    if (empty($condition)) return;
	    $sql            = '';
	    $index          = 0;
	    $conditionArray = array();
	    foreach ($condition as $field => $sub) {
	      $math   = '';
	      $subVal = explode(' ', $sub);
	      // $subVal is
	   		// array (
				//   0 => '[e-mail]',
				// )
	      if (!in_array($subVal[0], $this->keywords)) {
	        $math = '=';
	      }
	      $conditionArray[] = " " . $field . " " . $math . " '" . $sub . "'";
	   		// array (
				//   0 => "email = '[e-mail]'",
				//   1 => "password = '7c4a8d09ca3762af61e59520943dc26494f8941b'",
				// )
	    }
	    return " WHERE " . implode(' AND ', $conditionArray);
	    // "WHERE  email = '[e-mail]' AND  password = '7c4a8d09ca3762af61e59520943dc26494f8941b'"
	  }
	  protected function buildOrders($orders) {
			// $orders is null
	    $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;
	  }
	  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;
	  }
	  protected function buildJoins($mainTable, $joins) {
	  	// $mainTable is 'user'
	  	// $joins is NULL
	    $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;
	  }
	}
?>