使用mysqli对象支持预处理和事务封装
基于mysqli写的sql封装语句，可支持预处理和事务，可以输出最后执行的sql。
所有的条件以数组传递即为预处理语句，否则为原始语句执行传递字符串
调用方法如下：
$mysqlObj = new mysqliModel();
添加=》
$data['cat_name'] = '测试1111';
$data['spec'] = MD5('描述');
$data['cname'] = '执行三次?';
$data['cat_name'] = '修改测试2';
$result = $mysqliObj->add("category",$data);//第一个参数为表名，第二个参数为处理的数组，若post的数组都是就是表单数据即可不用传递，若部分需要进行加密等操作，只需要将键值写入数组并赋值处理后的数据
修改=>$result = $mysqliObj->save("category",$id,$data);//和添加方法使用一致，只是传递的id为主键值
update是基于add和save方法，可以在添加和修改时都使用$result = $mysqliObj->update("category",$data);
删除=>$result = $mysqliObj->del("category",$id);//主键id删除
$result = $mysqliObj->dels("category",$ids);//根据主键批量删除，返回删除总数。ids可以是一个数组，也可以是逗号分隔的字符串
$result = $mysqliObj->delc("category",$conditon);//根据条件删除
查找=>$result = $mysqObj->find("category",$id);//根据主键查询单条记录
$result = $mysqlObj->select("category");//查询表中所有值
当需要根据一定条件和排序等等限制条件时可以使用连贯操作如$result = $mysqlIbj->field('id')->where("id= 1")->order("id asc,cid desc")->limit(1,2)->select('category);\
where()中可传递数组，即为预处理
输出sql语句：echo $mysqlObj->getsql();或者echo $mysqlObj->_sql();
还有很多的的方法比如分页等等，这里就不详述了，可以自行查看代码
header("content-type:text/html;charset=utf-8");
/**
* autor：sujianbin
* 2016-09-01
* 采用mysqli对象方式连接
* MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理!其他的类型是不支持的!(alter table tablename ENGINE = InnoDB)
* 支持预处理技术,主要在连接和编译过程精简，还可以SQL防止注入,快速执行
*/
class mysqliModel{
private $mysqli = '';//当前mysqli对象
private $field = '';//需要查询的字段
private $where = '';//查询条件
private $order = '';//排序规则
private $limit = '';//查询数量
private $pri = '';//主键
private $table = '';//表名
private $params = array();//预处理数据
private static $host = 'localhost';//主机名或ip地址
private static $db_user = 'root';//用户名
private static $db_pass = 'root';//密码
private static $db_name = 'mymoban';//数据库名称
private $db_press = 'su_';//数据库表前缀
private $db_log = false;//是否开启日志
private $auto_commit = TRUE;//是否开启自动提交,不适用于查询操作,默认自动提交，如若关闭操作时请先调用方法关闭
private $debug = 1;//是否开启调试模式，开启后出现错误查询会出现自动打印出sql语句
/**
* 构造函数
*/
public function __construct($field='',$where='',$order='',$limit='') {
$this->field = $field;
$this->where = $where;
$this->order = $order;
$this->limit = $limit;
$this->mysqli = new mysqli(self::$host,self::$db_user,self::$db_pass,self::$db_name);
if($this->mysqli->connect_error){
echo "Failed to connect to MySQL: " . $this->mysqli->connect_error;exit;
}else{
$this->mysqli->query("SET names UTF8");
session_start();
error_reporting(E_ALL ^ E_NOTICE);
date_default_timezone_set("PRC");
}
}
/**
* __get()方法用来获取私有属性
*/
public function __get($property_name){
if(isset($this->$property_name)){
return($this->$property_name);
}else{
return null;
}
}
/**
* __set()方法用来设置私有属性
*/
public function __set($property_name,$value){
$this->$property_name = $value;
}
/**
* 析构函数
*/
public function __destruct(){
$this->destroy();
$this->mysqli->close();
}
/**
* 函数执行错误处理
* @param string $functionName 函数名称
* @param array $args 包含信息的数组
* @return 返回执行状态
*/
public function __call($functionName,$args){
if(strstr($functionName,'getFieldBy')){
$search = str_replace('getFieldBy','',$functionName);
return $this->getFieldBy($args,$search);
}else if(!function_exists($functionName)){
$msg = "你所调用的函数： ".$functionName."不存在";
echo $msg;exit;
}
}
/**
* 销毁变量
* @return 无
*/
protected function destroy(){
$this->db_log($this->_sql());
if($type == 0){
$array = array('field'=>'','where'=>'','order'=>'','limit'=>'','table'=>'','pri'=>'');
}
foreach($array as $key=>$value){
unset($this->$key);
}
}
/**
* 关闭自动提交事务处理
* @param string $auto_commit 是否自动提交
* @return 返回当前对象
*/
public function auto_commit($auto_commit = ''){
$this->auto_commit = $auto_commit?$auto_commit:$this->auto_commit;
$this->mysqli->autocommit(FALSE);
return $this;
}
/**
* 事务提交
* @return boolean 布尔型结果
*/
public function commit(){
if(!$this->mysqli->error){
$result = $this->mysqli->commit();
if($result){
return true;
}else{
return null;
}
}else{
//事务回滚
$this->mysqli->rollback();
return null;
}
}
/**
* 给表加上前缀
* @param string $table 表名
* @return string 返回完整等我表名
*/
protected function db_press($table){
return "`".$this->db_press.$table."`";
}
/**
* 获取毫秒技术
* @param string $format [description]
* @param [type] $utimestamp [description]
* @return [type] [description]
*/
private function udate($format = 'u', $utimestamp = null) {
if (is_null($utimestamp))
$utimestamp = microtime(true);
$timestamp = floor($utimestamp);
$milliseconds = round(($utimestamp - $timestamp) * 1000000);
return date(preg_replace('`(?
}
/**
* 写入日志
* @param string $sql 执行的sql语句
* @return 无
*/
private function db_log($sql){
if($this->db_log){
$file = 'cache/log.txt';
if(!file_exists($file)){
$myfile = fopen($file, "w");
}
$filesize = filesize($file)/1024/1024;//1M
if($filesize > 1){
file_put_contents($file,"");//防止日志太大
}
if(stripos("insert",'@'.$sql) || stripos("update",'@'.$sql)){
file_put_contents($file,$this->udate('Y-m-d H:i:s.u')." ".$sql."\r\n\r\n",FILE_APPEND | LOCK_EX);
}
}
}
/**
* 过滤字符
* @param string $item 需要过滤的字符
* @return string 返回过滤后的字符
*/
private function saddslashes($item){
$item=trim($item);
if(!get_magic_quotes_gpc()) $item = addslashes($item);
return $item;
}
/**
* 获取最后执行的sql语句
* @return string 返回执行的sql语句
*/
public function getsql(){
if(is_array($this->params)){
foreach($this->params as $k=>$v){
if($k != 0){
if(strpos($this->sqlx,'?')){
$sql = substr($this->sqlx,0,strpos($this->sqlx,'?')+1);
if(strpos($sql,'like ?')){
$this->sqlx = preg_replace("/\?/","'".$v."'",$this->sqlx,1);
}else{
$this->sqlx = preg_replace("/\?/",$v,$this->sqlx,1);
}
}
//$this->sqlx = preg_replace("/like \?/","like '".$v."'",$this->sqlx,1);
//$this->sqlx = preg_replace("/\?/",$v,$this->sqlx,1);
}
}
}
return $this->sqlx;
}
/**
* getsql函数的别名
* @return string 返回sql语句
*/
public function _sql(){
return $this->getsql();
}
/**
* 自定义sql语句执行
* @param string $com_sql 需要执行的语句
* @return 未开启事务时才有返回
*/
public function querySql($com_sql){
$results = $this->mysqli->query($com_sql);
$this->sqlx = $com_sql;
$this->destroy();
if($this->auto_commit != FALSE){
return $results;
}
}
/**
*公共函数获取表信息
*@param string $table 表名
*@param array $array 对提交的所有数据进行过滤
*/
private function common($table,$array=''){
$this->params = '';
$this->table = $this->db_press($table);
$query = $this->mysqli->query("DESC {$this->table}");
while($row = $query->fetch_array()){
$result[] = $row['Field'];
if($row['Key'] == 'PRI'){
$zhujian = $row['Field'];
}
}
$arr = $left = $right = '';
//获取前台处理后的数组
if(empty($array)){
$data = count($_POST) != 0 ? $_POST:'';
}else{
$data = count($_POST) != 0 ? $array + $_POST : $array;
}
$sqlR = '';
if(is_array($data)){
foreach ($data as $key => $value) {
//过滤字段处理
if(in_array($key,$result)){
//为字段加上`符号和过滤字段
$value = $this->saddslashes($value);
$left =$left. '`'.$key.'`'.',';
//$right =$right.$value.'+-+_=_';(废除字符串形式，易导致bug)
$right[] = $value;
$arr=$arr."`".$key."`=?".',';//专为修改设置
$sqlR .= '?,';//专为添加设置
}
}
}
$sqlR = substr($sqlR,0,-1);
$left = substr($left,0,-1);
//$right = substr($right,0,-1);
$sql = substr($arr,0,-1);
$temp['left'] = $left;
$temp['right'] = $right;
$temp['sql'] = $sql;
$temp['zhujian'] = $zhujian;
$temp['sqlR'] = $sqlR;
return $temp;
}
/**
* 需要查询的字段
* @param string $field 查询的字段
* @return $this 返回当前对象
*/
public function field($field){
if(!empty($field)){
if(strpos($field,',') && !strpos($field,'.')){
$field = explode(',',$field);
foreach ($field as $key => $value) {
$field[$key] = '`'.$value.'`';
}
$this->field = implode(',',$field);
}else if(strpos($field,'.')){
$this->field = $field;
}else{
$this->field = '`'.$field.'`';
}
}else{
$this->field = '*';
}
return $this;
}
/**
* 排序条件
* @param string $order 排序条件
* @return $this 返回当前对象
*/
public function order($order){
$this->order = $order?'ORDER BY '.$order :'';
return $this;
}
/**
* 查询数量
* @param int $offset 开始位置
* @param int $length 查询长度
* @return $this 返回当前对象
*/
public function limit($offset,$length){
$this->limit = $length?('LIMIT '.$offset.','.$length) :'';
return $this;
}
/**
* 查询条件
* @param array|string $condition 查询条件
* @return $this 返回当前对象
*/
public function where($condition=''){
$this->params = '';
if(!empty($condition)){
if(is_array($condition)){
$conditions = $this->condition($condition);
$this->where = $conditions['right'];
$this->where = $this->where?'and '.$this->where:'';
$this->params = $conditions['params'];
}else{
$this->where = 'and '.$condition;
}
}
return $this;
}
/**
* 解析条件数组
* @param array $condition 条件数组
* @return array 返回数组，包含右侧prepare语句和param数组
*/
public function condition($condition){
$right = '';
if(is_array($condition)){
foreach($condition as $k=>$v){
if(is_array($v)){
if(empty($v['description'])){
$right.="and `".$k."` ".$v['terms']." ? ";
$params[] = $v['value'];
}else{
if(is_array($v['value'])){
$right.='and (';
foreach($v['value'] as $k1=>$v1){
$params[] = $v1;
if(count($v['value']) != $k1+1){
$right.= "`".$k."` ".$v['terms'].' ? '.$v['description']." ";
}else{
$right.= "`".$k."` ".$v['terms'].' ?';
}
}
$right.=') ';
}else{
$right.=$v['description']." `".$k."` ".$v['terms']." ? ";
$params[] = $v['value'];
}
}
}else{
$right.="and `".$k."`".'=? ';
$params[] = $v;
}
}
$right = preg_replace('/and /', '', $right, 1); //只替换一次(去掉第一次出现的字符and )
}
$condition['right'] = &$right;
$condition['params'] = &$params;
return $condition;
}
/**
* 获取参数类型
* @param array $param 当前数组
* @param int 主键id
* @param int $type 0表示主键放在最后，1表示主键放在最前
* @return string 返回类型长字符串
*/
protected function paramType($param,$id,$type=0){
$arr = '';
if($type ==1 && $id){
$arr.='i';
}
if(is_array($param)){
foreach($param as $k=>$v){
if (ctype_digit((string)$v)){
$arr.= ($v <= PHP_INT_MAX) ? 'i' : 's';
}else if (is_numeric($v)){
$arr = $arr.'d';
}else{
$arr .='s';
}
}
}
if($type ==0 && $id){
$arr.='i';
}
return $arr;
}
/**
* 预处理引用参数
* @param string|array $params 需要处理的字符串
* @param int $id 主键
* @param int type 0表示主键放在最后，1表示主键放在最前
* @return array 返回魔术方法数组
*/
protected function bindParam($params,$id='',$type=0){
if($params){
if(is_array($params)){
$arrays[] = &$this->paramType($params,$id,$type);
if($type ==1 && $id){
$arrays[] = &$id;
}
foreach($params as $k=>$v){
$arrays[] = &$params[$k]; //注意此处的引用
}
}
// else{
// $params = explode('+-+_=_',$params);//传入字符含有相同的符号会导致准备语句出错,因此废除当前操作
// $arrays[] = &$this->paramType($params,$id,$type);
// if($type ==1 && $id){
// $arrays[] = &$id;
// }
// foreach($params as $k=>$v){
// $arrays[] = &$params[$k];
// }
// }
}else{
$arrays[] = &$this->paramType($params,$id,$type);
if($type ==1 && $id){
$arrays[] = &$id;
}
}
if($type ==0 && $id){
$arrays[] = &$id;
}
return $arrays;
}
/**
* 添加单条数据
* @param string $table 表名
* @param array $data 需要改变的数组参数值
* @return 返回执行结果
*/
public function add($table,$data=''){
$temp = $this->common($table,$data);
$left = $temp['left'];
$sqlR = $temp['sqlR'];
$this->sqlx = "INSERT INTO {$this->table} ($left) VALUES ($sqlR)";
$stmt = $this->mysqli->stmt_init;
$stmt = $this->mysqli->prepare($this->sqlx);
$params = $temp['right'];
$this->params = $this->bindParam($params);
$re = call_user_func_array(array($stmt,'bind_param'),$this->bindParam($params));// 魔术方法直接call
if(!$re and $this->debug){
exit("亲，遇到问题咯，当前sql语句为:".$this->_sql());
}
$results = $stmt->execute();
$this->destroy();
if($this->auto_commit != FALSE){
if($stmt->affected_rows){
return $results;
}else{
return null;
}
}
}
/**
* 修改单条数据
* @param string $table 表名
* @param int $id 主键id
* @param array $data 需要改变的数组参数值
* @return 返回执行结果
*/
public function save($table,$id,$data=''){
$temp = $this->common($table,$data);
$sql = $temp['sql'];
$zhujian = $temp['zhujian'];
$params = $temp['right'];
$id = (int)$id;
$this->sqlx = "UPDATE {$this->table} SET $sql WHERE `{$zhujian}` = ?";
$stmt = $this->mysqli->stmt_init;
$stmt = $this->mysqli->prepare($this->sqlx);
$this->params = $this->bindParam($params,"$id");
$re = call_user_func_array(array($stmt,'bind_param'),$this->bindParam($params,"$id"));
if(!$re and $this->debug){
exit("亲，遇到问题咯，当前sql语句为:".$this->_sql());
}
$results = $stmt->execute();
$this->destroy();
if($this->auto_commit != FALSE){
if($results){
return $results;
}else{
return null;
}
}
}
/**
* 基于add、save方法后，添加和修改可统一使用update
* @param string $table 表名
* @param array $data 传值处理数组
* @return 返回执行结果
*/
public function update($table,$data){
$temp = $this->common($table,$data);
$zhujian = $temp['zhujian'];
if(empty($_POST[$zhujian])){//add
return $this->add($table,$data);
}else{
$id = $_POST[$zhujian];
return $this->save($table,$id,$data);
}
}
/**
* 根据主键删除单表的数据
* @param string $table 表名
* @param int $id 主键id
* @return string 返回结果
*/
public function del($table,$id){
$temp = $this->common($table);
$zhujian = $temp['zhujian'];
$this->sqlx = "DELETE FROM {$this->table} WHERE `{$zhujian}` = ?";
$stmt = $this->mysqli->stmt_init;
$stmt = $this->mysqli->prepare($this->sqlx);
$this->params = $this->bindParam($params='',"$id");
$re = call_user_func_array(array($stmt,'bind_param'),$this->bindParam($params='',"$id"));
if(!$re and $this->debug){
exit("亲，遇到问题咯，当前sql语句为:".$this->_sql());
}
$results = $stmt->execute();
$this->destroy();
if($this->auto_commit != FALSE){
if($stmt->affected_rows){
return $results;
}else{
return null;
}
}
}
/**
* 根据所传主键批量删除数据
* @param string $table 表名
* @param string|array $ids 主键组成的字符串(必须以逗号隔开)或者数组
* @return int 返回删除总数
*/
public function dels($table,$ids){
$temp = $this->common($table);
$zhujian = $temp['zhujian'];
$right = '';
if(is_array($ids)){
$params = $ids;
foreach($ids as $k=>$v){
if($k == 0){
$right.='?';
}else{
$right.=',?';
}
}
}else{
$params = explode(',',$ids);
foreach($params as $k=>$v){
if($k == 0){
$right.='?';
}else{
$right.=',?';
}
}
}
$this->sqlx = "DELETE FROM {$this->table} WHERE `{$zhujian}` IN ($right)";
$stmt = $this->mysqli->stmt_init;
$stmt = $this->mysqli->prepare($this->sqlx);
$this->params = $this->bindParam($params);
$re = call_user_func_array(array($stmt,'bind_param'),$this->bindParam($params));
if(!$re and $this->debug){
exit("亲，遇到问题咯，当前sql语句为:".$this->_sql());
}
$results = $stmt->execute();
$this->destroy();
if($this->auto_commit != FALSE){
if($stmt->affected_rows){
return $stmt->affected_rows;
}else{
return null;
}
}
}
/**
* 根据条件删除数据
* @param string $table 表名
* @param array $condition 条件
* @return int 返回删除总数
*/
public function delc($table,$condition){
$this->table = $this->db_press($table);
$conditions = $this->condition($condition);
$right = $conditions['right'];
$params = $conditions['params'];
$this->sqlx = "DELETE FROM {$this->table} WHERE $right";
$stmt = $this->mysqli->stmt_init;
$stmt = $this->mysqli->prepare($this->sqlx);
$this->params = $this->bindParam($params);
$re = call_user_func_array(array($stmt,'bind_param'),$this->bindParam($params));
if(!$re and $this->debug){
exit("亲，遇到问题咯，当前sql语句为:".$this->_sql());
}
$results = $stmt->execute();
$this->destroy();
if($this->auto_commit != FALSE){
if($stmt->affected_rows){
return $stmt->affected_rows;
}else{
return null;
}
}
}
/**
* 获取表的表名或者单表时的主键和字段
* @param string $table 表名集合
* @return 无返回，已将值设置为类变量
*/
public function getTable($table){
if(!strpos($table,',')){//单表查找主键
$this->table = $this->db_press($table);
$query = $this->mysqli->query("DESC {$this->table}");
while($row =$query->fetch_array(MYSQLI_ASSOC)){
$result[] = $row['Field'];
if($row['Key'] == 'PRI'){
$this->pri = $row['Field'];
}
}
}else{
$table = explode(',',$table);
foreach ($table as $key => $value) {
$table[$key] = $this->db_press($value);
}
$this->table = implode(',',$table);
}
}
/**
* 根据主键查询单条记录
* @param string $table 表名
* @param int $id 主键
* @return array/string 成功返回当条记录失败返回null
*/
public function find($table,$id){
$id = (int)$id;
$this->getTable($table);
$this->field = ($this->field)?($this->field) :'*';
$this->sqlx = "SELECT {$this->field} FROM {$this->table} WHERE `{$this->pri}` = ? {$this->where} {$this->order} {$this->limit}";
$stmt = $this->mysqli->stmt_init;
$stmt = $this->mysqli->prepare($this->sqlx);
if(!$this->where)
$this->params = '';
$params = $this->params;
//var_dump($params);
$this->params = $this->bindParam($params,$id,1);
$re = call_user_func_array(array($stmt,'bind_param'),$this->bindParam($params,$id,1));
if(!$re and $this->debug){
exit("亲，遇到问题咯，当前sql语句为:".$this->_sql());
}
$stmt->execute();
$result = $stmt->get_result();
$results =$result->fetch_assoc();
$result->close();//释放结果集
$this->destroy();
if($results){
return $results;
}else{
return null;
}
}
/**
* 查询符合条件的所有数据
* @param string $table 表名
* @return array/string 成功返回满足条件的所有记录失败返回null
*/
public function select($table){
$this->getTable($table);
$this->field = ($this->field)?($this->field) :'*';
$this->sqlx = "SELECT {$this->field} FROM {$this->table} WHERE 1 {$this->where} {$this->order} {$this->limit}";
if(!$this->where)
$this->params = '';
if($this->params){
$stmt = $this->mysqli->stmt_init;
$stmt = $this->mysqli->prepare($this->sqlx);
$params = $this->params;
$this->params = $this->bindParam($params);
$re = call_user_func_array(array($stmt,'bind_param'),$this->bindParam($params));
if(!$re and $this->debug){
exit("亲，遇到问题咯，当前sql语句为:".$this->_sql());
}
$stmt->execute();
$result = $stmt->get_result();
}else{
$result = $this->mysqli->query($this->sqlx);
if(!$result and $this->debug){
exit("亲，遇到问题咯，当前sql语句为:".$this->_sql());
}
}
while($row = $result->fetch_array(MYSQLI_ASSOC)){
$results[] = $row;
}
$result->close();
$this->destroy();
if($results){
return $results;
}else{
return null;
}
}
/**
* 根据主键设置某个字段的值
* @param string $table 表名
* @param int $id 主键
* @param string $field_name 字段名
* @param string $field_value 字段值
*/
public function setField($table,$id,$field_name,$field_value){
$this->getTable($table);
$this->sqlx = "UPDATE {$this->table} SET `{$field_name}` = ? WHERE `{$this->pri}` = ?";
$stmt = $this->mysqli->stmt_init;
$stmt = $this->mysqli->prepare($this->sqlx);
$params[] = $field_value;
$params[] = (int)$id;
$this->params = $this->bindParam($params);
$re = call_user_func_array(array($stmt,'bind_param'),$this->bindParam($params));
if(!$re and $this->debug){
exit("亲，遇到问题咯，当前sql语句为:".$this->_sql());
}
$results = $stmt->execute();
$this->destroy();
if($this->auto_commit != FALSE){
if(!$results){
return null;
}else{
return true;
}
}
}
/**
* 根据查询字段返回需要的字段
* @param 查看_call()方法调用 $args
* @param 查看_call()方法调用 $search
* @param string $field_c 条件字段
* @param string $field_f 需要查找字段
* @return 返回需要查询的字段值
*/
public function getFieldBy($args,$search){
$field_c = $search;
$this->table = $args[0];
$this->table = $this->db_press($this->table);
$where = "`{$field_c}` = ?";//
$field_f = $args[2];
$this->sqlx = "SELECT `{$field_f}` FROM {$this->table} WHERE {$where}";
$stmt = $this->mysqli->stmt_init;
$stmt = $this->mysqli->prepare($this->sqlx);
$this->params = $this->bindParam($params,$args[1]);
$re = call_user_func_array(array($stmt,'bind_param'),$this->bindParam($params,$args[1]));
if(!$re and $this->debug){
exit("亲，遇到问题咯，当前sql语句为:".$this->_sql());
}
$stmt->execute();
$result = $stmt->get_result();
$results = $result->fetch_assoc();
$this->destroy();
if(!$results){
return null;
}else{
return $results[$field_f];
}
}
/**
* 将数据自动加上对应数据
* @param string $table 表名
* @param int $id 主键
* @param string $field 字段名
* @param int $num 增加数量
*/
public function setInc($table,$id,$field,$num){
$this->getTable($table);
$method = getFieldBy.$this->pri;
$fields = $this->$method($table,$id,$field);
$fields+=$num;
$results = $this->setField($table,$id,$field,$fields);
if($this->auto_commit != FALSE){
if($results){
return $results;
}else{
return null;
}
}
}
/**
* 将数据自动减去对应数据最低值为0
* @param string $table 表名
* @param int $id 主键
* @param string $field 字段名
* @param int $num 减去数量
*/
public function setDec($table,$id,$field,$num){
$this->getTable($table);
$method = getFieldBy.$this->pri;
$fields = $this->$method($table,$id,$field);
$fields-=$num;
if($fields < 0){
$fields = 0;
}
$results = $this->setField($table,$id,$field,$fields);
if($this->auto_commit != FALSE){
if($results){
return $results;
}else{
return null;
}
}
}
/**
* 查询满足条件的总数
* @param string table表名
* @param string field字段名
* @return 返回满足条件的数量
*/
public function count($table,$field=''){
if(strpos($table,',')){
$table = $this->db_press($table);
}else{
$table = explode(',',$table);
foreach ($table as $key => $value) {
$value = $this->db_press($value);
$table[$key] = $value;
}
$table = implode(',',$table);
}
$this->field = $field?"count($field) as counts":"count(*) as counts";
if(!$this->where)
$this->params = '';
$this->sqlx = "SELECT {$this->field} FROM {$table} WHERE 1 {$this->where} {$this->order} {$this->limit}";
if($this->params){
$stmt = $this->mysqli->stmt_init;
$stmt = $this->mysqli->prepare($this->sqlx);
$params = $this->params;
$this->params = $this->bindParam($params);
$re = call_user_func_array(array($stmt,'bind_param'),$this->bindParam($params));
if(!$re and $this->debug){
exit("亲，遇到问题咯，当前sql语句为:".$this->_sql());
}
$stmt->execute();
$result = $stmt->get_result();
}else{
$result = $this->mysqli->query($this->sqlx);
if(!$result and $this->debug){
exit("亲，遇到问题咯，当前sql语句为:".$this->_sql());
}
}
$results = $result->fetch_assoc();
$counts = $results["counts"];
$result->close();
$this->destroy();
return $counts?$counts:0;
}
/**
* 查询分页方法
* @param string $table 表名
* @param string $page 当前页码
* @param string $num 默认显示页数10
* @param string $num_page 默认分页数8
* @param string $fields 需要查询的字段
* @param string $order 排序条件
* @param string $first 第一页(根据需要传入)
* @param string $pre 上一页(根据需要传入)
* @param string $next 下一页(根据需要传入)
* @param string $end 尾页(根据需要传入)
* @return array 成功返回数组$list['list']为查询数据$list['page_show']为分页样式
*/
public function search($table,$page = '',$num = '',$num_page = '',$first = '第一页',$pre= '上一页',$next = '下一页',$end = '尾页'){
//每页显示数量
$_pageNum = $num ? $num : 10;
$start = ceil(($page-1)*$_pageNum);
//将值赋给变量否则会被销毁
$where1 = $this->where;
$order1 = $this->order;
$field1 = $this->field;
$params = $this->params;
$results = $this->count($table);
//获取查询的总数
$counts = count($results);
//将变量再赋值给类变量，完成查询
$this->where = $where1;
$this->order = $order1;
$this->field = $field1;
$this->limit = " LIMIT $start,$_pageNum";
$this->params = $params;
$list = $this->select($table);
$this->destroy();
if($counts != 0){
//赋值数据结果集
$list['list'] = $list;
//默认分页显示数8
$num_page = $num_page ? $num_page : 8;
//赋值分页结果集
$list['page_show'] = $this->page($page,$counts,$num,$num_page,$first,$pre,$next,$end);
//赋值查询的总数
$list['counts'] = $counts;
return $list;
}else{
return null;
}
}
/**
* 根据结果集数组进行分页
* @param array $array 结果集数据
* @param int $page 当前页码
* @param string $page 当前页码
* @param string $num 默认显示页数10
* @param string $num_page 默认分页数8
* @param string $first 第一页(根据需要传入)
* @param string $pre 上一页(根据需要传入)
* @param string $next 下一页(根据需要传入)
* @param string $end 尾页(根据需要传入)
* @return array 成功返回数组$list['list']为查询数据$list['page_show']为分页样式 $list['counts']为总数
*/
public function getPage($array,$page,$num='',$num_page='',$first='第一页',$pre='上一页',$next='下一页',$ends='尾页'){
if(!empty($array)){
$counts = count($array);
$_pageNum = $num ? $num : 10;
$start = ceil(($page-1)*$_pageNum);
$end = $start + $_pageNum;
if($counts>0){
foreach ($array as $key=>$v){
if($key>=$start && $key<$end){
$list[] = $v;
}
}
}
$lists['list'] = $list;
$num_page = $num_page ? $num_page : 8;
$lists['page_show'] = $this->page($page,$counts,$num,$num_page,$first,$pre,$next,$ends);
$lists['counts'] = $counts;
return $lists;
}else{
return null;
}
}
/**
* 分页方法
* @param int $page 当前页码
* @param int $counts 总数
* @param integer $num 每页显示数(默认10条)
* @param integer $num_page 默认分页数(默认8条)
* @param string $first 第一页(根据需要传入)
* @param string $pre 上一页(根据需要传入)
* @param string $next 下一页(根据需要传入)
* @param string $end 尾页(根据需要传入)
*/
public function page($page,$counts,$num='',$num_page='',$first = '',$pre='',$next='',$end=''){
//获取当前url使得查询去除分页影响
$url = 'http://'.$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF'].'?'.$_SERVER['QUERY_STRING'];
$url1 = strpos($url,"&page");
if(!empty($url1)){
$url = substr($url,0,$url1);
}else{
$url = $url;
}
//分页显示样式处理
//第一页
$first = $first ? $first : '';
//上一页
$pre = $pre ? $pre : '«';
//下一页
$next = $next ? $next : '»';
//最后一页
$end = $end ? $end : '';
//分页结果处理
//默认显示数量为10
$_pageNum = $num ? $num : 10;
//默认分页显示数8
$num_page = $num_page ? $num_page : 8;
//总页数
$pages = ceil($counts/$_pageNum);
//当前页面小于1 则为1
$page = $page<1?1:$page;
//当前页大于总页数 则为总页数
$page = $page > $pages ? $pages : $page;
//页数小当前页 则为当前页
$pages = $pages < $page ? $page : $pages;
//计算开始页
//计算基数
$_start = 1 + floor(($page-1)/$num_page)*$num_page;
$_start = $_start<1 ? 1 : $_start;
//计算结束页
$_end = $_start + $num_page;
$_end = $_end>$pages? $pages : $_end;
//当前显示的页码个数不够最大页码数，在进行左右调整
$_curPageNum = $_end-$_start+1;
//左调整
if($_curPageNum<$_pageNum && $_start>1){
$_start = $_start - ($_pageNum-$_curPageNum);
$_start = $_start<1 ? 1 : $_start;
$_curPageNum = $_end-$_start+1;
}
//右边调整
if($_curPageNum<$_pageNum && $_end<$pages){
$_end = $_end + ($_pageNum-$_curPageNum);
$_end = $_end>$pages? $pages : $_end;
}
//初始化变量
$_pageHtml = '';
if(!empty($first)){
if($_start == 1){
$_pageHtml .= '
- '.$first.'
- ';
}else{
$_pageHtml .= '
- '.$first.'
- ';
}
}
if($page>1){
$_pageHtml .= '
- '.$pre.'
- ';
}
for ($i = $_start; $i <= $_end; $i++) {
if($i == $page){
//当前页
$_pageHtml .= '
- '.$i.'
- ';
}else{
//跳转指定页
$_pageHtml .= '
- '.$i.'
- ';
}
}
if(!empty($end)){
if($_end == $pages){
$_pageHtml .= '
- '.$end.'
- ';
}else{
$_pageHtml .= '
- '.$end.'
- ';
}
}
if($page<$_end){
$_pageHtml .= '
- '.$next.'
- ';
}
//返回分页数据
if($pages>1){
return $_pageHtml;
}else{
return null;
}
}
}
?>