Community Learn Tools Library Leisure
search
English
Home > List of blog posts > PHP - 实现分页以及数据删改 实例
Blogger Information
Blog 47
fans 1
comment 0
visits 52941
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
790 people have browsed it

分页原理

1. 术语

  • 记录索引:
  • 偏移量:
  • 显示数量
  1. // 第一页
  2. SELECT * FROM `staffs` LIMIT 5 OFFSET 0;
  4. // 第二页
  5. SELECT * FROM `staffs` LIMIT 5 OFFSET 0;
  7. 第一页索引: 0 - 4
  8. 第二页索引: 5 - 9
  9. 第三页索引: 10 - 14
  11. 偏移量 = 每页显示的数量 * (当前页数 - 1)
  13. 第一页: 5 * (1 - 1) = 0
  14. 第二页: 5 * (2 - 1) = 5
  15. 第三页: 5 * (3 - 1) = 10
  16. ...

演示代码

演示地址 http://php.rc238.cn/0512/

index.php

  1. <!DOCTYPE html>
  2. <html lang="en">
  4. <head>
  5.     <meta charset="UTF-8" />
  6.     <meta name="viewport" content="width=device-width, initial-scale=1.0" />
  7.     <title>员工管理系统</title>
  8.     <link rel="stylesheet" href="style.css" />
  9. </head>
  11. <body>
  12.     <table>
  13.         <caption>
  14.             员工管理系统
  15.         </caption>
  16.         <thead>
  17.             <tr>
  18.                 <th>ID</th>
  19.                 <th>姓名</th>
  20.                 <th>年龄</th>
  21.                 <th>性别</th>
  22.                 <th>职位</th>
  23.                 <th>手机</th>
  24.                 <th>入职时间</th>
  25.                 <th>操作</th>
  26.             </tr>
  27.         </thead>
  28.         <tbody>
  29.             <?php
  31. require 'demo1.php';
  32. foreach ($staffs as $staff){
  33. //    数组转名值对
  34.     extract($staff);
  35.     /* @var string $id;
  36.      * @var string $name;
  37.      * @var string $age;
  38.      * @var string $sex;
  39.      * @var string $position;
  40.      * @var string $mobile;
  41.      * @var string $hiredate;
  42.      * */
  43.     $sex  = $sex ? '男' : '女';
  44.     $hiredate = date('Y / m / d',$hiredate);
  45.     echo <<<AAA
  46.         <tr>
  47.           <td>{$id}</td>
  48.           <td>{$name}</td>
  49.           <td>{$age}</td>
  50.           <td>{$sex}</td>
  51.           <td>{$position}</td>
  52.           <td>{$mobile}</td>
  53.           <td>{$hiredate}</td>
  54.           <td><a href="update.php?p={$page}&update_id={$id}">编辑</a><a href="delect.php?p={$page}&delect_id={$id}">删除</a></td>
  55.         </tr>
  56. AAA;
  58. }
  60. //删除数据
  62. ?>
  63.         </tbody>
  64.     </table>
  65.     <div>
  66.         <?php
  68. //    判断是不是第一页
  69.     if ($page > 1){
  70.         echo "<a href='?p=1' class='headtail'>首页</a>";
  71.             $start = $page-1;
  72.         echo "<a href='?p={$start}' class='around'>上一页</a>";
  73.     }
  75. //创建输出方法
  76.     function Output($i){
  77.         global $page;
  78.         $jump = sprintf('?p=%s',$i);
  79.         $active = ($i==$page)?'active':null;
  80.         echo "<a href='{$jump}' class='{$active}'>{$i}</a>";
  81.     }
  82. //    输出前两条
  83.     for ($i=1;$i<=2;$i++){
  84.         Output($i);
  85.     }
  86. //    判断输出 ...
  87.     if ($page > 5){
  88.       echo "<span > ... </span>";
  89.     }
  90. //    输出前后页数
  91. //    当前页小于五输出1234567
  92.     if ($page <= 5 ){
  93.         for ($i=3;$i<=7;$i++){
  94.             Output($i);
  95.         }
  96.     }
  97. //    当前页大于5和小于总页数-4 输出当前页前2加厚2
  98.     if ($page >5 && $page < $pages-4){
  99.         for ($i=($page-2);$i<=$page+2;$i++){
  100.             Output($i);
  101.         }
  102.     }
  103. //    当前页大于总页数-4 输出后面全部页数
  104.     if ($page>=$pages-4){
  105.         for ($i=$pages-4;$i<=$pages;$i++){
  106.             Output($i);
  107.         }
  108.     }
  109. // 判断输出
  110. if ($page<$pages-4){
  111.     echo "<span > ... </span>";
  112. }
  113. //尾部
  114.     if ($page<=$pages-5 ) {
  116.         for ($i = ($pages - 1); $i <= $pages; $i++) {
  117.             Output($i);
  118.         }
  119.     }
  120.     if ($page < $pages ){
  121.         $next = $page+1;
  122.         echo "<a href='?p={$next}' class='around'>下一页</a>";
  123.         echo "<a href='?p={$pages}'class='headtail'>尾页</a>";
  124.     }
  125.     if ($pages > 10 ){
  126.         echo <<<bbb
  127.     <form class="form1" action="" method="get">
  128.         <input type="number" name="p"min="1" max=" {$pages}" required />
  129.         <button>跳转</button>
  130.     </form>
  131. bbb;
  133.     }
  134.     ?></div>
  136. </body>
  138. </html>

demo1.php

  1. <?php
  2. //连接数据库
  3. require 'connect.php';
  4. ////非法请求
  5. $err = "<script>alert('非法请求');location.href='index.php';</script>";
  6. //当前页码
  7. $page = $_GET['p'] ?? 1 ;
  8. if ($page<=0){
  9.     die($err);
  10. }
  11. //每页记录数量
  12. $num = 12;
  14. //获取总页数
  15. $sql = "SELECT CEIL(COUNT(`id`)/{$num}) AS `total` FROM `staffs`";
  16. $pages = $pdo->query($sql)->fetch()['total'];
  17. if ($page>$pages){
  18.     die($err);
  19. }
  20. //偏移量
  21. $offset = $num*($page-1);
  23. //分页的数据
  24. $sql = "SELECT * FROM `staffs` LIMIT {$num} OFFSET {$offset}";
  25. $staffs = $pdo->query($sql)->fetchAll();

connect.php

  1. <?php
  3. $dsn = 'mysql:host=localhost;dbname=php11.edu';
  4. $username = 'php11.edu';
  5. $password = 'php11.edu';
  7. try {
  8.     $pdo = new PDO($dsn,$username,$password);
  9.     $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
  10. }catch (Exception $e){
  11.     die($e->getMessage());
  12. }

delect.php

  1. <?php
  2. //链接数据库
  3. require 'connect.php';
  4. //获取返回页码
  5. $page = 'index.php?p='.$_GET['p'];
  6. //获取删除id
  7. $delect_id = [$_GET['delect_id']];
  9. //创建sql语句
  10. $sql = 'DELETE FROM `staffs` WHERE `id`=?';
  11. //执行删除
  12. $stmt = $pdo->prepare($sql);
  13. $stmt->execute($delect_id);
  14. //判断是否删除成功
  15. if ($stmt->rowCount() === 1){
  16.     echo "<script>alert('删除成功:id={$_GET['delect_id']}');location.href='{$page}'</script>";
  17. }else{
  18.     echo "<script>alert('删除失败');location.href='{$page}'</script>";
  19. }

update.php

  1. <?php
  2. //更新修改数据库
  3. //链接数据库
  4. require 'connect.php';
  5. //获取id
  6. $update_id = $_GET['update_id'];
  7. //获取页数
  8. $page = $_GET['p'];
  10. //先查询到信息显示
  11. $sql = "SELECT * FROM `staffs` WHERE `id`={$update_id}";
  12. $user = $pdo->query($sql)->fetch(PDO::FETCH_ASSOC);
  13. extract($user);
  14. /**
  15.  * @var string $id;
  16.  * @var string $name;
  17.  * @var string $age;
  18.  * @var string $sex;
  19.  * @var string $position;
  20.  * @var string $mobile;
  21.  * @var string $hiredate;
  22.  **/
  23. $sex = $sex ? '男': '女';
  24. $date  = date('Y / m / d' ,$hiredate);
  25. echo <<< aa
  26. <style>
  27. @import "style.css";
  28. </style>
  30.     <form class="update" action="update1.php" method="post">
  31.       <div>
  32.         <label for="id">ID:</label>
  33.         <input type="text" id="id"  disabled value="{$id}" />
  34.       </div>
  35.       <div>
  36.         <label for="name">姓名:</label>
  37.         <input type="text" id="name" name="name" required value="{$name}" />
  38.       </div>
  39.       <div>
  40.         <label for="age">年龄:</label>
  41.         <input type="number" max="99" min="18" id="age" name="age" required value="{$age}" />
  42.       </div>
  43.       <div>
  44.         <label for="sex">性别:</label>
  45.         <input type="text" id="sex" disabled value="{$sex}" />
  46.       </div>
  47.       <div>
  48.         <label for="position">职位:</label>
  49.         <input type="text" id="position" name="position" required value="{$position}" />
  50.       </div>
  51.       <div>
  52.         <label for="mobile">手机号:</label>
  53.         <input
  54.           type="text"
  55.           id="mobile"
  56.           name="mobile"
  57.           required
  58.           minlength="11"
  59.           maxlength="11"
  60.           value="{$mobile}"
  61.         />
  62.       </div>
  63.       <div>
  64.         <label for="hiredate">入职时间:</label>
  65.         <input type="text" id="hiredate" disabled value="{$date}" />
  66.       </div>
  67.       <input type="hidden" name="p" value="{$page}">
  68.       <button name="id" value="{$id}">提交</button>
  69.     </form>
  70. aa;

update1.php

  1. <?php
  2. require 'connect.php';
  4. //获取值
  5. $page = $_POST['p'];
  6. $id = $_POST['id'];
  7. $name = $_POST['name'];
  8. $age = $_POST['age'];
  9. $position = $_POST['position'];
  10. $mobile = $_POST['mobile'];
  12. //执行更新
  13. $sql = "UPDATE `staffs` SET `name` = '{$name}' ,`age` = '{$age}' , `position` = '{$position}',`mobile` = '{$mobile}' WHERE `id` = {$id}";
  14. $stmt = $pdo->prepare($sql);
  15. $stmt->execute();
  16. //判断是否执行成功
  17. if ($stmt->rowCount()===1){
  18.     echo "<script>alert('修改成功');location.href='index.php?p={$page}'</script>";
  19. }else{
  20.     if ($stmt->errorInfo()[0] == 00000){
  21.         echo "<script>alert('没有值被修改');location.href='index.php?p={$page}'</script>";
  22.     }else{
  23.         echo "<script>alert('修改失败');location.href='index.php?p={$page}'</script>";
  24.     }
  25. }

style.css

  1. * {
  2.   margin: 0px;
  3.   padding: 0px;
  4.   box-sizing: border-box;
  5.   color: #555;
  6. }
  7. body {
  8.   display: flex;
  9.   flex-direction: column;
  10.   align-items: center;
  11. }
  12. table {
  13.   width: 1000px;
  14.   border: 1px solid;
  15.   text-align: center;
  16.   border-collapse: collapse;
  17. }
  18. table > caption {
  19.   font-size: 1.2rem;
  20.   margin: 10px;
  21. }
  22. table td,
  23. table th {
  24.   border: 1px solid;
  25.   padding: 5px;
  26. }
  27. table tr:hover {
  28.   background-color: #eee;
  29. }
  30. div {
  31.   display: flex;
  32.   flex-flow: row nowrap;
  33.   align-items: center;
  34. }
  35. div > a {
  36.   padding: 5px;
  37.   border: 1px solid;
  38.   margin: 10px;
  39.   width: 30px;
  40.   height: 30px;
  41.   text-decoration: none;
  42.   text-align: center;
  43. }
  44. div > a:hover {
  45.   background-color: violet;
  46.   color: #fff;
  47. }
  48. .headtail {
  49.   width: 50px;
  50. }
  51. .around {
  52.   width: 80px;
  53. }
  54. .active {
  55.   background-color: lightskyblue;
  56.   color: linen;
  57. }
  58. .form1 > input {
  59.   width: 50px;
  60.   height: 30px;
  61. }
  62. .form1 > input:out-of-range,
  63. .form1 > input::-webkit-inner-spin-button {
  64.   appearance: none;
  65. }
  67. .update {
  68.   display: flex;
  69.   flex-flow: column;
  70.   margin: 30px auto;
  71. }
  72. .update > div {
  73.   width: 220px;
  74.   margin-bottom: 10px;
  75.   display: flex;
  76.   justify-content: space-between;
  77. }
  78. tbody a {
  79.   text-decoration: none;
  80.   margin: 0px 10px;
  81.   background-color: lightskyblue;
  82.   padding: 3px 10px;
  83.   color: #fff;
  84.   border: none;
  85.   border-radius: 5px;
  86. }
  87. tbody a:hover {
  88.   background-color: aqua;
  89. }

总结

感觉这个方法太笨了，总算能够实现功能，一定有更优美的方式实现

Correcting teacher：天蓬老师天蓬老师

Correction status：qualified

Teacher's comments：这个案例今晚会讲, 你看一下不一样的实现方案
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