pdo封装数据操作

hyx 2018-08-21 11:23:09 146次 0
<?php 
header("content-type:text/html;charset=utf-8");
/**
 * autor:sujianbin
 * 2016-09-15
 * 采用pdo对象方式连接
 * MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理!其他的类型是不支持的!(alter table tablename ENGINE = InnoDB)
 * 支持预处理技术,主要在连接和编译过程精简,还可以SQL防止注入,快速执行
 */
 
 
class pdoModel{
    private $linkId = '';//数据库连接标识
    private $PDOStatement = null;//PDOStatement对象  
    private static $charset = 'utf8';//设置字符集
    private static $host = 'localhost';//主机名或ip地址
    private static $DBPort = '3306';//端口号
    private static $DBUser = 'root';//用户名
    private static $DBPass = 'root';//密码
    private static $DBName = 'wsttc';//数据库名称
    private static $debug = 1;//是否开启调试模式(项目上线后请关闭)
    private static $db_log = 0;//是否开启日志
    private $querySql ='';//预处理sql语句
    private $params = array();//预处理数组
    private $autoCommit = TRUE;//是否开启自动提交,不适用于查询操作,默认自动提交,如若关闭操作时请先调用方法关闭
    protected $transactionCount = 0;//事务标识数
    private $db_press = 'su_';//数据库表前缀
 
 
    /**
     * 构造函数
     */
    public function __construct() {
        if (!class_exists('PDO')){
            throw new Exception('not found PDO');
            return false; 
        }
        try{
            $this->linkId = new PDO('mysql:dbname='.self::$DBName.';host='.self::$host.';port='.self::$DBPort,self::$DBUser,self::$DBPass,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES '".self::$charset."';"));
            session_start();
            date_default_timezone_set("PRC");
            if(self::$debug){
                error_reporting(E_ALL ^ E_NOTICE); 
            }else{
                error_reporting(NULL);
                ini_set('display_errors','Off');
            }
        }catch (PDOException $e){
            exit($e->getMessage());
        }
    }
 
 
    /**
     * __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;
    }
 
 
    /**
     * 释放查询结果
     */
    private function free() {
        $this->PDOStatement = null;
        $this->params = array();
    }
 
 
    /**
     * 销毁变量
     * @return 无
     */
    protected function destroy(){
        $this->db_log($this->_sql());
        $array = array('field'=>'','join'=>'','where'=>'','group'=>'','having'=>'','order'=>'','limit'=>'','pk'=>'');
        foreach($array as $key=>$value){
            unset($this->$key);
        }
    }
 
 
    /**
     * 获取毫秒技术
     * @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('`(?<!\\\\)u`', $milliseconds, $format), $timestamp);
    }
 
 
    /**
     * 写入日志
     * @param  string $sql 执行的sql语句
     * @return 无
     */
    private function db_log($sql){
        if($this->db_log){
            $file = 'cache/log.txt';
            if(!file_exists($file)){
                mkdir ("cache");
                $myfile = fopen($file, "w");
            }
            $filesize = filesize($file)/1024/1024;//1M
            if($filesize > 1){
                file_put_contents($file,"");//防止日志太大
            }
            if(preg_match('/INSERT|UPDATE|insert|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;
    }
 
 
    /**
      *开启事务
      */
    public function beginTransaction(){
        if (!$this->transactionCounter++) {
            return $this->linkId->beginTransaction();
        }
        $this->exec('SAVEPOINT trans'.$this->transactionCounter);
        return $this->transactionCounter >= 0;
    }
 
 
    /**
      *提交事务
      */
    public function commit(){
        if (!--$this->transactionCounter) {
            return $this->linkId->commit();
        }
        return $this->transactionCounter >= 0;
    }
 
 
    /**
      *事务回滚
      */
    public function rollback(){
        if (--$this->transactionCounter) {
            $this->exec('ROLLBACK TO trans'.$this->transactionCounter + 1);
            return true;
        }
        return $this->linkId->rollback();
    }
 
 
    /**
     * 析构函数
     * 释放结果集和销毁变量
     */
    public function __destruct(){
        $this->destroy();
        $this->free();
    }
 
 
    /**
     * 函数执行错误处理
     * @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;
        }
    }
 
 
    /**
      *输出最后执行的sql语句
      */
    public function getsql(){
        if(is_array($this->params) && strpos($this->querySql,'?')){
            $sql = '';
            $array = explode('?',$this->querySql);
            foreach ($array as $key => $value) {
                if(!empty($this->params[$key]) || $this->params[$key] == '0'){
                    if(strpos($this->params[$key],"{$this->db_press}")==0 && strpos($this->params[$key],'.')){
                        $sql .= $array[$key].$this->params[$key];
                    }else{
                        $sql .= $array[$key]."'".$this->params[$key]."'";
                    }
                }else{
                    $sql .=$array[$key];
                }
            }
            return $sql;
        }
        return $this->querySql;
    }
 
 
    /**
     * getsql函数的别名
     * @return string 返回sql语句
     */
    public function _sql(){
        return $this->getsql();
    }
 
 
    /**
     * 给表加上前缀
     * @param  string $table 表名
     * @return string 返回完整等我表名
     */
    protected function db_press($table){
        return "`".$this->db_press.$table."`";
    }
 
 
    /**
      *公共函数获取表信息
      *@param string $table 表名
      *@param array $array 对提交的所有数据进行过滤
      */
    private function common($table,$array=''){
        $this->params = '';
        $this->table = $this->db_press($table);
        //释放前次的查询结果
        if ( !empty($this->PDOStatement) ) $this->free();
        $this->PDOStatement = $this->linkId->query("DESC {$this->table}");
        if(self::$debug && !$this->PDOStatement){
            echo "\nPDO::errorInfo():\n";
            print_r($this->linkId->errorInfo());die;
        }
        $results = $this->PDOStatement->fetchAll(PDO::FETCH_ASSOC);
        foreach($results as $k=>$v){
            $result[] = $v['Field'];
            if($v['Key'] == 'PRI'){
                $zhujian = $v['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[] = $value;
                  $arr=$arr."`".$key."`=?".',';
                  $sqlR .= '?,';
                }
            }
        }
        $sqlR = substr($sqlR,0,-1);
        $left = substr($left,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;
    }
 
 
    /**
     * 连接方式,联合查询
     * @return 返回当前对象
     */
    public function join($join){
        if(!empty($join)){
            $this->join = $join;
        }
        return $this;
    }
 
 
    /**
      * 排序条件
      * @param  string $having 字段
      * @return $this 返回当前对象
      */
    public function having($having){
        $this->having = $having?'HAVING '.$having :'';
        return $this;
    }
 
 
    /**
      * 排序条件
      * @param  string $group groupby字段
      * @return $this 返回当前对象
      */
    public function group($group){
        $this->group = $group?'GROUP BY '.$group :'';
        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  string $value 条件字段
     * @return string       返回条件字段
     */
    public function parseK($value){
        if(strpos($value,'.')){
            return $value;
        }else{
            return "`".$value."`";
        }
    }
    /**
     * 解析条件数组
     * @param  array $condition 条件数组
     * @return array 返回数组,包含右侧prepare语句和param数组
     */
    public function condition($condition){
        $right = '';
        $params = '';
        if(is_array($condition)){
            foreach($condition as $k=>$v){
                if(is_array($v)){
                    if(empty($v['description'])){
                        $right.="and ".$this->parseK($k)." ".$v['terms']." ? ";
                        $params[] = $v['value'];
                    }else{
                        if(is_array($v['value'])){
                            $right.='and (';
                            foreach($v['value'] as $k1=>$v1){
                                $params[] = $v1;
                                if(is_array($v['terms'])){
                                    if(count($v['value']) != $k1+1){
                                        $right.= " ".$this->parseK($k)." ".$v['terms'][$k1].' ? '.$v['description']." ";   
                                    }else{
                                        $right.= " ".$this->parseK($k)." ".$v['terms'][$k1].' ?';  
                                    } 
                                }else{
                                    if(count($v['value']) != $k1+1){
                                        $right.= " ".$this->parseK($k)." ".$v['terms'].' ? '.$v['description']." ";   
                                    }else{
                                        $right.= " ".$this->parseK($k)." ".$v['terms'].' ?';  
                                    } 
                                }
                            }
                            $right.=') ';
                        }else{
                            $right.= $v['description']." ".$this->parseK($k)." ".$v['terms']." ? ";
                            $params[] = $v['value'];
                        }
                    }
                }else{
                    $right.="and ".$this->parseK($k).' =? ';
                    $params[] = $v;
                }
            }
            $right = preg_replace('/and /', '', $right, 1); //只替换一次(去掉第一次出现的字符and ) 
            $condition['right'] = $right;
            $condition['params'] = $params; 
        }else{
            $condition = array('right'=>$condition,$params=>'');
        }
        return $condition;
    }
 
 
 
 
    /**
      * 值类型
      * @param string 预处理值
      * @param int 返回值类型
      */
    protected function pdoParamType($value){
      if(is_int($value))
          $type = PDO::PARAM_INT;
      elseif(is_bool($value))
          $type = PDO::PARAM_BOOL;
      elseif(is_null($value))
          $type = PDO::PARAM_NULL;
      elseif(is_string($value))
          $type = PDO::PARAM_STR;
      else
          $type = FALSE;
      return $type;
    }
 
 
    
    /**
      * 把一个值绑定到一个参数
      * @param array $params 预处理值数组
      */
    protected function bindPdoParam($params){
        if(is_array($params)){
            foreach($params as $key=>$val){
              $val  = array($key+1,$val,$this->pdoParamType($val));
              call_user_func_array(array($this->PDOStatement,'bindValue'),$val);
          }
        }
    }
 
 
    /**
      * 添加单条数据
      * @param  string $table  表名
      * @param  array  $data   需要改变的数组参数值
      * @return 返回执行结果
      */
    public function add($table,$data=''){
        $temp = $this->common($table,$data);
        $left = $temp['left'];
        $sqlR = $temp['sqlR'];
        $this->querySql = "INSERT INTO {$this->table} ($left) VALUES ($sqlR)";
        //释放前次的查询结果
        if ( !empty($this->PDOStatement) ) $this->free();
        $this->PDOStatement = $this->linkId->prepare($this->querySql);
        $this->params = $temp['right'];
        $this->bindPdoParam($this->params); 
        $this->PDOStatement->execute();
        if(self::$debug){
            $errorInfo = $this->PDOStatement->errorInfo();
            if($errorInfo[0] != 0){
                echo '<br/>bindSql:'.$this->querySql.'<br/>';
                echo "\nPDOStatement::errorInfo():\n";
                print_r($this->PDOStatement->errorInfo());die;
            }
        }
        if($this->autoCommit != FALSE){
            if($this->PDOStatement->rowCount()){
                return $this->PDOStatement->rowCount();
            }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'];
        $this->querySql = "UPDATE {$this->table} SET $sql WHERE `{$zhujian}` = ?";
        //释放前次的查询结果
        if ( !empty($this->PDOStatement) ) $this->free();
        $this->PDOStatement = $this->linkId->prepare($this->querySql);
        $this->params = $temp['right'];
        $this->params[] = (int)$id;
        $this->bindPdoParam($this->params);
        $result = $this->PDOStatement->execute();
        if(self::$debug){
            $errorInfo = $this->PDOStatement->errorInfo();
            if($errorInfo[0] != 0){
                echo '<br/>bindSql:'.$this->querySql.'<br/>';
                echo "\nPDOStatement::errorInfo():\n";
                print_r($this->PDOStatement->errorInfo());die;
            }
        }
        if($this->autoCommit != FALSE){
            if($result){
                return $result;
            }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->querySql = "DELETE FROM {$this->table}  WHERE   `{$zhujian}` = ?";
        //释放前次的查询结果
        if ( !empty($this->PDOStatement) ) $this->free();
        $this->PDOStatement = $this->linkId->prepare($this->querySql);
        $this->params[] = (int)$id;
        $this->bindPdoParam($this->params);
        $this->PDOStatement->execute();
        if(self::$debug){
            $errorInfo = $this->PDOStatement->errorInfo();
            if($errorInfo[0] != 0){
                echo '<br/>bindSql:'.$this->querySql.'<br/>';
                echo "\nPDOStatement::errorInfo():\n";
                print_r($this->PDOStatement->errorInfo());die;
            }
        }
        if($this->autoCommit != FALSE){
            if($this->PDOStatement->rowCount()){
                return $this->PDOStatement->rowCount();
            }else{
                return null; 
            }
        }
    }
 
 
    /**
     * 根据所传主键批量删除数据
     * @param  string $table 表名
     * @param  string|array  $ids   主键组成的字符串(必须以逗号隔开)或者数组
     * @return int 返回删除总数   
     */
    public function dels($table,$ids){
        $temp = $this->common($table);
        $zhujian = $temp['zhujian'];
        //释放前次的查询结果
        if ( !empty($this->PDOStatement) ) $this->free();
        $right = '';
        if(is_array($ids)){
            $this->params = $ids;
            foreach($ids as $k=>$v){
                if($k == 0){
                    $right.='?';
                }else{
                    $right.=',?';
                }
            }   
        }else{
            $this->params = explode(',',$ids);
            foreach($this->params as $k=>$v){
                if($k == 0){
                    $right.='?';
                }else{
                    $right.=',?';
                }    
            }
        }
        $this->querySql = "DELETE FROM {$this->table}  WHERE `{$zhujian}` IN ($right)";
        $this->PDOStatement = $this->linkId->prepare($this->querySql);
        $this->bindPdoParam($this->params);
        $this->PDOStatement->execute();
        if(self::$debug){
            $errorInfo = $this->PDOStatement->errorInfo();
            if($errorInfo[0] != 0){
                echo '<br/>bindSql:'.$this->querySql.'<br/>';
                echo "\nPDOStatement::errorInfo():\n";
                print_r($this->PDOStatement->errorInfo());die;
            }
        }
        if($this->autoCommit != FALSE){
            if($this->PDOStatement->rowCount()){
                return $this->PDOStatement->rowCount();
            }else{
                return null; 
            }
        }
    }
 
 
    /**
     * 根据条件删除数据
     * @param  string $table     表名
     * @param  array  $condition 条件
     * @return int 返回删除总数
     */
    public function delc($table,$condition){
        $this->table = $this->db_press($table);
        //释放前次的查询结果
        if ( !empty($this->PDOStatement) ) $this->free();
        $conditions = $this->condition($condition);
        $right = $conditions['right'];
        $this->params = $conditions['params'];
        $this->querySql = "DELETE FROM {$this->table} WHERE $right";
        $this->PDOStatement = $this->linkId->prepare($this->querySql);
        $this->bindPdoParam($this->params);
        $this->PDOStatement->execute();
        if(self::$debug){
            $errorInfo = $this->PDOStatement->errorInfo();
            if($errorInfo[0] != 0){
                echo '<br/>bindSql:'.$this->querySql.'<br/>';
                echo "\nPDOStatement::errorInfo():\n";
                print_r($this->PDOStatement->errorInfo());die;
            }
        }
        if($this->autoCommit != FALSE){
            if($this->PDOStatement->rowCount()){
                return $this->PDOStatement->rowCount();
            }else{
                return null; 
            }
        }
    }
 
 
    /**
      * 获取表的表名或者单表时的主键和字段
      * @param  string $table 表名集合
      * @return 无返回,已将值设置为类变量
      */
    public function getTable($table){
        if(!strpos($table,',')){//单表查找主键
            $tables = $this->db_press($table);
            $this->PDOStatement = $this->linkId->query("DESC {$tables}");
            if(self::$debug && !$this->PDOStatement){
                $errorInfo = $this->PDOStatement->errorInfo();
                if($errorInfo[0] != 0){
                    echo '<br/>bindSql:'.$this->querySql.'<br/>';
                    echo "\nPDOStatement::errorInfo():\n";
                    print_r($this->PDOStatement->errorInfo());die;
                }
            }
            $result = $this->PDOStatement->fetch(PDO::FETCH_ASSOC);
            $this->pk = $result['Field'];
        }else{
            $tables = explode(',',$table);
            foreach ($tables as $key => $value) {
                $tables[$key] = $this->db_press($value); 
            }
            $tables = implode(',',$tables);
        }
        return $tables;
    }
 
 
    public function querySql($sql){
        $this->querySql = $sql;
        $this->PDOStatement = $this->linkId->prepare($this->querySql); 
        $this->PDOStatement->execute();
        if(self::$debug){
            $errorInfo = $this->PDOStatement->errorInfo();
            if($errorInfo[0] != 0){
                echo '<br/>bindSql:'.$this->querySql.'<br/>';
                echo "\nPDOStatement::errorInfo():\n";
                print_r($this->PDOStatement->errorInfo());die;
            }
        }
        return $this->PDOStatement;
    }
 
 
    /**
     * 根据主键查询单条记录
     * @param  string $table 表名
     * @param  int $id    主键
     * @return array/string   成功返回当条记录失败返回null
     */
    public function find($table,$id){
        $this->field = ($this->field)?($this->field) :'*';
        if(!$this->where)
            $this->params = '';
        $this->querySql = "SELECT {$this->field} FROM {$this->getTable($table)} WHERE `{$this->pk}` = ? {$this->where} {$this->group} {$this->having} {$this->order} {$this->limit}";
        //销毁变量
        $this->destroy();
        $this->PDOStatement = $this->linkId->prepare($this->querySql);
        $this->params[] = (int)$id;
        $this->bindPdoParam($this->params);
        $this->PDOStatement->execute();
        if(self::$debug){
            $errorInfo = $this->PDOStatement->errorInfo();
            if($errorInfo[0] != 0){
                echo '<br/>bindSql:'.$this->querySql.'<br/>';
                echo "\nPDOStatement::errorInfo():\n";
                print_r($this->PDOStatement->errorInfo());die;
            }
        }
        $results = $this->PDOStatement->fetch(PDO::FETCH_ASSOC);
        if($this->autoCommit != FALSE){
            if($results){
              return $results;
          }else{
              return null; 
          }
        }
    }
 
 
    /**
      * 查询符合条件的所有数据
      * @param  string $table 表名
      * @return array/string   成功返回满足条件的所有记录失败返回null
      */
    public function select($table){
        $this->field = ($this->field)?($this->field) :'*';
        if(!$this->where)
            $this->params = '';
        $this->querySql = "SELECT {$this->field} FROM {$this->getTable($table)} {$this->join} WHERE 1 {$this->where} {$this->group} {$this->having} {$this->order} {$this->limit}";
        //销毁变量
        $this->destroy();
        $this->PDOStatement = $this->linkId->prepare($this->querySql);
        $this->bindPdoParam($this->params);
        $this->PDOStatement->execute();
        if(self::$debug){
            $errorInfo = $this->PDOStatement->errorInfo();
            if($errorInfo[0] != 0){
                echo '<br/>bindSql:'.$this->querySql;
                echo "\nPDOStatement::errorInfo():\n";
                print_r($this->PDOStatement->errorInfo());
                die;
            }
        }
        $results = $this->PDOStatement->fetchAll(PDO::FETCH_ASSOC);
        if($results){
            return $results;
        }else{
            return null; 
        }
    }
 
 
    /**
      * 根据主键设置某个字段的值
      * @param string $table  表名
      * @param int $id     主键
      * @param string $field_name  字段名
      * @param string $field_value 字段值
      * @return 返回设置的值
      */
    public function setField($table,$id,$field_name,$field_value){
        $this->querySql = "UPDATE {$this->getTable($table)} SET `{$field_name}` = ? WHERE `{$this->pk}` = ?";
        //释放前次的查询结果
        if ( !empty($this->PDOStatement) ) $this->free();
        $this->pk = '';
        $this->PDOStatement = $this->linkId->prepare($this->querySql);
        $this->params = array($field_value,(int)$id);
        $this->bindPdoParam($this->params);
        $results = $this->PDOStatement->execute();
        if(self::$debug){
            $errorInfo = $this->PDOStatement->errorInfo();
            if($errorInfo[0] != 0){
                echo '<br/>bindSql:'.$this->querySql.'<br/>';
                echo "\nPDOStatement::errorInfo():\n";
                print_r($this->PDOStatement->errorInfo());die;
            }
        }
        if($this->autoCommit != FALSE){
            if($results){
                return $field_value;
            }else{
                return null;
            }
        }
    }
 
 
    /**
      * 根据查询字段返回需要的字段
      * @param  查看_call()方法调用  $args   
      * @param  查看_call()方法调用  $search
      * @param  string $field_c 条件字段
      * @param  string $field_f 需要查找字段
      * @return 返回需要查询的字段值
      */
    public function getFieldBy($args,$search){
        $field_c = $search;
        $table = $args[0];
        $table = $this->db_press($table);
        $where = "`{$field_c}` = ?";//
        $field_f = $args[2];
        $this->querySql = "SELECT `{$field_f}` FROM {$table} WHERE {$where}";
        //释放前次的查询结果
        if ( !empty($this->PDOStatement) ) $this->free();
        $this->PDOStatement = $this->linkId->prepare($this->querySql);
        $this->params[] = $args[1];
        $this->bindPdoParam($this->params);
        $this->PDOStatement->execute();
        if(self::$debug){
            $errorInfo = $this->PDOStatement->errorInfo();
            if($errorInfo[0] != 0){
                echo '<br/>bindSql:'.$this->querySql.'<br/>';
                echo "\nPDOStatement::errorInfo():\n";
                print_r($this->PDOStatement->errorInfo());die;
            }
        }
        $results = $this->PDOStatement->fetch(PDO::FETCH_ASSOC);
        if($results){
            return $results[$field_f];
        }else{
            return null; 
        } 
    }
 
 
    /**
      * 将数据自动加上对应数据
      * @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->pk;
        $this->pk = '';
        $fields = $this->$method($table,$id,$field);
        $fields+=$num;
        $results = $this->setField($table,$id,$field,$fields);
        if($this->autoCommit != 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->pk; 
        $this->pk = '';
        $fields = $this->$method($table,$id,$field);
        $fields -= $num;
        if($fields < 0){
            $fields = 0;
        }
        $results = $this->setField($table,$id,$field,$fields);
        if($this->autoCommit != 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->querySql = "SELECT {$this->field} FROM {$table} {$this->join} WHERE 1 {$this->where} {$this->group} {$this->having} {$this->order} {$this->limit}";
        //销毁变量
        $this->destroy();
        $this->PDOStatement = $this->linkId->prepare($this->querySql);
        $this->bindPdoParam($this->params);
        $this->PDOStatement->execute();
        if(self::$debug){
            $errorInfo = $this->PDOStatement->errorInfo();
            if($errorInfo[0] != 0){
                echo '<br/>bindSql:'.$this->querySql.'<br/>';
                echo "\nPDOStatement::errorInfo():\n";
                print_r($this->PDOStatement->errorInfo());die;
            }
        }
        $results = $this->PDOStatement->fetch(PDO::FETCH_ASSOC);
        $counts = $results["counts"];
        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);
        if(!$this->where)
          $this->params = '';
        //将值赋给变量否则会被销毁
        $where1 = $this->where;
        $order1 = $this->order;
        $field1 = $this->field;
        $join1 = $this->join;
        $params = $this->params;
        //获取查询的总数
        $counts = $this->count($table);
        //echo $this->_sql();
        //$counts = count($results);
        //将变量再赋值给类变量,完成查询
        $this->where = $where1;
        $this->order = $order1;
        $this->field = $field1;
        $this->join = $join1;
        $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 .= '<li><a  title="第一页" href="'.$url.'&page=1">'.$first.'</a></li>';
            }else{
                $_pageHtml .= '<li><a  title="第一页" href="'.$url.'&page=1">'.$first.'</a></li>';
            }
        }
        if($page>1){
            $_pageHtml .= '<li><a  title="上一页" href="'.$url.'&page='.($page-1).'">'.$pre.'</a></li>';
        }
        for ($i = $_start; $i <= $_end; $i++) {
            if($i == $page){
                //当前页
                $_pageHtml .= '<li><a class="current">'.$i.'</a></li>';
            }else{
                //跳转指定页
                $_pageHtml .= '<li><a href="'.$url.'&page='.$i.'">'.$i.'</a></li>';
            }
        }
        if(!empty($end)){
          if($_end == $pages){
              $_pageHtml .= '<li><a  title="最后一页" href="'.$url.'&page='.$pages.'">'.$end.'</a></li>';
          }else{
              $_pageHtml .= '<li><a  title="最后一页" href="'.$url.'&page='.$pages.'">'.$end.'</a></li>';
          }
        }
        if($page<$_end){
            $_pageHtml .= '<li><a  title="下一页" href="'.$url.'&page='.($page+1).'">'.$next.'</a></li>';
        }
        //返回分页数据
        if($pages>1){
            return $_pageHtml;
        }else{
            return null;
        }
    }
 
 
}
?>

发表评论

注:*为必填

回复 的评论
*
选择
*
*