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

<?phpheader ('content-type:text/html;charset=utf8');ini_set('display_errors', true);$config =['host' => 'localhost',

'user' => 'root',

'password' => 'phpcj',

'dbname' => 'learn',

'port' => 3306];final ClassDB

{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 functiongetInstance()

{if (!self::$dbinstanceof Mysqli) {newself();

}return self::$db;

}

}classExpression {private $str;public function __construct(string $str) {$this->str = $str;

}public functiongetStr() {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) && $valinstanceof 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 functionselect () {$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 functiongroupBy() {$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 functionorderBy(){$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', '>=', '<=', '=', '>', '= 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 functionwhere(){$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 functionhaving(){$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($valinstanceof 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 functionleftJoin() {$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 functionrightJoin() {$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 functioninnerJoin() {$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 functiondelete() {$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 functionupdate() {$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 functioninsert() {$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 functiongetSql() {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;

}

}ClassQuery {useFilter;private $db;public function__construct() {$this->db = DB::getInstance();

}private functioninnerQuery() {//执行查询操作,如果执行成功返回true,如果执行错误返回的是false

$this->getSql();$res = $this->db->query($this->sql);if(!$res) {die("查询错误:{$this->db->error},sql 语句是{$this->sql}");

}return $res;

}public functionall() {$res = $this->innerQuery();$resArr =[];//把mysql对象转成结果并还条打印

while($row = $res->fetch_assoc()) {$resArr[] = $row;

}$res->free();return $resArr;

}public functionone() {$res = $this->innerQuery();$resArr =[];if($row = $res->fetch_assoc()){array_push($resArr, $row);

}$res->free();return $resArr;

}

}classOperate{useFilter;private $db;public function__construct() {$this->db = DB::getInstance();

}private functioninnerOperation() {$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 functionexecute() {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);?>