db类

<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2019-01-15
 * Time: 17:19
 */

class Db
{
    private $where = array();
    private $field = '*';
    private $pdo = null;
    private $order = '';
    private $limit = '';
    #构造方法初始化加载
    public function __construct()
    {
        $this->pdo = new PDO('mysql:host=localhost;dbname=myblog','myblog','myblog');
    }

    #指定数据表
    public function table($table)
    {
        #将表名设置成类属性
        $this->table = $table;
        return $this;
    }
    #===========================================================查询数据==================================================
    #指定字段查询
    public function field($field)
    {
        $this->field = $field;
        return $this;
    }
    #指定排序查询
    public function order($order)
    {
        $this->order = $order;
        return $this;
    }
    #查询指定条数
    public function limit($limit)
    {
        $this->limit = $limit;
        return $this;
    }
    #指定条件
    public function where($where)
    {
        $this->where = $where;
        return $this;
    }
    #查询一条数据
    public function item()
    {
        $sql = $this->_build_sql('select').' limit 1';
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return isset($res[0]) ? $res[0] : array();
    }
    #查询多条记录
    public function lists()
    {
        $sql = $this->_build_sql('select');
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    #数据count
    public function count()
    {
        $sql = $this->_build_sql('count');
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchColumn(0);
    }
    #分页查询 $cur_page:当前页 $count ：总数 $pageSize：每页大小
    public function pages($page,$pageSize=10,$path='/')
    {
        #($page-1)*$pageSize  //计算公式
        #select * from limit 0,3
        #select * from limit 3,3
        #select * from limit 6,3
        #...........
        #select * from limit ($page-1)*$pageSize $pageSize
        $count = $this->count();
        $page = $page <= 0 ? 1 : $page;
        $cur_page = ($page-1)*$pageSize;
        $this->limit = "{$cur_page},{$pageSize}";
        $data = $this->lists();
        $pages = $this->_subPages($page,$count,$pageSize,$path);
        return ['count'=>$count,'data'=>$data,'pages'=>$pages];
    }
    #分页html    $cur_page:当前页 $count ：总数 $pageSize：每页大小
    private function _subPages($cur_page,$count,$pageSize,$path)
    {
        if(strstr($path,'?')){
            $symbol = '&';
        }else{
            $symbol = '?';
        }
        $page_count = ceil($count/$pageSize);
        $html = '';
        #生成首页 #生成上一页
        if($cur_page>1){
            $html .= "<li><a href='{$path}{$symbol}page=1'>首页</a></li>";
            $pre_page = $cur_page - 1;
            $html .= "<li><a href='{$path}{$symbol}page={$pre_page}'>上一页</a></li>";
        }
        #生成数字页
        $start = $cur_page;
        $start = $start - 5;
        $start = $start <=0 ? 1 : $start;
        $end = ($cur_page + 10) > $page_count ? $page_count : ($cur_page + 10);
        $end = $end  > ($cur_page + 4) ? ($cur_page + 4) : $end;
        if(($cur_page+4)<=10 && ($cur_page+4)<$page_count){
            $end = 10;
        }
        for($i=$start;$i<=$end;$i++){
            $html .= $cur_page==$i ? "<li class='active'><a href='javascript:;' style='cursor: pointer'>{$i}</a></li>" : "<li><a href='{$path}{$symbol}page={$i}'>{$i}</a></li>";
        }
        #生成下一页 #生成末页
        if($cur_page<$page_count) {
            $next_page = $cur_page + 1;
            $html .= "<li><a href='{$path}{$symbol}page={$next_page}'>下一页</a></li>";
            #生成末页
            $html .= "<li><a href='{$path}{$symbol}page={$page_count}'>末页</a></li>";
        }
        $html = '<nav aria-label="..." style="float: right"><ul class="pagination" style="margin-top: 0">'.$html.'</ul></nav>';

        if($count<=$pageSize){
            $html = '';
        }

        return $html;
    }
    #===============================================插入数据=============================================================
    public function insert($data)
    {
        $sql = $this->_build_sql('insert',$data);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $this->pdo->lastInsertId();
    }
    #=============================================更新数据===============================================================
    public function update($data)
    {
        $sql = $this->_build_sql('update',$data);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->rowCount();
    }
    #=============================================删除数据===============================================================
    public function delete()
    {
        $sql = $this->_build_sql('delete');
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->rowCount();
        #DELETE FROM Person WHERE LastName = 'Wilson'
    }

    #构造查询sql语句
    private function _build_sql($type,$data=null)
    {
        if($type == 'select'){
            $where = $this->_build_where();
            $where = $where == ''?'' : "where {$where}";
            $sql = "select {$this->field} from {$this->table} {$where}";
            if($this->order){
                $sql.=" order by {$this->order}";//var_dump($sql);exit();
            }
            if($this->limit){
                $sql.=" limit {$this->limit}";
            }
        }
        if($type == 'count'){
            $where = $this->_build_where();
            $where = $where == ''?'' : "where {$where}";
            $field = $this->field;
            $array = explode(",",$field);
            if(count($array) == 1){
                $field = $this->field;
            }else{
                $field = '*';
            }
            $sql = "select count({$field}) from {$this->table} {$where}";
        }
        if($type == 'insert'){
            $ziduan = '';
            $values = '';
            foreach ($data as $key=>$value){
                $value = is_string($value) ? "'".$value."'" : $value;
                $values .= "{$value},";
            }
            $ziduan = implode(',',array_keys($data));
            $values = rtrim($values,',');
            $sql = "insert into {$this->table}({$ziduan}) values ({$values})";
        }

        if($type == 'update'){
            $datas = '';
            $where = $this->_build_where();
            foreach ($data as $key=>$value){
                $value = is_string($value) ? "'".$value."'" : $value;
                $datas.="{$key}={$value},";
            }
            $datas = rtrim($datas,',');
            $sql = "UPDATE {$this->table} SET {$datas} WHERE {$where}";
        }

        if($type == 'delete'){
            $where = $this->_build_where();
            #DELETE FROM Person WHERE LastName = 'Wilson'
            $sql = "DELETE FROM {$this->table} WHERE {$where}";
        }
        return $sql;
    }
    #构造where语句
    private function _build_where()
    {
        $where = '';
        if(is_array($this->where)){
            foreach ($this->where as $key=>$value){
                $value = is_string($value) ? "'".$value."'" : $value;

                $where.="`{$key}`={$value} and ";
            }
        }else{
            $where = $this->where;
        }
        $where = rtrim($where,'and ');
        return $where;

    }
}