db查询类

<?php
/**
 * 数据库工具类,采用pdo方式
 * 当前仅支持MySql
 */
class Db{
    public function __construct(){
        // 数据库连接池
        $this->pdo_list = [];
    }
    // 初始化pdo
    private function init($db){
        if(isset($this->pdo_list[$db]) && $this->pdo_list[$db]){
            $this->pdo = $this->pdo_list[$db];
            return;
        }
        // 数据库配置
        $dsn = "mysql:host=127.0.0.1;dbname=imqq";
        $this->pdo = new \PDO($dsn,'root','root');
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        $this->pdo_list[$db] = $this->pdo;
    }
    // 指定表名，支持多数据库
    public function table($table,$db='default'){
        $this->init($db);
        $this->table = $table;
        $this->field = '*';
        $this->order = '';
        $this->limit = 0;
        $this->where = [];
        $this->lastsql = '';
        $this->binds = [];
        return $this;
    }
    // 指定查询字段
    public function field($field='*'){
        $this->field = $field;
        return $this;
    }
    // 指定where条件
    public function where($where){
        $this->where = $where;
        return $this;
    }
    // 限制结果数量
    public function limit($limit){
        $this->limit = $limit;
        return $this;
    }
    // 排序
    public function order($order=''){
        $this->order = $order;
        return $this;
    }
    // 查询一条记录
    public function item(){
        $sql = $this->build_sql('select').' limit 1';
        $stmt = $this->pdo->prepare($sql);
        $this->bindValue($stmt);
        $stmt->execute();
        $item = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        return $item ? $item[0] : false;
    }
    // 查询列表
    public function lists(){
        $sql = $this->build_sql('select');
        $stmt = $this->pdo->prepare($sql);
        $this->bindValue($stmt);
        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
    // 自定义索引列表
    public function cates($index){
        $result = [];
        $lists = $this->lists();
        if(!$lists){
            return $result;
        }
        foreach ($lists as $key => $value) {
            $result[$value[$index]] = $value;
        }
        return $result;
    }
    // 查询总数
    public function count(){
        $sql = $this->build_sql('count');
        $stmt = $this->pdo->prepare($sql);
        $this->bindValue($stmt);
        $stmt->execute();
        $total = $stmt->fetchColumn(0);
        return $total;
    }
    // 分页
    public function pages($page = 1,$pageSize = 10,$path=''){
        $this->limit = ($page-1)*$pageSize.','.$pageSize;
        $total = $this->count();
        $data = $this->lists();
        $pages = $this->_subPages($page,$pageSize,$total,$path);
        $result = array('total'=>$total,'data'=>$data,'page'=>$page,'pages'=>$pages);
        return $result;
    }
    // 添加记录
    public function insert($data){
        $sql = $this->build_sql('insert',$data);
        $this->sqls[] = $sql;
        $stmt = $this->pdo->prepare($sql);
        $this->bindValue($stmt,$data);
        $stmt->execute();
        return $this->pdo->lastInsertId();
    }
    // 修改记录
    public function update($data){
        $sql = $this->build_sql('update',$data);
        $stmt = $this->pdo->prepare($sql);
        $this->bindValue($stmt,$data);
        return $stmt->execute();
    }
    // 删除记录
    public function delete(){
        $sql = $this->build_sql('delete');
        $stmt = $this->pdo->prepare($sql);
        $this->bindValue($stmt);
        return $stmt->execute();
    }
    // 构造sql
    private function build_sql($type,$data = null){
        $sql = '';
        // query
        if($type == 'select'){
            $where = $this->_build_where();
            $sql = "SELECT {$this->field} FROM {$this->table}" . ($where ? " WHERE {$where}" : '');
            $this->order && $sql .= " order by {$this->order}";
            $this->limit && $sql .= " limit {$this->limit}";
        }
        // count
        if($type == 'count'){
            $where = $this->_build_where();
            $field = count(explode(',',$this->field))>1?'*':$this->field;
            $sql = "SELECT count({$field}) FROM {$this->table}" . ($where ? " WHERE {$where}" : '');
        }
        // insert
        if($type == 'insert'){
            $sql = $this->_build_insert($data);
        }
        // update
        if($type == 'update'){
            $sql = $this->_build_update($data);
        }
        // delete
        if($type == 'delete'){
            $sql = $this->_build_delete();
        }
        $this->lastsql = $sql;
        return $sql;
    }
    // 构造查询条件where
    private function _build_where(){
        $where = '';
        if(is_array($this->where)){
            foreach ($this->where as $key => $item) {
                $where .= " and `{$key}`=:{$key}";
            }
        }else{
            $where = $this->where;
        }
        $where = ltrim($where,' and');
        return $where;
    }
    // 构造添加数据sql
    private function _build_insert($data){
        if(!$data){
            return false;
        }
        $sql = "insert into {$this->table}";
        $fields = $values = [];
        foreach ($data as $key => $val) {
            $fields[] = '`'.$key.'`';
            $values[] = ":$key";
        }
        $sql .= ('('.implode(',',$fields).')values('.implode(',',$values).')');
        return $sql;
    }
    // 构造更新数据sql
    private function _build_update($data){
        $where = $this->_build_where();
        $str_update = '';
        foreach ($data as $key => $val) {
            $str_update .= ('`'.$key.'`=:'.$key.',');
        }
        $sql = "UPDATE {$this->table} SET {$str_update}";
        $sql = rtrim($sql,',');
        $sql .= ' WHERE '.$where;
        return $sql;
    }
    // 构造删除数据sql
    private function _build_delete(){
        $where = $this->_build_where();
        $sql = "DELETE FROM {$this->table}" . ($where ? " WHERE {$where}" : '');
        return $sql;
    }
    // 参数绑定
    private function bindValue($stmt,$data = null){
        if($this->where && is_array($this->where)){
            foreach($this->where as $key => $item){
                $stmt->bindValue(':'.$key,$item);
                $this->binds[$key] = $item;
            }
        }
        if($data){
            foreach ($data as $k => $value) {
                $stmt->bindValue(':'.$k,$value);
                $this->binds[$k] = $value;
            }
        }
    }
    // 获取最后执行的sql
    public function getlastsql(){
        if(!$this->lastsql){
            return '';
        }
        foreach ($this->binds as $key => $value) {
            $value = is_string($value) ? "'".$value."'" : $value;
            $this->lastsql = str_replace(':'.$key, $value, $this->lastsql);
        }
        echo $this->lastsql.'<br>';
    }
    // 构造分页(bootstrap风格)
    // cur_page：当前第几页
    private function _subPages($cur_page,$pageSize,$total=0,$path=''){
        $html = '';
        // 分页数
        $page_count = ceil($total / $pageSize);
        if($page_count == 1){
            return $html;
        }
        // path
        $symbol = '?';
        $is_and = strpos($path,'?');
        if($is_and !== false && $is_and >= 0){
            $symbol = '&';
        }
        // 添加“首页”
        if($cur_page>1){
            $pre_page = $cur_page-1;
            $html = "<li><a href='{$path}{$symbol}page=1'><span>首页</span></a></li>";
            $html .= "<li><a href='{$path}{$symbol}page={$pre_page}'><span>下一页</span></a></li>";
        }
        // 每次最多显示几页
        $max_page_limit = 6;
        // 当前页向前显示几页
        $cur_page_pre = (int)$max_page_limit/2;
        // 第一页
        $start = $cur_page > ($page_count - $max_page_limit) ? ($page_count - $max_page_limit) : $cur_page;
        // 最后一页
        $end = ($cur_page + $max_page_limit) >$page_count ? $page_count : ($cur_page + $max_page_limit);
        if($start - $cur_page_pre > 0){
            $start = $start - $cur_page_pre;
            $end = $end -$cur_page_pre;
        }
        if($cur_page+$cur_page_pre>=$end && $page_count>$max_page_limit){
            $start = $start + $cur_page_pre;
            $end = $end +$cur_page_pre;
        }
        $start = $start <= 0 ? 1 : $start;
        for($i=$start;$i<$end;$i++){
            $html .= $cur_page == $i ? "<li class='active'><a>{$i}</a></li>":"<li><a href='{$path}{$symbol}page={$i}'>{$i}</a></li>";
        }
        // 添加尾页
        if($cur_page<$page_count){
            $after_page = $cur_page+1;
            $html .= "<li><a href='{$path}{$symbol}page={$after_page}'>下一页</a></li>";
            $html = $html . "<li><a href='{$path}{$symbol}page={$page_count}'>尾页</a></li>";
        }
        $html =  '<ul class="pagination">'.$html.'</ul>';
        return $html;
    }
}