search

博客列表 >PHP - 实现分页以及数据删改 实例

PHP - 实现分页以及数据删改 实例

晴天
晴天原创
2020年05月13日 19:38:00789浏览

分页原理

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. }

总结

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

上一条：redis 键的过期回调。常用在判断一段时间后订单是否是支付下一条：php文件上传原理
声明：本文内容转载自脚本之家，由网友自发贡献，版权归原作者所有，如您发现涉嫌抄袭侵权，请联系admin@php.cn 核实处理。
全部评论
文明上网理性发言，请遵守新闻评论服务协议

相关文章

查看更多