Xây dựng buildJoin (ok)

Phần 1

Nếu ghi như này

Kết quả là:

Nếu ghi như này thì kết quả sẽ là

Kết quả sẽ là

Phần 2:

<?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;
      $orders = isset($option['orders']) ? $option['orders'] : 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);
        $order = $this->buildOrders ( $orders );
        $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 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;
        }
      }
      echo $sql;
      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" => "[e-mail]"
    ),
    "orders" => "user.email,user.fullname",
    "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>';
?>

Tổng kết:

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