简易封装mysqli操作数据库的方法

<?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);
?>