php的mysql操作类，支持连贯操作，自动分页，防sql注入，超好用的where计算
这个类文件来自于fastmvc的sql操作类，类似thinkphp的操作类，可以直接提取完全直接提取，如果写小型工程不想用php框架，是一个不错的选择
<?php
// FastMVC
// Copyright (c) 2006~2016 http://www.fastmvc.com All rights reserved
namespace core;
class Query{
public $conn;
public $sqls = [];
public $data = [];
public $clear = true;
public $debug = true;
public $options = [];
public function __construct($config=[]){
$this->connect(array_merge($this->config,$GLOBALS['_conf']['sql_conf'],$config));
}
public function table($table){
$this->options['table'] = $this->config['prefix'].$table;
return $this;
}
public function field($field=null){
if($field)$this->options['field']=$field;
return $this;
}
public function limit($limit=null,$value=null){
if(is_null($value)){
$this->options['limit']=$limit;
}else{
$this->options['limit']=$limit.','.$value;
}
return $this;
}
public function order($order=null,$value=null){
if(strpbrk($order,'( )')){
$this->options['order']= $order ;
}elseif(is_null($value)){
$this->options['order']='`'.$order.'`';
}else{
$this->options['order']='`'.$order.'` '.$value;
}
return $this;
}
public function page($sub=10,&$page,$size=10){
$this->clear=false;
$page= \core\Request::pagelist($sub,$this->count(),$size);
$this->limit($page['limit']);
return $this;
}
public function save($data=null,$key='id'){
$this->options['data']=$data;
return $this->query('save');
}
public function data($data=null){
if(is_array($data))$this->options['data']=$data;
return $this;
}
public function insert($data=null){
if(!empty($data))$this->options['data']=$data;
return $this->query('insert');
}
public function update($data=null){
if(!empty($data)){
if(isset($data['id'])&&empty($this->options['where'])){
$this->where($data['id']);
unset($data['id']);
}
$this->options['data']=$data;
}
return $this->query('update');
}
public function count($field=null){
return $this->method('COUNT',$field);
}
public function max($field=null){
return $this->method('MAX',$field);
}
public function min($field=null){
return $this->method('MIN',$field);
}
public function avg($field=null){
return $this->method('AVG',$field);
}
public function sum($field=null){
return $this->method('SUM',$field);
}
public function method($method,$field=null){
if($field)$this->options['field']=$field;
$this->options['method']=$method;
return $this->query('method');
}
public function find($name=null,$action=null,$value=null){
if($name)$this->where($name,$action,$value);
return $this->query('find')[0];
}
public function select($name=null,$action=null,$value=null){
if($name)$this->where($name,$action,$value);
return $this->query('select');
}
public function delete($name=null,$action=null,$value=null){
if($name)$this->where($name,$action,$value);
if($this->options['where'])return $this->query('delete');
}
public function escape($str){
if(is_numeric($str)){
return $str;
}elseif(is_string($str)){
return '"'.@mysql_escape_string($str).'"';
}elseif(is_bool($str)){
return mysql_escape_string($str);
}elseif(is_null($str)){
return 'NULL';
}
return '"undefined"';
}
public function joinEscape($arr){
if(is_string($arr)){
$arr=explode(',',$arr);
}
foreach($arr as &$v)$v=$this->escape($v);
return implode(',',$arr);
}
public function where($logic=null,$name=null,$action=null,$value=null){
$arg=is_array($name)?$name:func_get_args();
if(!(is_string($arg[0])&&in_array($arg[0],$this->operator))){
array_unshift($arg,'AND');
}
if(is_numeric($arg[1])){
$str= '`id` = '.$arg[1];
}elseif(is_string($arg[1])){
if(is_string($arg[2])||is_numeric($arg[2])){
if(isset($arg[3])&&in_array($arg[2],['not in','not like','NOT IN','NOT LIKE','in','like','IN','LIKE','<','>','<>','!=','='])){
$arg[2]=strtoupper($arg[2]);
if('IN'==$arg[2]){
$str='`'.$arg[1].'` IN ('.$this->joinEscape($arg[3]).')';
}elseif('LIKE'==$arg[2]){
$str='`'.$arg[1].'` LIKE '.$this->escape($arg[3]);
}else{
$str='`'.$arg[1].'` '.$arg[2].' '.$this->escape($arg[3]);
}
$this->options['keys'].=$arg[1];
}elseif(strpos($arg[1],'|')){
$arg2=$this->escape($arg[2]);
$str= '(`'.str_replace('|','` = '.$arg2.' OR `',$arg[1]).'` = '.$arg2.')';
}else{
$str= '`'.$arg[1].'` = '.$this->escape($arg[2]).'';
$this->options['keys'].=$arg[1];
}
}elseif(is_null($arg[2])){
if(strpos($arg[1],'OR')||strpos($arg[1],'AND')){
$str= '('.$arg[1].')';
}else{
$str= $arg[1];
}
}
}
if(!isset($str)){
$arr=[];
foreach($arg as $k=>$v){
if(is_array($v)){
$arr[]=$this->where('CALLBACK',$v);
}elseif(!is_int($k)){
$arr[]=$this->where('CALLBACK',[$arg[0],$k,$v]);
}
}
if(count($arr)>0){
$str=''.implode($arr,' '.strtoupper($arg[0]).' ').'';
if($this->options['where']&&count($arr)>1){
$str='('.$str.')';
}
}
}
if('CALLBACK'==$logic){
return $str;
}
if($str){
$logic=is_string($logic)&&in_array($logic,$this->operator)?strtoupper($logic):'AND';
if(isset($this->options['where'])){
if(strpos($this->options['where'],' OR ')){
$this->options['where']='('.$this->options['where'].') '.$logic.' '.$str;
}elseif($this->options['where']){
$this->options['where'].=' '.$logic.' '.$str;
}
}else{
$this->options['where'] = $str;
}
}
return $this;
}
public function connect($config){
try {
$this->conn = new \PDO($config['type'].':host='.$config['host'].';dbname='.$config['table'],$config['user'],$config['pass']);
$this->conn->exec('SET character_set_connection='.$config['charset'].', character_set_results='.$config['charset'].', character_set_client=binary');
} catch (PDOException $e){
throw new \Exception('数据库连接错误: '.$e->getMessage(),"行号: ".$e->getLine());
}
return $this;
}
public function query($sql){
try{
if(APP_DEBUG){
Debug::consuming('query');
}
if(isset($this->template[$sql])){
$sql=$this->template[$sql];
}
$sql=$this->build($sql);
if($this->clear){
//p($this->options);
$this->options=[];
}else{
$this->clear=true;
}
if(in_array($action=strtoupper(substr($sql,0,6)),['SELECT','INSERT','UPDATE','DELETE'])){
if('SELECT' == $action){
$sth=$this->conn->query($sql);
if($sth){
$result=$sth->fetchAll(\PDO::FETCH_ASSOC);
if(isset($result[0]['methodback'])){
$result=$result[0]['methodback'];
}
}else{
$result=null;
}
}else{
$result=$this->conn->exec($sql);
if('INSERT'==$action){
$result=$this->conn->lastInsertId();
}
}
}
if(APP_DEBUG){
$GLOBALS['_bugs']['sql'][]='['.sprintf("%.9f", Debug::consuming('query')*1000 ).':'.(is_numeric($result)?$result:count($result)).'] '.$sql;
if ($this->conn->errorCode() != '00000'){
$err=$this->conn->errorInfo();
Debug::errorBar('数据库错误['.$err['1'].']',$err[2],$sql,$err[0],null);
}
}
return $result;
}catch (PDOException $e) {
throw new \Exception('数据库操作错误: '.$e->getMessage(),"行号: ".$e->getLine());
}
return null;
}
public function whereOr($name=null,$action=null,$value=null){
$this->where('OR',$name,$action,$value);
}
public function build($tpl=''){
$sql=preg_replace_callback('/(\s)?%([a-z]+?)%/',function($a){
switch($a[2]){
case 'where' :
if(isset($this->options['where'])){
return $a[1].'WHERE '.$this->options['where'];
}
break;
case 'table' :
if(isset($this->options['table'])){
return $a[1].'`'.$this->options['table'].'`';
}
break;
case 'value':
if(isset($this->options['data'])){
$keyStr=$valstr=[];
foreach($this->options['data'] as $key=>$val){
array_push($keyStr,'`'.$key.'`');
array_push($valstr,$this->escape($val));
$this->options['keys'].=$key;
}
return $a[1].'('.implode(',',$keyStr).')VALUE('.implode(',',$valstr).')';
}
break;
case 'set':
if(isset($this->options['data'])){
$valstr=[];
foreach($this->options['data'] as $key=>$val){
$valstr[]= '`'.$key.'` = '.$this->escape($val);
$this->options['keys'].=$key;
}
return $a[1].implode(' , ',$valstr).'';
}
break;
case 'field':
if(isset($this->options['field']) && '*'!==$this->options['field']){
return $a[1].'`'.str_replace(',','`,`',$this->options['field']).'`';
}else{
return $a[1].'*';
}
break;
case 'limit':
if(isset($this->options['limit'])){
return $a[1].'LIMIT '.$this->options['limit'];
}
break;
case 'order':
if(isset($this->options['order'])){
return $a[1].'ORDER BY '.$this->options['order'];
}
break;
case 'method':
if(isset($this->options['method'])){
return $a[1].$this->options['method'];
}
break;
}
return '';
},$tpl);
//防sql注入
if(isset($this->options['keys'])){
if(preg_match('/[^\w,]/',$this->options['keys'])){
throw new \Exception('数据库操作违法');
}
}
$this->sqls[]=$sql;
return $sql;
}
//运算符
public function match($str,$key='spell',&$split=null){
if(!$str)return $this;
$split = $this ->splitWord($str);
return $this->where('MATCH(`'.$key.'`)AGAINST('.$this->escape($split['spell']).')');
}
//分词
public function splitWord($str,$sub=50){
if(strlen($str)==mb_strlen($str,'utf8'))return ['text'=>$str,'split'=>$str,'spell'=>$str];
return \lib\Phpanalysis::splitWord(mb_substr($str,0,$sub,'utf-8'));
}
//debug
public function sql(){
return $this->sqls;
}
//清理机制
public function __destruct(){
$this->conn=null;
}
//运算符
public $operator = ['or','and','not','OR','AND','NOT'];
//模版
public $template = [
'find' => "SELECT * FROM `` LIMIT 1;",
'save' => "INSERT INTO `` ON DUPLICATE KEY UPDATE;",
'method' => "SELECT(*) AS methodback FROM ``;",
'select' => "SELECT * FROM ``;",
'insert' => "INSERT INTO ``;",
'update' => "UPDATE `` SET;",
'delete' => "DELETE FROM ``;",
];//模版
public $templatex = [
'find' => "SELECT *\nFROM ``\n\n LIMIT 1;",
'save' => "INSERT INTO ``\n\nON DUPLICATE KEY\nUPDATE;",
'method' => "SELECT(*) AS methodback\nFROM ``\n\n;",
'select' => "SELECT *\nFROM ``\n\n\n;",
'insert' => "INSERT INTO ``\n\n;",
'update' => "UPDATE ``\nSET\n;",
'delete' => "DELETE FROM ``\n\n\n;",
];
//配置
public $config=[
'type' => 'mysql',
'host' => 'localhost:3306',
'user' => 'root',
'pass' => '',
'table' => '',
'prefix' => '',
'charset' => 'utf8',
];
}
