Xây dựng setLimit, offset (ok)
Muốn có kết quả giống thế này
<?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) {
$sql = "";
if ($limit) {
$limitoffset = '';
if (! strpos ( strtolower ( $limit ), 'limit' ) || strpos ( strtolower ( $limit ), 'limit' ) === 0) {
$limitoffset .= ' LIMIT ';
}
if ($offset) {
$limitoffset .= ' ' . $offset . ',';
}
$limitoffset .= ' ' . $limit;
$sql = $sql . $limitoffset;
}
echo '<pre>';
var_export($sql);
echo '<pre>';
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(
"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 hợp
<?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(
"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>';
?>Last updated
