Xây dựng insert (ok)

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} {$group} {$order}";
        break;
        case 'create':
          return "INSERT INTO {$table} ({$fields}) VALUES ({$values})";
          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 insert($myTable, $data) {
      $query = array();
      $query["table"] = $myTable;
      $fields = array();
      $values = array();
      foreach ($data as $key => $value) {
        $fields[] = $key;
        $values[] = "'$value'";
      }
      $query['fields'] = implode(", ",$fields);
      $query['values'] = implode(", ",$values);
      $sql = $this->renderStatement ( 'create', $query );
      echo $sql; 
      if($this->execute($sql)) {
        return true;
      }else {
        return false;
      }
    }
    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" => "[e-mail]"
    ),
    "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();
  $data = array(
    "email" => "[e-mail]",
    "password" => "1234567",
    "testjoin" => "phamngoctuong1",
    "testaddress" => "Ha Noi 1",
    "testis_admin" => "0",
    "created" => "2021-10-06 19:30:14",
    "modified" => "2021-10-06 19:30:14",
    "user_id" => 2
  );
  $test = $pi->insert("user_join", $data);
  echo '<pre>';
  var_export($test);
  echo '<pre>';
?>