php封装链式调用数据库访问类以及数据分页实现

1.Db数据库访问类代码:

<?php
class Db{
    private $where =array();
    private $field = '*';
    private $order = '';
    private $limit = 0;
    private $pdo = null;

    // 构造初始化pdo连接对象
    public function __construct(){
        define('DSN','mysql:host=localhost;dbname=edu');
        define('USERNAME','root');
        define('PASSWORD','root');
        $this->pdo = new PDO(DSN,USERNAME,PASSWORD);
        $this->pdo->query('set names utf8;');
    }

    // 指定数据表
    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;
    }

    // 指定where条件
    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] : false;
    }

    // 返回多条数据记录
    public function lists(){
        $sql = $this->_build_sql('select');
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // 查询数据总数
    public function count(){
        $sql = $this->_build_sql('count');
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        $total = $stmt->fetchColumn(0);
        return $total;
    }
    // 分页
    public function pages($page,$pageSize = 10,$path = '/'){
        // $page = intval($page);
        $count = $this->count();
        $this->limit = ($page - 1) * $pageSize.','.$pageSize;
        $data = $this->lists();
        $page_count = ceil($count/$pageSize);
        if (!is_numeric($page) || $page > $page_count ) {
            exit("<script>alert('参数错误!');</script>");
        }
        $pages = $this->_subPages($page,$pageSize,$count,$path);
        return [
            'total'=>$count,
            'data'=>$data,
            'pages'=>$pages,
        ];
    }

    // 生成分页html(bootstrap4风格);cur_page当前第几页,pageSite每页大小,total数据总数
    private function _subPages($cur_page,$pageSize,$total,$path){
        $symbol = '?';
        $index = strpos($path,'?');
        if ($index !== false && $index > 0) {
            $symbol = '&';
        }
        // 分页数,向上取整
        $html = '';
        $page_count = ceil($total/$pageSize);

        // 生成首页,生成上一页
        if ($cur_page > 1) {
            $html .= "<li class='page-item'><a href='{$path}{$symbol}page=1' class='page-link'>首页</a></li>";
            $pre_page = $cur_page - 1;
            $html .= "<li class='page-item'><a href='{$path}{$symbol}page={$pre_page}' class='page-link'>上一页</a></li>";
        }

        // 遍历页码

        $start = $cur_page > ($page_count - 6) ? ($page_count - 6) : $cur_page;
        $start = $start - 2;
        $start = $start <=0 ? 1 : $start;
        $end = ($cur_page + 6) > $page_count ? $page_count : ($cur_page + 6);
        $end = $end - 2;
        if ($cur_page+2 >= $end && $page_count > 6) {
            $start = $start + 2;
            $end = $end + 2;
        }
        for ($i=$start; $i <= $end; $i++) { 
            $html .= $i == $cur_page ? "<li class='page-item active'><a class='page-link' href='javascrip:;'>{$i}</a></li>" : "<li class='page-item'><a class='page-link' href='{$path}{$symbol}page={$i}'>{$i}</a></li>";
        }
        // 生成下一页,生成尾页
        if ($cur_page < $page_count) {
            $after_page = $cur_page + 1;
            $html .= "<li class='page-item'><a href='{$path}{$symbol}page={$after_page}' class='page-link'>下一页</a></li>";
            $html .= "<li class='page-item'><a href='{$path}{$symbol}page={$page_count}' class='page-link'>尾页</a></li>";
        }

        /* $num = 4;
        $start = $cur_page-(int)($num/2);
        $start = $start > 0 ? $start : 1;
        if ($start > $page_count - $num + 1) {
            $start = $page_count - $num + 2;
        }
        for ($i=1; $i <= min($page_count,$num); $i++) { 
            $html .= "<li ".($start == $cur_page ? 'class="page-item active"' : 'class="page-item"').'><a class="page-link" '.($start == $cur_page ? "href='javascrip:;'" : "href='{$path}{$symbol}page={$start}'").">{$start}</a></li>";
            $start++;
        }

        // 生成下一页,生成尾页
        if ($cur_page < $page_count) {
            $after_page = $cur_page + 1;
            $html .= "<li class='page-item'><a href='{$path}{$symbol}page={$after_page}' class='page-link'>下一页</a></li>";
            $html .= "<li class='page-item'><a href='{$path}{$symbol}page={$page_count}' class='page-link'>尾页</a></li>";
        } */
        $html = '<nav aria-label="Page navigation example"><ul class="pagination pagination-sm">'.$html.'</ul></nav>';
        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 delete(){
        $sql = $this->_build_sql('delete');
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->rowCount();
    }

    // 更新数据(并返回受影响的行数)
    public function update($data){
        $sql = $this->_build_sql('update',$data);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->rowCount();
    }

    // 构造sql语句
    private function _build_sql($type,$data=null){
        $sql = '';
        // 查询条数count
        if ($type == 'count') {
            $where = $this->_build_where();
            $field_list = explode(',',$this->field);
            $field = count($field_list) > 1 ? '*' : $this->field;
            $sql = "select count({$field}) from {$this->table} {$where}";
        }

        // 查询
        if ($type == 'select') {
            $where = $this->_build_where();
            $sql = "select {$this->field} from {$this->table} {$where}";
            if ($this->order) {
                $sql .= " order by {$this->order}";
            }
            if ($this->limit) {
                $sql .=" limit {$this->limit}";
            }
        }

        // 添加
        if ($type == 'insert') {
            $sql = "insert into {$this->table}";
            $fields = $values = [];
            foreach ($data as $key => $val) {
                $fields[] = $key;
                $values[] = is_string($val) ? "'".$val."'" : $val;
            }
            $sql .= "(".implode(',',$fields).") values(".implode(',',$values).")";
        }

        // 删除
        if ($type == 'delete') {
            $where = $this->_build_where();
            $sql = "delete from {$this->table} {$where}";
        }

        // 修改
        if ($type == 'update') {
            // 生成where条件
            $where = $this->_build_where();
            // 生成set
            $str = '';
            foreach ($data as $key => $val) {
                $val = is_string($val) ? "'".$val."'" : $val;
                $str .= "{$key}={$val},";
            }
            $str = rtrim($str,',');
            $sql = "update {$this->table}";
            $str = $str ? "set {$str}" : '';
            $sql = "update {$this->table} {$str} {$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 ');
        $where = $where=='' ? '' : "where {$where}";
        return $where;
    }
}

2.链式调用类代码:

<?php
require_once 'lib/Db.php';
$db = new Db();
// 查询
// $res = $db->table('student')->field('id,name')->order('id desc')->where('id>3')->limit(3)->lists();

// 插入数据
$data = [
    'name' => '张三',
    'email' => '1158624818@qq.com',
    'course' => 'ajax',
    'grade' => '58',
    'create_time' => time(),
    'update_time' => time(),
];
// $id = $db->table('student')->insert($data);
// 删除
// $res = $db->table('student')->where(['id'=>8])->delete();
// 更新
/* $data = [
'name'=>'赵六',
];
$res = $db->table('student')->where(['id'=>2])->update($data);
echo "<pre>";
print_r($res);
echo "</pre>";
 */

// 分页
// $grade = $_GET['grade']; 
$page = $_GET['page']; // 第几页
$pageSize = 1;
// $db->table('student')->field('id,name')->where('id>1')->count();
// $res = $db->table('student')->where('id>1 and grade>'.$grade)->pages($page, $pageSize,'/blog/?grade='.$grade);
$res = $db->table('student')->where('id>1')->pages($page, $pageSize,'/blog/');
// $res = json_encode($res);
/* echo "<pre>";
print_r($res);
echo "</pre>";
 */
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>数据分页</title>
    <link rel="stylesheet" href="static/css/bootstrap.min.css">
<script src="static/js/jquery.min.js"></script>
<script src="static/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<h2 class="text-center">用户表</h2>
<p class="text-center">共查询出:<?php echo $res['total']; ?> 条记录</p>
<table class="table table-striped">
  <thead class="thead-dark">
    <tr>
      <th scope="col">ID</th>
      <th scope="col">姓名</th>
      <th scope="col">邮箱</th>
      <th scope="col">课程</th>
      <th scope="col">成绩</th>
    </tr>
  </thead>
  <tbody>
    <?php foreach ($res['data'] as $article) {?>
        <tr>
        <th scope="row"><?php echo $article['id']; ?></th>
        <td><?php echo $article['name']; ?></td>
        <td><?php echo $article['email']; ?></td>
        <td><?php echo $article['course']; ?></td>
        <td><?php echo $article['grade']; ?></td>
        </tr>
    <?php }?>
  </tbody>
</table>
<?php echo $res['pages']; ?>
</div>
</body>
</html>
0
如无特殊说明,文章均为本站原创,转载请注明出处

该文章由 发布

这货来去如风,什么鬼都没留下!!!
发表我的评论

Hi,请填写昵称和邮箱!

取消评论
代码 贴图 加粗 链接 删除线 签到