Community Learn Tools Library Leisure
search
English
Home > List of blog posts > PHP分页操作数据表
Blogger Information
Blog 60
fans 5
comment 3
visits 65264
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分页操作数据表
longlong
Original
789 people have browsed it

1. 数据表分页

  • config.php
  1. <?php
  2. return [
  3.     'type' => $type ?? 'mysql',
  4.     'host' => $host ?? 'php.edu',
  5.     'dbname' => $dbname ?? 'first',
  6.     'charset' => $charset ?? 'utf8',
  7.     'port' => $port ?? '3306',
  8.     'username' => $username ?? 'root',
  9.     'password' => $password ?? 'root',
  10. ];
  • connect.php
  1. <?php
  2. $config = require "config.php";
  3. extract($config);
  4. // echo $type,$host;
  5. // $dsn = 'mysql:host=php.edu;dbname=first;charset=utf8;port=3306';
  6. $dsn = sprintf('%s:host=%s;dbname=%s;charset=%s;port=%s',$type,$host,$dbname,$charset,$port);
  7. try {
  8.     $pdo = new PDO($dsn,$username,$password);
  9.     // 设置结果集获取方式：关联数组
  10.     $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
  11. } catch (Throwable $e) {
  12.     exit($e->getMessage());
  13. } catch (PDOException $e) {
  14.     exit($e->getMessage());
  15. }
  16. // var_dump($pdo);
  • page-data.php
  1. <?php
  2. // 1. 连接数据库
  3. require "connect.php";
  5. // 2. 获取分页的两个已知条件
  6. // 2.1. 每页显示的记录数量
  7. $num = 8;
  8. // 2.2 获取当前页数（通过url传输 p ），默认为1，p=1
  9. $page = $_GET['p'] ?? 1;
  11. // 3. 计算偏移量
  12. $offset = ($page - 1) * $num;
  14. // 4. 获取分页数据
  15. $sql = "SELECT * FROM `student` LIMIT {$num} OFFSET {$offset}";
  16. $users = $pdo->query($sql)->fetchAll();
  17. // print_r($result);
  19. // 5. 计算总页数
  20. $sql = "SELECT CEIL(COUNT(`id`)/{$num}) AS `total` FROM `student`";
  21. $pages = $pdo->query($sql)->fetch()['total'];
  22. // var_dump($pages);
  23. // echo $pages;
  • page-list.php
  1. <?php
  2. // 引入分页数据
  3. require "page-data.php"; 
  4. ?>
  6. <!DOCTYPE html>
  7. <html lang="en">
  8. <head>
  9.     <meta charset="UTF-8">
  10.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  11.     <title>数据分页</title>
  12.     <link rel="stylesheet" href="style.css">
  13. </head>
  14. <body>
  15.     <!-- 显示数据表的信息 -->
  16.     <table>
  17.     <caption><h2>用户信息表<h2></caption>
  18.         <thead>
  19.             <tr>
  20.                 <th>id</th>
  21.                 <th>用户名</th>
  22.                 <th>性别</th>
  23.                 <th>年龄</th>
  24.                 <th>联系电话</th>
  25.                 <th>操作</th>
  26.             </tr>
  27.         </thead>
  28.         <tbody>
  29.             <!-- 使用模板语法循环每个用户信息 -->
  30.             <?php foreach ($users as $user): ?>
  31.             <tr>
  32.                 <!-- 使用短标签简化变量的显示 -->
  33.                 <td><?=$user['id']?></td>
  34.                 <td><?=$user['username']?></td>
  35.                 <td><?=$user['sex']?></td>
  36.                 <td><?php echo $user['age']; ?></td>
  37.                 <td><?php echo $user['tel']; ?></td>
  38.                 <td>
  39.                     <!-- 点击编辑时跳转到一个编辑页面，通过GET方式 -->
  40.                     <button onclick="location.href='handle.php?action=edit&id=<?=$user['id']?>'">编辑</button>
  41.                     <!-- 点击删除时，调用del()函数 -->
  42.                     <button onclick="del(<?=$user['id']?>)">删除</button>
  43.                 </td>    
  44.             </tr>
  45.             <?php endforeach ?>
  46.         </tbody>
  47.     </table>
  48.     <!-- 分页条 -->
  49.     <div>
  51.         <!-- 2. 首页 -->
  52.         <a href="<?=$_SERVER['PHP_SELF'].'?p=1'?>">首页</a>
  53.         <!-- 3.上一页 -->
  54.         <?php
  55.             $prev = $page - 1;
  56.             // 当前页数为第一页时就不再前移
  57.             if ($page == 1) $prev = 1;
  58.         ?>
  59.         <!-- 当前页为第一页时，不显示上一页 -->
  60.         <?php if ($page != 1) : ?>
  61.         <a href="<?=$_SERVER['PHP_SELF'].'?p='.$prev?>">上一页</a>
  62.         <?php endif ?>
  65.         <!-- 1. 分页条显示数量 -->
  66.         <!-- 1.1 小于10时 -->
  67.         <?php if ($page <= 10) : ?>
  68.         <?php for($i=1; $i<=10; $i++): ?>
  69.         <!-- 点击分页条时跳转 -->
  70.         <?php $jump = sprintf('%s?p=%s',$_SERVER['PHP_SELF'],$i); ?>
  71.         <!-- 当前页码高亮 -->
  72.         <?php
  73.             // 当前页数和url中的参数p 相等时高亮
  74.             $active = ($i == $page) ? 'active' : null;
  75.         ?>
  76.         <a href="<?=$jump?>" class="<?=$active?>"><?=$i?></a> 
  77.         <?php endfor ?>
  78.         <a>...</a> 
  79.         <!-- 显示最后两页，这里不需要加class，因为后面还会有判断 -->
  80.         <a href="<?=sprintf('%s?p=%s',$_SERVER['PHP_SELF'],$pages-1)?>"><?=$pages-1?></a>
  81.         <a href="<?=sprintf('%s?p=%s',$_SERVER['PHP_SELF'],$pages)?>"><?=$pages?></a>
  82.         <?php endif ?>
  84.         <!-- 1.2 大于10且小于最后10页时 -->
  85.         <?php if ($page > 10 && $page<$pages-10) : ?>
  86.         <!-- 显示1、2 + 左3 + 当前 + 右3 + 最后两页 -->
  87.         <!-- 显示1、2 -->
  88.         <a href="<?=sprintf('%s?p=1',$_SERVER['PHP_SELF'])?>">1</a>
  89.         <a href="<?=sprintf('%s?p=2',$_SERVER['PHP_SELF'])?>">2</a>
  90.         <a>...</a>
  91.         <!-- 显示左3、当前、右3 -->
  92.         <?php for ($i=$page-3;$i<=$page+3;$i++) : ?>
  93.             <?php  $jump = sprintf('%s?p=%s',$_SERVER['PHP_SELF'],$i); ?>
  94.             <?php  $active = ($i == $page) ? 'active' : null; ?>
  95.             <a href="<?=$jump?>" class="<?=$active?>"><?=$i?></a> 
  96.         <?php endfor ?>
  97.         <a>...</a>
  98.         <!-- 显示最后两页 -->
  99.         <a href="<?=sprintf('%s?p=%s',$_SERVER['PHP_SELF'],$pages-1)?>"><?=$pages-1?></a>
  100.         <a href="<?=sprintf('%s?p=%s',$_SERVER['PHP_SELF'],$pages)?>"><?=$pages?></a>
  101.         <?php endif ?>
  103.         <!-- 1.3 大于最后10页时 -->
  104.         <!-- 显示1、2、最后10页 -->
  105.         <?php if ($page>=$pages-10) : ?>
  106.             <!-- 显示1、2 -->
  107.             <a href="<?=sprintf('%s?p=1',$_SERVER['PHP_SELF'])?>">1</a>
  108.             <a href="<?=sprintf('%s?p=2',$_SERVER['PHP_SELF'])?>">2</a>
  109.             <a>...</a>
  110.             <!-- 显示最后10页 -->
  111.             <?php for ($i=$pages-10;$i<=$pages;$i++) :?>
  112.                 <?php  $jump = sprintf('%s?p=%s',$_SERVER['PHP_SELF'],$i); ?>
  113.                 <?php  $active = ($i == $page) ? 'active' : null; ?>
  114.                 <a href="<?=$jump?>" class="<?=$active?>"><?=$i?></a> 
  115.             <?php endfor ?>
  116.         <?php endif ?>
  118.         <!-- 4.尾页 -->
  119.         <a href="<?=$_SERVER['PHP_SELF'].'?p='.$pages?>">尾页</a>
  120.         <!-- 5.下一页 -->
  121.         <?php
  122.             $next = $page + 1;
  123.             // 下一页页数大于等于总页数时，不再后移
  124.             if ($next >= $pages) $next=$pages;
  125.         ?>
  126.         <!-- 当前页是最后一页时，不显示下一页 -->
  127.         <?php if ($page != $pages) : ?>
  128.         <a href="<?=$_SERVER['PHP_SELF'].'?p='.$next?>">下一页</a>
  129.         <?php endif ?>
  130.     </div>
  132.     <script>
  133.         // del()函数：点击确定时跳转到handle.php进行数据删除操作
  134.         function del(id){
  135.             var isDelete = confirm('此操作不可恢复，确定删除吗?')
  136.             if (isDelete) {
  137.                  location.href = 'handle.php?action=delete&id='+id;
  138.             }else{
  139.                 return false;
  140.             }
  141.         }
  142.     </script>
  143. </body>
  144. </html>
  • handle.php
  1. <?php
  2. // 连接数据库
  3. require "connect.php";
  5. $action = $_GET['action'];
  6. $id = $_GET['id'];
  8. switch ($action)
  9. {
  10.     // 点击编辑按钮时
  11.     case 'edit' :
  12.         // 加载渲染表单
  13.         include "edit.php";
  14.     break;
  15.     // 在edit.php编辑表单中，提交新编辑的数据过来时，执行更新操作
  16.     case 'doedit' :
  17.         $sql = "UPDATE `student` SET `username`=?, `sex`=?, `age`=?, `tel`=? WHERE `id`=?";
  18.         $stmt = $pdo->prepare($sql);
  19.         // 新编辑的数据以POST的方式提交过来
  20.         if(!empty($_POST))
  21.             $stmt->execute([$_POST['username'],$_POST['sex'],$_POST['age'],$_POST['tel'],$id]);
  22.             if($stmt->rowCount()==1)
  23.             {
  24.                 echo "
  25.                     <script>
  26.                         alert('更新信息成功');
  27.                         location.href = 'page-list.php';
  28.                     </script>
  29.                 ";
  30.             }
  32.     break;
  33.     // 点击删除按钮并确定时，执行删除操作
  34.     case 'delete' :
  35.         $sql = "DELETE FROM `student` WHERE `id`=?";
  36.         $stmt = $pdo->prepare($sql);
  37.         $stmt->execute([$id]);
  38.         if( $stmt->rowCount() == 1 )
  39.         {
  40.             echo "
  41.                 <script>
  42.                     alert('删除信息成功');
  43.                     location.href = 'page-list.php';
  44.                 </script>
  45.             ";
  46.         }
  47.     break;
  48. }
  • edit.php
  1. <?php
  2. // 通过GET传过来的id，将用户信息取到
  3. $sql = "SELECT * FROM `student` WHERE `id`={$id}"; 
  4. $user = $pdo->query($sql)->fetch();
  5. ?>
  8. <!DOCTYPE html>
  9. <html lang="en">
  10. <head>
  11.     <meta charset="UTF-8">
  12.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  13.     <link rel="stylesheet" href="edit.css">
  14.     <title>用户编辑</title>
  15. </head>
  16. <body>
  17.     <h2>用户信息编辑</h2>
  18.     <table>
  19.         <caption><h3>原始信息如下：<h3></caption>
  20.         <thead>
  21.             <tr>
  22.                 <td>id</td>
  23.                 <td>用户名</td>
  24.                 <td>性别</td>
  25.                 <td>年龄</td>
  26.                 <td>联系电话</td>
  27.             </tr>
  28.         </thead>
  29.         <tbody>
  30.             <tr>
  31.                 <td><?=$user['id']?></td>
  32.                 <td><?=$user['username']?></td>
  33.                 <td><?=$user['sex']?></td>
  34.                 <td><?=$user['age']?></td>
  35.                 <td><?=$user['tel']?></td>
  36.             </tr>
  37.         </tbody>
  38.     </table>
  39.     <hr>
  40.     <h3>请编辑您的要修改的信息：</h3>
  41.     <form action="<?=$_SERVER['PHP_SELF'].'?action=doedit&id='.$id?>" method="POST">
  42.         <div>
  43.             <label for="username">用户名：</label>
  44.             <input type="text" name="username" id="username">
  45.         </div>
  46.         <div>
  47.             <label for="male">性别：</label>
  48.             <div>   
  49.                 <input type="radio" name="sex" id="male" value="男">男
  50.                 <input type="radio" name="sex" id="female" value="女">女
  51.             </div>
  52.         </div>
  53.         <div>
  54.             <label for="age">年龄：</label>
  55.             <input type="text" name="age" id="age">
  56.         </div>
  57.         <div>
  58.             <label for="tel">电话：</label>
  59.             <input type="tel" name="tel" id="tel">
  60.         </div>
  61.         <button>保存</button>
  62.     </form>
  63. </body>
  64. </html>
  • style.css
  1. * {
  2.   margin: 0;
  3.   padding: 0;
  4.   box-sizing: border-box;
  5. }
  6. /* 表格样式 */
  7. table {
  8.   border: 1px solid black;
  9.   border-collapse: collapse;
  10.   text-align: center;
  11.   width: 800px;
  12.   margin: auto;
  13. }
  14. th,
  15. td {
  16.   border: 1px solid black;
  17.   line-height: 50px;
  18. }
  19. table h2 {
  20.   padding: 10px 0;
  21. }
  22. th {
  23.   background-color: lightcoral;
  24. }
  25. tr:hover {
  26.   background-color: lightcyan;
  27. }
  28. button {
  29.   padding: 5px 15px;
  30.   background-color: rgb(9, 9, 71);
  31.   color: white;
  32.   border-radius: 5px;
  33.   outline: none;
  34.   border: none;
  35. }
  36. button:hover {
  37.   background-color: rgb(77, 77, 163);
  38.   cursor: pointer;
  39. }
  40. /* 分页条样式 */
  41. div {
  42.   text-align: center;
  43.   margin-top: 30px;
  44. }
  45. div > a {
  46.   text-decoration: none;
  47.   color: black;
  48.   padding: 5px 10px;
  49.   vertical-align: middle;
  50.   margin: 0 5px;
  51.   border-radius: 5px;
  52. }
  53. /* 当前页码高亮 */
  54. .active {
  55.   background-color: red;
  56.   color: white;
  57. }
  • edit.css
  1. * {
  2.   margin: 0;
  3.   padding: 0;
  4.   box-sizing: border-box;
  5. }
  6. body {
  7.   display: flex;
  8.   flex-flow: column nowrap;
  9.   align-items: center;
  10. }
  11. h2 {
  12.   padding: 20px 0;
  13.   border-bottom: 1px solid black;
  14. }
  15. table {
  16.   border: 1px solid black;
  17.   border-collapse: collapse;
  18.   width: 500px;
  19. }
  20. table td {
  21.   border: 1px solid black;
  22.   line-height: 50px;
  23.   text-align: center;
  24. }
  25. table > caption > h3 {
  26.   padding: 10px 0;
  27. }
  29. form > div {
  30.   display: grid;
  31.   grid-template-columns: 80px 300px;
  32.   grid-template-rows: 30px;
  33.   margin: 10px 0;
  34. }
  35. body > h3 {
  36.   padding-bottom: 10px;
  37.   margin-top: 50px;
  38. }
  39. form {
  40.   text-align: center;
  41. }
  42. form > div:nth-of-type(2) input {
  43.   margin: 0 20px;
  44. }
  45. button {
  46.   width: 100px;
  47.   padding: 5px 0;
  48.   outline: none;
  49. }
  50. button:hover {
  51.   cursor: pointer;
  52.   background-color: black;
  53.   color: white;
  54. }

2. 分页效果如下：

3 总结

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
2 comments
果冻 2020-08-05 14:06:36
老周威武！
2 floor
老周 2020-08-04 22:15:56
你是来踢馆的，鉴定完毕~~~~
1 floor
Author's latest blog post