Community Learn Tools Library Leisure
search
English
Home > List of blog posts > PHP实战分页显示和分页导航
Blogger Information
Blog 54
fans 6
comment 31
visits 107459
Special Recommendation
More>
Related recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
PHP实战分页显示和分页导航
吾逍遥
Original
2178 people have browsed it

一、分页原理

数据分页其实就是SQL构造语句的Limit使用，其有两种形式:SELECT * FROM user LIMIT 0,10;和SELECT * FROM USER LIMIT 10 OFFSET 0;。若是LIMIT 0,10则偏移量是0，10是记录条数，而是使用LIMIT 10 OFFSET 0表示意思是一样的，即若LIMIT后面只有一个数字，则表示取得记录条数。

关于偏移量的计算是offset=(page-1)*num;。page是当前页的页码，即是index.php?p=2中2。num则表示每页要显示的记录条数。

二、封装的PDO数据库操作类

写本博文开始自己封装常用的类了，如数据库类Db，目前是第一版，实现了数据库链式查询和CURD操作，至于预处理和事务则后续版本中逐渐增加。

  1. declare(strict_types=1);
  3. namespace WOXIAOYAO;
  5. use \PDO;
  6. use \Exception;
  8. /*
  9.  * @Descripttion: 自己封装的PDO类，可支持MySQL、MSSQL、ORACLE和SQLite的数据库操作
  10.  * @version: 1.0.0
  11.  * 准备要完成功能:1、支持PDO的query和exec(1.0.0)  2、支持PDO的预处理   3、支持PDO的事务处理
  12.  */
  14. //  准备知识:四种数据库连接方式
  15. //  MySQL:'dsn'=>'mysql:host=localhost;dbname=talk','username'=>'root','password'=>'123456'
  16. //  MSSQL:'dsn'=>'odbc:Driver={SQL Server};Server=192.168.1.60;Database=his','username'=>'sa','password'=>'xxxxx'
  17. //  Oracle:'dsn'=>'oci:dbname=orcl','username'=>'BAOCRM','password'=>'BAOCRM'
  18. //  SQLite:'dsn'=>'sqlite:'.dirname(__FILE__).'\log.db'
  20. // 抽象类完成单例模式连接、准备处理方法和接口的定义
  21. // 抽象类的保护静态成员为所有子类共享
  22. abstract class aDb
  23. {
  24.     // 定义单例模式连接
  25.     protected static $pdo = null;
  26.     protected static $config = null;
  27.     final protected function connect(array $config)
  28.     {
  29.         $config = array_change_key_case($config, CASE_LOWER);
  30.         if ($config === false) throw new Exception('连接配置不是数组');
  31.         if (empty($this->is_assoc($config))) throw new Exception('连接配置不是关联数组');
  32.         if (empty(self::$config)) {
  33.             self::$config = $config;
  34.         } else if (!empty(array_diff_assoc(self::$config, $config))) {
  35.             self::$config = $config;
  36.         } else {
  37.             return self::$pdo;
  38.         }
  39.         try {
  40.             $pdo = new \PDO(self::$config['dsn'], self::$config['username'], self::$config['password']);
  41.             // 若没报错则先清除旧连接，重置为新连接
  42.             self::$pdo = null;
  43.             self::$pdo = $pdo;
  44.             self::$pdo->query("set names utf8");
  45.             //属性名 属性值 数组以关联数组返回
  46.             self::$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
  47.             self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
  48.         } catch (\Exception $e) {
  49.             echo '数据库连接失败,详情: ' . $e->getMessage() . ' 请在配置文件中数据库连接信息';
  50.             exit();
  51.         }
  52.     }
  54.     // 判断是否是关联数组
  55.     final protected function is_assoc(array $arr)
  56.     {
  57.         if (is_array($arr)) {
  58.             $key = array_keys($arr);
  59.             return $key === array_keys($key) ? false : true;
  60.         }
  61.         return null;
  62.     }
  64.     // 定义接口规范
  65.     // 链式规范
  66.     abstract public function table(string $table);
  67.     abstract public function field(string $fields);
  68.     abstract public function where($where);
  69.     abstract public function order(string $order);
  70.     abstract public function limit(string $limit);
  71.     // 单条记录和多条记录查询
  72.     abstract public function find();
  73.     abstract public function select();
  74.     // 插入、更新和删除规范
  75.     abstract public function insert(array $data);
  76.     abstract public function update(array $data);
  77.     abstract public function delete();
  78. }
  80. // 工作类，实现CURD操作
  81. class Db extends aDb
  82. {
  83.     private $res;
  84.     private $table;
  85.     private $fields = '*';
  86.     private $where = 'true';
  87.     private $order;
  88.     private $limit;
  90.     function __construct(array $config)
  91.     {
  92.         $this->connect($config);
  93.     }    
  94.     function getConfig()
  95.     {
  96.         return parent::$config;
  97.     }
  98.     function getPDO()
  99.     {
  100.         return parent::$pdo;
  101.     }
  102.     private function reset(){
  103.         // $table='';
  104.         $this->fields = '*';
  105.         $this->where = 'true';
  106.         $this->order = '';
  107.         $this->limit = '';
  108.     }
  110.     // 链式查询
  111.     function table(string $table)
  112.     {
  113.         if (!is_string($table)) throw new Exception("参数是字符串，形式如'user'表示user表");
  114.         if (!empty($table))
  115.             $this->table = $table;
  116.         return $this;
  117.     }
  118.     function field(string $fields)
  119.     {
  120.         if (!is_string($fields)) throw new Exception("参数是字符串，形式如'id,name,pwd'表示获取3个字段");
  121.         if (!empty($fields))
  122.             $this->fields = $fields;
  123.         return $this;
  124.     }
  125.     function where($where)
  126.     {
  127.         if (is_string($where)) {
  128.             if (!empty($fields))
  129.                 $this->where .= " and {$where}";
  130.             return $this;
  131.         }
  132.         if ($this->is_assoc($where)) {
  133.             while (current($where)) {
  134.                 $this->where .= ' and ' . key($where) . '=' . current($where);
  135.                 next($where);
  136.             }
  137.             return $this;
  138.         }
  139.         throw new Exception('请检查条件');
  140.     }
  141.     function order(string $order)
  142.     {
  143.         if (!is_string($order)) throw new Exception("参数是字符串，形式如'id asc'表示id升序");
  144.         if (!empty($order))
  145.             $this->order = $order;
  146.         return $this;
  147.     }
  148.     function limit(string $limit)
  149.     {
  150.         if (!is_string($limit)) throw new Exception("参数是字符串，形式如'0,5'表示偏移0数量是5");
  151.         if (!empty($limit))
  152.             $this->limit = $limit;
  153.         return $this;
  154.     }
  155.     // 查询单条记录
  156.     function find()
  157.     {
  158.         try {
  159.             if (empty($this->table)) throw new Exception('没有查询表');
  160.             $sql = "SELECT {$this->fields} FROM {$this->table} WHERE {$this->where}";
  161.             if (!empty($this->order))
  162.                 $sql .= " ORDER BY {$this->order}";
  163.             $this->res = parent::$pdo->query($sql);
  164.             $this->reset();
  165.             return $this->res->fetch(PDO::FETCH_ASSOC);
  166.         } catch (\PDOException $e) {
  167.             return '查询错误信息：' . $e->getMessage();
  168.         }
  169.     }
  170.     // 查询所有记录
  171.     function select()
  172.     {
  173.         try {
  174.             if (empty($this->table)) throw new Exception('没有查询表');
  175.             $sql = "SELECT {$this->fields} FROM {$this->table} WHERE {$this->where}";
  176.             if (!empty($this->order))
  177.                 $sql .= " ORDER BY {$this->order}";
  178.             if (!empty($this->limit))
  179.                 $sql .= " LIMIT {$this->limit}";
  180.             $this->res = parent::$pdo->query($sql);
  181.             $this->reset();
  182.             return $this->res->fetchAll(PDO::FETCH_ASSOC);
  183.         } catch (\PDOException $e) {
  184.             return '查询错误信息：' . $e->getMessage();
  185.         }
  186.     }
  188.     // 插入、更新和删除操作
  189.     public function insert(array $data)
  190.     {
  191.         if (empty($this->is_assoc($data))) throw new Exception('插入数据不是关联数组');
  192.         if (empty($this->table)) throw new Exception('插入时必须指定表');
  193.         $sql = "INSERT INTO {$this->table}";
  194.         $key = key($data);
  195.         $value = "'" . current($data) . "'";
  196.         next($data);
  197.         while (current($data)) {
  198.             $key .= "," . key($data);
  199.             $value .= ",'" . current($data) . "'";
  200.             next($data);
  201.         }
  202.         $sql .= " ({$key}) VALUES ({$value})";
  203.         $this->res = parent::$pdo->exec($sql);
  204.         $this->reset();
  205.         return $this->res;
  206.     }
  207.     public function update(array $data)
  208.     {
  209.         if (empty($this->is_assoc($data))) throw new Exception('更新数据不是关联数组');
  210.         if (empty($this->table)) throw new Exception('更新时必须指定表');
  211.         if ($this->where == 'true') throw new Exception('更新时必须指定条件');
  212.         $sql = "UPDATE {$this->table}";
  213.         $item = key($data) . "='" . current($data) . "'";
  214.         next($data);
  215.         while (current($data)) {
  216.             $item .= "," . key($data) . "='" . current($data) . "'";
  217.             next($data);
  218.         }
  219.         $sql .= " SET {$item} WHERE {$this->where}";
  220.         $this->res = parent::$pdo->exec($sql);
  221.         $this->reset();
  222.         return $this->res;
  223.     }
  224.     public function delete()
  225.     {
  226.         try {
  227.             if (empty($this->table)) throw new Exception('删除时必须指定表');
  228.             if ($this->where == 'true') throw new Exception('删除时必须指定条件');
  229.             $sql = "DELETE FROM {$this->table} WHERE {$this->where}";
  230.             $this->res = parent::$pdo->exec($sql);
  231.             $this->reset();
  232.             return $this->res;
  233.         } catch (\PDOException $e) {
  234.             return '查询错误信息：' . $e->getMessage();
  235.         }
  236.     }
  237. }

三、基础版的分页

上面已经准备了数据库，也理解了分页的知识，下面代码反而比较简单，直接看代码

  1. //pagnate.php
  2. require_once 'Db.php';
  4. use WOXIAOYAO\Db;
  6. $config = [
  7.     'dsn' => 'mysql:host=localhost;dbname=test',
  8.     'username' => 'root',
  9.     'password' => 'root'
  10. ];
  11. $obj = new Db($config);
  13. //分页获取数据
  14. $num = 10;
  15. $res = $obj->table('user')->field('count(id) as total')->select();
  16. $total = intval($res[0]['total']);
  17. $pages = ceil($total / $num);
  18. $page = $_GET['p'] ?? 1;
  19. $offset = ($page - 1) * $num;
  20. $users = $obj->table('user')->limit("{$offset},{$num}")->select();
  1. // index.php
  2. <style>
  3.     * {
  4.         margin: 0;
  5.         padding: 0;
  6.         box-sizing: border-box;
  7.     }
  9.     a {
  10.         text-decoration: none;
  12.         display: inline-block;
  13.         /* width: 2em; */
  14.         height: 2em;
  15.         line-height: 2em;
  16.     }
  18.     .container {
  19.         width: 60vw;
  20.         margin: 1em auto;
  21.     }
  23.     td {
  24.         text-align: center;
  25.     }
  27.     .page {
  28.         margin-top: 1em;
  29.         text-align: center;
  30.     }
  32.     td a:first-child {
  33.         margin-right: 5px;
  34.     }
  36.     td a:last-child {
  37.         margin-left: 5px;
  38.     }
  40.     .page a {
  41.         padding: 0 0.5em;
  42.         margin: 0 5px;
  43.     }
  45.     .page a.cur {
  46.         background-color: #007d20;
  47.         color: white;
  48.     }
  49. </style>
  50. <div class="container">
  51.     <table border='1' cellspacing="0" width="100%">
  52.     <caption>用户信息表</caption>
  53.         <thead>
  54.             <tr bgColor="lightgray">
  55.                 <th>ID</th>
  56.                 <th>name</th>
  57.                 <th>password</th>
  58.                 <th>操作</th>
  59.             </tr>
  60.         </thead>
  61.         <tbody>
  62.             <?php
  63.             include_once 'pagnate.php';
  64.             foreach ($users as $user) {
  65.                 $trdata = "<tr>";
  66.                 foreach ($user as $item) {
  67.                     $trdata .= "<td>{$item}</td>";
  68.                 }
  69.                 $trdata .= "<td><a href='#'>编辑</a><a href='#'>删除</a></td>";
  70.                 $trdata .= "</tr>";
  71.                 echo $trdata;
  72.             }
  73.             ?>
  74.         </tbody>
  75.     </table>
  76.     <div class="page">
  77.         <?php
  78.         echo "<a href='{$_SERVER["PHP_SELF"]}?p=1'>首页</a>";
  79.         $prev = ($page - 1 > 1) ? ($page - 1) : 1;
  80.         if ($page > 1)
  81.             echo "<a href='{$_SERVER["PHP_SELF"]}?p={$prev}'>上一页</a>";
  82.         for ($i = 1; $i <= $pages; $i++) :
  83.             if ($i == $page)
  84.                 echo "<a class='cur' href='{$_SERVER["PHP_SELF"]}?p={$i}'>{$i}</a>";
  85.             else
  86.                 echo "<a href='{$_SERVER["PHP_SELF"]}?p={$i}'>{$i}</a>";
  87.         endfor;
  88.         $next = ($page + 1) < $pages ? ($page + 1) : $pages;
  89.         if ($page < $pages)
  90.             echo "<a href='{$_SERVER["PHP_SELF"]}?p={$next}'>下一页</a>";
  91.         echo "<a href='{$_SERVER["PHP_SELF"]}?p={$pages}'>未页</a>";
  92.         ?>
  93.     </div>
  94. </div>

四、对分页导航栏的改进

无论是PHP中文网或老师演示的省略号仅仅展示，而无实际功能，而我认为前省略号相当于前一页，后面省略号相当于后一页。具体代码我在老师的基础上进行了精简。下面分布导航既有老师所演示的省略号功能，而且省略号也能起到跳转页码作用。

  1. //pagnate.php
  2. // 改进的导航栏（在基础版中增加）
  3. $startPage = 1;
  4. // 显示页码数最好为奇数
  5. $showPage = 5;
  6. if (($page - ceil(($showPage - 1) / 2)) > $startPage)
  7.     $startPage = $page - ceil(($showPage - 1) / 2);
  1. //index2.php
  2. // 分页数据代码不变，对分页页码导航代码重新编写
  3. <div class="page">
  4.     <?php
  5.     echo "<a href='{$_SERVER["PHP_SELF"]}?p=1'>首页</a>";
  6.     $prev = ($page - 1 > 1) ? ($page - 1) : 1;
  7.     if ($startPage > 1)
  8.         echo "<a href='{$_SERVER["PHP_SELF"]}?p={$prev}'>...</a>";
  9.     for ($i = $startPage; $i < $startPage+$showPage; $i++) :
  10.         if ($i == $page)
  11.             echo "<a class='cur' href='{$_SERVER["PHP_SELF"]}?p={$i}'>{$i}</a>";
  12.         else
  13.             echo "<a href='{$_SERVER["PHP_SELF"]}?p={$i}'>{$i}</a>";
  14.     endfor;
  15.     $next = ($page + 1) < $pages ? ($page + 1) : $pages;
  16.     if ($startPage+$showPage <= $pages+1)
  17.         echo "<a href='{$_SERVER["PHP_SELF"]}?p={$next}'>...</a>";
  18.     echo "<a href='{$_SERVER["PHP_SELF"]}?p={$pages}'>未页</a>";
  19.     ?>
  20. </div>

pagnate

关于单条数据的编辑和删除: 其实就是数据库的更新和删除操作，比较简单，我这里就不演示了

Correcting teacher：灭绝师太灭绝师太

Correction status：qualified

Teacher's comments：php中文网不教授没有用武之地的课程, 设计程序要从用户体验出发, 便民利民才能走的更远, 参考支付宝,微信给你带来的遍历~(除了微信中聊天转账功能不分离有待商榷)
Statement of this Website
The copyright of this blog article belongs to the blogger. Please specify the address when reprinting! If there is any infringement or violation of the law, please contact admin@php.cn Report processing!
All comments Speak rationally on civilized internet, please comply with News Comment Service Agreement
0 comments
Author's latest blog post