Xây dựng lại $fields, $joins (ok)

Ok

File để thực hành :)

4KB

Mysql-practice.php

Hoặc code để thực hành

<?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} {$conditions}";
        break;
      }
    }
    public function select($myTable, $option, $isCount = false) {
      $table = $myTable;
      $fields = isset($option['fields']) ? $option['fields'] : " * ";
      $condition = isset($option['conditions']) ? $option['conditions'] : null;
      $conditions = $this->buildCondition($condition);
      try {
        $query  = compact("fields","table","conditions");
        $sql = $this->renderStatement("select",$query);
        if($isCount) {
          return $this->fetchRow($sql);
        }else {
          return $this->fetchAll($sql);
        }
      }
      catch(Exception $e) {
        echo 'Message: ' . $e->getMessage();
      }
    }
    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(
      "email" => "[e-mail]"
    )
  );
  $pi = Mysql::getInstance();
  $test = $pi->select("user",$options,false);
  echo '<pre>';
  var_export($test);
  echo '<pre>';
?>

Kết quả muốn như này :(

SELECT * , user_join.testjoin, user_join.testaddress FROM user LEFT JOIN user_join ON user.id = user_join.user_id WHERE user_join.email = '[e-mail]'

Chuẩn bị database:

6KB

ticket.sql

<?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}";
        break;
      }
    }
    public function select($myTable, $option, $isCount = false) {
      $table = $myTable;
      $myFields = isset($option['fields']) ? $option['fields'] : " * ";
      $condition = isset($option['conditions']) ? $option['conditions'] : null;
      $conditions = $this->buildCondition($condition);
      $myJoins = isset($option['joins']) ? $option['joins'] : null;
      try {
        $fields = "";
        if (is_array ( $myFields )) {
          $fields = implode ( ', ', $myFields );
        } else {
          $fields = $myFields;
        }
        if (isset ( $myJoins ) && is_array ( $myJoins )) {
          foreach ( $myJoins as $jTable => $join ) {
            foreach ($join ['join_fields'] as $key => $value) {
              $join ['join_fields'][$key] = $jTable .".". $value;
            }
            if (empty ( $jTable ) || empty ( $join ['join_key'] ) || empty ( $join ['join_fields'] )) continue;
            $fields .= ", " . implode(", ",$join ['join_fields']) . " ";
          }
        }
        $joins = $this->buildJoins ($myTable, $myJoins);
        $query  = compact("fields","table","joins","conditions");
        $sql = $this->renderStatement("select",$query);
        echo $sql;
        if($isCount) {
          return $this->fetchRow($sql);
        }else {
          return $this->fetchAll($sql);
        }
      }
      catch(Exception $e) {
        echo 'Message: ' . $e->getMessage();
      }
    }
    protected function buildJoins($myTable, $myJoins) {
      $sql = '';
      if (!empty($myTable) && !empty( $myJoins )) {
        foreach ( $myJoins as $k => $v ) {
          $sql .= " " . (isset ( $v ['type'] ) ? $v ['type'] : "") . " JOIN " . $k . " ON " . $myTable . "." . $v ['main_key'] . " = " . $k . "." . $v ['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_join.email" => "[e-mail]"
    ),
    '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,false);
  echo '<pre>';
  var_export($test);
  echo '<pre>';
?>

Kết quả:

Hoặc tham khảo code

<?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}";
        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;
      $conditions = $this->buildCondition($condition);
      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);
        $query  = compact("fields","table","joins","conditions");
        $sql = $this->renderStatement("select",$query);
        echo $sql;
        if($isCount) {
          return $this->fetchRow($sql);
        }else {
          return $this->fetchAll($sql);
        }
      }
      catch(Exception $e) {
        echo 'Message: ' . $e->getMessage();
      }
    }
    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" => "[e-mail]"
    ),
    "fields" => array(
      "email",
      "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>';
?>