当前位置:博客展示

php的mysql操作类,支持连贯操作,自动分页,防sql注入,超好用的where计算

作者: 创建时间:1970-01-01 08:00:00

这个类文件来自于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',
	];
}
相关评论
<··>
Ctrl + Enter快速提交
提交
还没有评论,赶快来坐沙发吧!
博客达人
网站目录
首页 讨论中心 帮助手册 下载中心 站内博客 用户管理
友情链接
在线网页工具
声明
电话:18729480012 地址:西安市高新路18号凯创国际A1802号 西安爱迪科技 陕ICP备14010060号2014-2025 版权所有