博客列表 >PHP实战分页显示和分页导航

PHP实战分页显示和分页导航

吾逍遥
吾逍遥原创
2020年12月14日 20:33:102193浏览

一、分页原理

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

三、基础版的分页

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

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

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

上一条:标题和段落下一条:html温习
声明:本文内容转载自脚本之家,由网友自发贡献,版权归原作者所有,如您发现涉嫌抄袭侵权,请联系admin@php.cn 核实处理。
全部评论
文明上网理性发言,请遵守新闻评论服务协议