分页原理

1. 术语

记录索引:
偏移量:
显示数量

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

演示代码

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

index.php

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>员工管理系统</title>
    <link rel="stylesheet" href="style.css" />
</head>
<body>
    <table>
        <caption>
            员工管理系统
        </caption>
        <thead>
            <tr>
                <th>ID</th>
                <th>姓名</th>
                <th>年龄</th>
                <th>性别</th>
                <th>职位</th>
                <th>手机</th>
                <th>入职时间</th>
                <th>操作</th>
            </tr>
        </thead>
        <tbody>
            <?php
require 'demo1.php';
foreach ($staffs as $staff){
//    数组转名值对
    extract($staff);
    /* @var string $id;
     * @var string $name;
     * @var string $age;
     * @var string $sex;
     * @var string $position;
     * @var string $mobile;
     * @var string $hiredate;
     * */
    $sex  = $sex ? '男' : '女';
    $hiredate = date('Y / m / d',$hiredate);
    echo <<<AAA
        <tr>
          <td>{$id}</td>
          <td>{$name}</td>
          <td>{$age}</td>
          <td>{$sex}</td>
          <td>{$position}</td>
          <td>{$mobile}</td>
          <td>{$hiredate}</td>
          <td><a href="update.php?p={$page}&update_id={$id}">编辑</a><a href="delect.php?p={$page}&delect_id={$id}">删除</a></td>
        </tr>
AAA;
}
//删除数据
?>
        </tbody>
    </table>
    <div>
        <?php
//    判断是不是第一页
    if ($page > 1){
        echo "<a href='?p=1' class='headtail'>首页</a>";
            $start = $page-1;
        echo "<a href='?p={$start}' class='around'>上一页</a>";
    }
//创建输出方法
    function Output($i){
        global $page;
        $jump = sprintf('?p=%s',$i);
        $active = ($i==$page)?'active':null;
        echo "<a href='{$jump}' class='{$active}'>{$i}</a>";
    }
//    输出前两条
    for ($i=1;$i<=2;$i++){
        Output($i);
    }
//    判断输出 ...
    if ($page > 5){
      echo "<span > ... </span>";
    }
//    输出前后页数
//    当前页小于五输出1234567
    if ($page <= 5 ){
        for ($i=3;$i<=7;$i++){
            Output($i);
        }
    }
//    当前页大于5和小于总页数-4 输出当前页前2加厚2
    if ($page >5 && $page < $pages-4){
        for ($i=($page-2);$i<=$page+2;$i++){
            Output($i);
        }
    }
//    当前页大于总页数-4 输出后面全部页数
    if ($page>=$pages-4){
        for ($i=$pages-4;$i<=$pages;$i++){
            Output($i);
        }
    }
// 判断输出
if ($page<$pages-4){
    echo "<span > ... </span>";
}
//尾部
    if ($page<=$pages-5 ) {
        for ($i = ($pages - 1); $i <= $pages; $i++) {
            Output($i);
        }
    }
    if ($page < $pages ){
        $next = $page+1;
        echo "<a href='?p={$next}' class='around'>下一页</a>";
        echo "<a href='?p={$pages}'class='headtail'>尾页</a>";
    }
    if ($pages > 10 ){
        echo <<<bbb
    <form class="form1" action="" method="get">
        <input type="number" name="p"min="1" max=" {$pages}" required />
        <button>跳转</button>
    </form>
bbb;
    }
    ?></div>
</body>
</html>

demo1.php

<?php
//连接数据库
require 'connect.php';
////非法请求
$err = "<script>alert('非法请求');location.href='index.php';</script>";
//当前页码
$page = $_GET['p'] ?? 1 ;
if ($page<=0){
    die($err);
}
//每页记录数量
$num = 12;
//获取总页数
$sql = "SELECT CEIL(COUNT(`id`)/{$num}) AS `total` FROM `staffs`";
$pages = $pdo->query($sql)->fetch()['total'];
if ($page>$pages){
    die($err);
}
//偏移量
$offset = $num*($page-1);
//分页的数据
$sql = "SELECT * FROM `staffs` LIMIT {$num} OFFSET {$offset}";
$staffs = $pdo->query($sql)->fetchAll();

connect.php

<?php
$dsn = 'mysql:host=localhost;dbname=php11.edu';
$username = 'php11.edu';
$password = 'php11.edu';
try {
    $pdo = new PDO($dsn,$username,$password);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
}catch (Exception $e){
    die($e->getMessage());
}

delect.php

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

update.php

<?php
//更新修改数据库
//链接数据库
require 'connect.php';
//获取id
$update_id = $_GET['update_id'];
//获取页数
$page = $_GET['p'];
//先查询到信息显示
$sql = "SELECT * FROM `staffs` WHERE `id`={$update_id}";
$user = $pdo->query($sql)->fetch(PDO::FETCH_ASSOC);
extract($user);
/**
 * @var string $id;
 * @var string $name;
 * @var string $age;
 * @var string $sex;
 * @var string $position;
 * @var string $mobile;
 * @var string $hiredate;
 **/
$sex = $sex ? '男': '女';
$date  = date('Y / m / d' ,$hiredate);
echo <<< aa
<style>
@import "style.css";
</style>
    <form class="update" action="update1.php" method="post">
      <div>
        <label for="id">ID:</label>
        <input type="text" id="id"  disabled value="{$id}" />
      </div>
      <div>
        <label for="name">姓名:</label>
        <input type="text" id="name" name="name" required value="{$name}" />
      </div>
      <div>
        <label for="age">年龄:</label>
        <input type="number" max="99" min="18" id="age" name="age" required value="{$age}" />
      </div>
      <div>
        <label for="sex">性别:</label>
        <input type="text" id="sex" disabled value="{$sex}" />
      </div>
      <div>
        <label for="position">职位:</label>
        <input type="text" id="position" name="position" required value="{$position}" />
      </div>
      <div>
        <label for="mobile">手机号:</label>
        <input
          type="text"
          id="mobile"
          name="mobile"
          required
          minlength="11"
          maxlength="11"
          value="{$mobile}"
        />
      </div>
      <div>
        <label for="hiredate">入职时间:</label>
        <input type="text" id="hiredate" disabled value="{$date}" />
      </div>
      <input type="hidden" name="p" value="{$page}">
      <button name="id" value="{$id}">提交</button>
    </form>
aa;

update1.php

<?php
require 'connect.php';
//获取值
$page = $_POST['p'];
$id = $_POST['id'];
$name = $_POST['name'];
$age = $_POST['age'];
$position = $_POST['position'];
$mobile = $_POST['mobile'];
//执行更新
$sql = "UPDATE `staffs` SET `name` = '{$name}' ,`age` = '{$age}' , `position` = '{$position}',`mobile` = '{$mobile}' WHERE `id` = {$id}";
$stmt = $pdo->prepare($sql);
$stmt->execute();
//判断是否执行成功
if ($stmt->rowCount()===1){
    echo "<script>alert('修改成功');location.href='index.php?p={$page}'</script>";
}else{
    if ($stmt->errorInfo()[0] == 00000){
        echo "<script>alert('没有值被修改');location.href='index.php?p={$page}'</script>";
    }else{
        echo "<script>alert('修改失败');location.href='index.php?p={$page}'</script>";
    }
}

style.css

* {
  margin: 0px;
  padding: 0px;
  box-sizing: border-box;
  color: #555;
}
body {
  display: flex;
  flex-direction: column;
  align-items: center;
}
table {
  width: 1000px;
  border: 1px solid;
  text-align: center;
  border-collapse: collapse;
}
table > caption {
  font-size: 1.2rem;
  margin: 10px;
}
table td,
table th {
  border: 1px solid;
  padding: 5px;
}
table tr:hover {
  background-color: #eee;
}
div {
  display: flex;
  flex-flow: row nowrap;
  align-items: center;
}
div > a {
  padding: 5px;
  border: 1px solid;
  margin: 10px;
  width: 30px;
  height: 30px;
  text-decoration: none;
  text-align: center;
}
div > a:hover {
  background-color: violet;
  color: #fff;
}
.headtail {
  width: 50px;
}
.around {
  width: 80px;
}
.active {
  background-color: lightskyblue;
  color: linen;
}
.form1 > input {
  width: 50px;
  height: 30px;
}
.form1 > input:out-of-range,
.form1 > input::-webkit-inner-spin-button {
  appearance: none;
}
.update {
  display: flex;
  flex-flow: column;
  margin: 30px auto;
}
.update > div {
  width: 220px;
  margin-bottom: 10px;
  display: flex;
  justify-content: space-between;
}
tbody a {
  text-decoration: none;
  margin: 0px 10px;
  background-color: lightskyblue;
  padding: 3px 10px;
  color: #fff;
  border: none;
  border-radius: 5px;
}
tbody a:hover {
  background-color: aqua;
}