<?php
header('content-type:text/html;charset=utf8');
ini_set('display_errors', true);
$config = [
'host' => 'localhost',
'user' => 'root',
'password' => 'phpcj',
'dbname' => 'learn',
'port' => 3306
];
final Class DB
{
private static $db;
private function __clone(){}
private function __construct() {
global $config;
$mysqli = new Mysqli($config['host'], $config['user'], $config['password'],$config['dbname'], $config['port']);
//如果没有错误则返回0如果有错误，那么返回对应的错误编号
if($mysqli->connect_errno) {
die('连接错误，错误信息是'.$mysqli->connect_error);
}
//设置字符集
$mysqli->set_charset('utf8');
self::$db = $mysqli;
}
static function getInstance()
{
if (!self::$db instanceof Mysqli) {
new self();
}
return self::$db;
}
}
class Expression {
private $str;
public function __construct(string $str) {
$this->str = $str;
}
public function getStr() {
return $this->str;
}
}
trait Filter {
private $sql;
private $currentType; //0为自写的sql语句，1为查询，2为插入，3为修改，4为删除
private $selectSql = null;
private $whereSql = null;
private $leftJoinSql = null;
private $rightJoinSql = null;
private $innerJoinSql = null;
private $groupBySql = null;
private $havingSql = null;
private $orderBySql = null;
private $table = null;
private function quoteField($val, $split = null) {
if(is_object($val) && $val instanceof Expression) {
return $val->getStr();
}
if(!$split){
return sprintf('`%s`',trim($val));
}
$tempArr = explode($split,$val);
$newArr = [];
$keyWord = ['AS', 'ASC', 'DESC'];
array_walk($tempArr, function($value)use(&$newArr, $keyWord) {
$temp = null;
if($value) {
switch(true) {
case in_array(strtoupper($value), $keyWord): array_push($newArr, strtoupper($value)); break;
case stripos($value, '.'): array_push($newArr, $this->quoteField($value, '.')); break;
default : array_push($newArr, sprintf('`%s`',trim($value)));
}
}
});
return implode($newArr, $split);
}
private function quoteValue($val) {
$str = null;
switch(true) {
case preg_match('/\'\"/', $val): $str = $val; break;
case is_string($val) : $str = sprintf('"%s"', $val); break;
case is_numeric($val) : $str = $val;
}
return $str ?? '';
}
private function commonDeal(array $args, &$handle) {
$arr = [];
foreach($args as $key => $val){
array_push($arr, $this->quoteField($val,','));
}
count($arr) > 0 ? $handle = implode($arr, ','): null;
}
/**select 语句
* @return $this
*/
public function select () {
$args = is_array(func_get_arg(0))? func_get_arg(0) : func_get_args();
$arr = [];
$isAll = false;
foreach($args as $key=>$val) {
if(is_string($val) && preg_match('/\(.*?\)/', $val)) {
$str = preg_replace_callback('/([^\(\)]*)\((.*?)\)([^\(\)]*)/',function ($argArr) {
list(, $f, $s, $t) = $argArr;
if($s === '*') {
return sprintf('%s(%s) %s' , strtoupper($f), $s, $this->quoteField($t, ' '));
}
return sprintf('%s(%s) %s',strtoupper($f), $this->quoteField($s, ' '), $this->quoteField($t, ' '));
}, $val);
array_push($arr, $str);
}else{
array_push($arr, $this->quoteField($val, ' '));
if($val === '*'){
$isAll = true;
break;
}
}
}
$this->currentType = 1;
count($arr) > 0 ? $this->selectSql = ($isAll === true ? true : implode($arr, ',')): null;
return $this;
}
/**group by 方法
* @return $this
*/
public function groupBy() {
$args = is_array(func_get_arg(0))? func_get_arg(0) : func_get_args();
$this->commonDeal($args, $this->groupBySql);
$this->groupBySql =$this->groupBySql? sprintf(' GROUP BY %s', $this->groupBySql) : '';
return $this;
}
/**order by 方法
* @return $this
*/
public function orderBy(){
$arg = func_get_arg(0);
$arr = [];
if(is_array($arg)) { //如果传入的是一个数组
array_walk($arg, function($val, $key)use(&$arr) {
array_push($arr, sprintf('%s %s', $this->quoteField($key), $val == 3? 'SORT_DESC' : 'SORT_ASC'));
});
} else if(is_string($arg)) { //如果传入的是一个字符串
$tempArr = explode(',', $arg);
array_walk($tempArr, function($val)use(&$arr) {
array_push($arr, $this->quoteField($val, ' '));
});
}
count($arr) > 0? $this->orderBySql = sprintf(' ORDER BY %s', implode(',', $arr)) : null;
return $this;
}
/**from 方法
* @param string $table
* @return $this
*/
public function from (string $table) {
$this->table = $this->quoteField($table, ' ');
return $this;
}
/**where 与 having 的内部处理函数
* @param $arg1
* @param null $arg2
* @return array|string|string[]|null
*/
private function conditionFilter($arg1, $arg2 = null) {
if($arg1 && !$arg2) {
if(is_string($arg1)) {
return $arg1;
} else if(is_array($arg1)) {
$tempArr = [];
$setArr1 = ['LIKE', '>=', '<=', '=', '>', '<'];
$setArr2 = ['AND', 'OR'];
$link = strtoupper(reset($arg1));
if(in_array($link, $setArr1)) {
$key = count($arg1) >= 2 ? next($arg1) : null;
$val = count($arg1) >= 3 ? next($arg1) : null;
$sign = count($arg1) >= 4 ? next($arg1) : true;
return !$key || !$val ? '' : (strtoupper(trim($link)) === 'LIKE'? "{$this->quoteField($key)} LIKE ".($sign? "\"%{$val}%\"" : "\"{$val}\""):"{$this->quoteField($key,' ')} {$link} {$val}");
}else if (in_array($link, $setArr2)) {
while($temp = next($arg1)){
array_push($tempArr, is_array($arg1)? $this->conditionFilter($temp): $temp);
}
$link = strtoupper($link);
return implode(' '.$link.' ',$tempArr);
}
foreach($arg1 as $key=>$val) {
array_push($tempArr, sprintf('%s = %s', $this->quoteField($key), $this->quoteValue($val)));
}
return implode(',', $tempArr);
}
}else if($arg2 && is_array($arg2)) {
return preg_replace_callback('/(?:^|,{1}|\s)?(.*?)(=|>|<|<=|>=|\s*like\s*){1}\s*(:[^,]*)\s*(,{1}|$)/', function($all)use($arg2){
list(, $name, $sign, $key, $split) = $all;
if(!$arg2[trim($key)]) {
die("where语句中的{$key}没有对应的值,是否是因为数组引号里空格的原因");
}
strtoupper(trim($sign)) === 'LIKE'? $arg2[trim($key)] = "\"%{$arg2[trim($key)]}%\"" : null;
return sprintf('%s %s %s %s', $this->quoteField($name,'.'), strtoupper($sign), $arg2[trim($key)], $split);
}, $arg1);
}
return '';
}
public function where(){
$count = func_num_args();
$first = $count >= 1? func_get_arg(0) : null;
$second =$count >= 2? func_get_arg(1) : null;
$res = $this->conditionFilter($first, $second);
$this->whereSql =$res ? ' WHERE '.$res : '';
return $this;
}
public function having(){
$count = func_num_args();
$first = $count >= 1? func_get_arg(0) : null;
$second =$count >= 2? func_get_arg(1) : null;
$res = $this->conditionFilter($first, $second);
$this->havingSql =$res ? ' Having '.$res : '';
return $this;
}
private function dealJoinFunc($arg1, $arg2) {
if(!$arg1 || !$arg2) {
die('表的连接的参数有问题');
}
if(is_string($arg1)) {
list($first, $second) = preg_split('/\s*=\s*/', $arg2);
return sprintf('%s ON %s = %s', $this->quoteField($arg1,' '), $this->quoteField($first,'.'),$this->quoteField($second, '.'));
}else if(is_array($arg1)) {
$val = reset($arg1);
$key = key($arg1);
list($first, $second) = preg_split('/\s*=\s*/', $arg2);
if($val instanceof Query){
$str = sprintf('(%s) AS %s', $val->getSql(), $this->quoteField($key));
return sprintf('%s ON %s = %s', $str, $this->quoteField($first,'.'),$this->quoteField($second, '.'));
}
}
return '';
}
public function leftJoin() {
$count = func_num_args();
$first = $count >= 1? func_get_arg(0) : null;
$second =$count >= 2? func_get_arg(1) : null;
$res = $this->dealJoinFunc($first, $second);
$this->leftJoinSql = $res ? ' LEFT JOIN '.$res : '';
return $this;
}
public function rightJoin() {
$count = func_num_args();
$first = $count >= 1? func_get_arg(0) : null;
$second =$count >= 2? func_get_arg(1) : null;
$res = $this->dealJoinFunc($first, $second);
$this->rightJoinSql = $res ? ' RIGHT JOIN '.$res : '';
return $this;
}
public function innerJoin() {
$count = func_num_args();
$first = $count >= 1? func_get_arg(0) : null;
$second =$count >= 2? func_get_arg(1) : null;
$res = $this->dealJoinFunc($first, $second);
$this->innerJoinSql = $res ? ' INNER JOIN '.$res : '';
return $this;
}
public function delete() {
$this->currentType = 4;
$count = func_num_args();
$table = $count >= 1? func_get_arg(0) : null;
$first = $count >= 2? func_get_arg(1) : null;
$second =$count >= 3? func_get_arg(2) : null;
$res = $this->conditionFilter($first, $second);
$this->sql = sprintf('DELETE FROM %s', $this->quoteField($table)).$this->whereSql =$res ? ' WHERE '.$res : '';
return $this;
}
public function update() {
$this->currentType = 3;
$count = func_num_args();
$table = $count >= 1? func_get_arg(0) : null;
$first = $count >= 2? func_get_arg(1) : null;
$second =$count >= 3? func_get_arg(2) : null;
$third =$count >= 4? func_get_arg(3) : null;
$res = $this->conditionFilter($second, $third);
$valArr = [];
if(is_string($first)) {
array_push($valArr, $first);
}else if (is_array($first)) {
array_walk($first, function($val, $key)use(&$valArr) {
array_push($valArr , sprintf('%s = %s', $this->quoteField($key), $this->quoteValue(trim($val))));
});
}
$this->sql = sprintf('UPDATE %s SET %s ', $this->quoteField($table), implode(',', $valArr)).$this->whereSql =$res ? ' WHERE '.$res : '';
return $this;
}
public function insert() {
$this->currentType = 2;
$count = func_num_args();
if($count < 2) {
die('传入的参数不足以启动Insert语句');
}
$table = $count >= 1? func_get_arg(0) : null;
$first = $count >= 2? func_get_arg(1) : null;
$keyArr = [];
$valArr = [];
if(is_string($first)) {
array_push($valArr, $first);
}else if (is_array($first)) {
array_walk($first, function($val, $key)use(&$keyArr, &$valArr) {
array_push($keyArr , $this->quoteField(trim($key)));
array_push($valArr , $this->quoteValue(trim($val)));
});
}
$this->sql = sprintf('INSERT INTO %s (%s) VALUES (%s) ', $this->quoteField($table), implode(',', $keyArr), implode(',', $valArr));
return $this;
}
public function getSql() {
if($this->currentType === 1){
$str = sprintf('SELECT %s FROM %s', $this->selectSql === true? '*' : $this->selectSql, $this->table);
$str.= $this->leftJoinSql.$this->whereSql.$this->groupBySql.$this->havingSql.$this->orderBySql;
$this->sql = $str;
}
return $this->sql;
}
}
Class Query {
use Filter;
private $db;
public function __construct() {
$this->db = DB::getInstance();
}
private function innerQuery() {
//执行查询操作,如果执行成功返回true,如果执行错误返回的是false
$this->getSql();
$res = $this->db->query($this->sql);
if(!$res) {
die("查询错误:{$this->db->error},sql 语句是{$this->sql}");
}
return $res;
}
public function all() {
$res = $this->innerQuery();
$resArr = [];
//把mysql对象转成结果并还条打印
while($row = $res->fetch_assoc()) {
$resArr[] = $row;
}
$res->free();
return $resArr;
}
public function one() {
$res = $this->innerQuery();
$resArr = [];
if($row = $res->fetch_assoc()){
array_push($resArr, $row);
}
$res->free();
return $resArr;
}
}
class Operate{
use Filter;
private $db;
public function __construct() {
$this->db = DB::getInstance();
}
private function innerOperation() {
$this->getSql();
$res = $this->db->query($this->sql);
if(!$res) {
die(sprintf('执行失败，错误是%s, sql语句是%s', $this->db->error, $this->sql));
}
return $this->db->affected_rows;
}
public function execute() {
return $this->innerOperation();
}
}
//$subQuery = (new Query())->select('count(*) as num','classid')->from('class')->groupBy('deptname');
//$data = (new Query())->select('c.deptname','t.num')->from('class As c')
// ->leftJoin(['t'=>$subQuery],'c.classid = t.classid')
// ->where(['>=', 't.num',2])
// ->getSql();
//$data = (new Operate())->update('student', ['name '=> 'haha ', 'age'=>30], 'studentid = :id ', [':id'=> 26])->getSql();
//$data = (new Operate())->insert('student',['name'=>'bbb', 'age'=>30, 'classid' => 3])->getSql();
$data = (new Operate())->delete('student', ['name'=>'haha'])->execute();
var_dump($data);
?>
浙公网安备 33010602011771号