PHP实现数据分页

1.数据库连接文件

<?php
$dsn = "mysql:host=localhost;dbname=phpedu;charset=utf8;port=3306";
$username = 'root';
$password = 'root';
try {
    $pdo = new PDO($dsn, $username, $password);
} catch (Exception $e) {
    die($e->getMessage());
}

2.显示页面

<?php
require 'connect.php';
//1.获取当前页码
$page = $_GET['p'] ?? 1;
//2.每页显示的记录数量
$num = 5;
//3.获取记录总数
$sql = "select count(`id`) as `total` from `staffs`";
$total = $pdo->query($sql)->fetch()['total'];
//4.计算总页数
$pages = ceil($total / $num);
//5.偏移量
$offset = $num * ($page - 1);
//6.获取显示的数据
$sql = "select * from `staffs` limit {$num} offset {$offset}";
$staffs = $pdo->query($sql)->fetchAll();
// var_dump($staffs);
// die;
?>
<!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 foreach ($staffs as $staff) : ?>
                <tr>
                    <td><?php echo $staff['id'] ?></td>
                    <td><?php echo $staff['name'] ?></td>
                    <td><?php echo $staff['age'] ?></td>
                    <td><?php echo $staff['sex'] ? '男' : '女' ?></td>
                    <td><?php echo $staff['position'] ?></td>
                    <td><?php echo $staff['mobile'] ?></td>
                    <td><?php echo $staff['hiredate']  ?></td>
                    <td>
                        <button onclick="location.href='handle.php?action=edit&id=<?php echo $staff['id'] ?>'">编辑</button>
                        <button onclick="del(<?php echo $staff['id'] ?>)">删除</button>
                    </td>
                </tr>
            <? endforeach; ?>
        </tbody>
    </table>
    <hr>
    <hr>
    <hr>
    <hr>
    <!-- 添加跳转到首页, 前一页, 下一页, 尾页的功能 -->
    <p>
        <?php
        //1.设置分页条显示的页码个数
        $showPage = 5;
        //2.设置分页条的起始页
        $startPage = 1;
        //3.设置分页条的终止页
        $endPage = $pages;
        //4.计算偏移量
        $offsetPage = ($showPage - 1) / 2;
        if ($showPage < $pages) {
            if ($page > $offsetPage + 1) {
                $startOmit = '...';
                $startPage = $page - $offsetPage;
            }
            if ($page + $offsetPage < $pages) {
                $endOmit = '...';
                $endPage = $page + $offsetPage;
            }
            //其他情况下要将起始终止页码进行重置
            if ($page < $offsetPage + 1) {
                $endPage = $showPage;
            }
            if ($page + $offsetPage > $pages) {
                $startPage = $pages - $showPage + 1;
            }
        }
        ?>
        <!-- 首页 -->
        <a href="<?php echo $_SERVER['PHP_SELF'] . '?p=1'  ?>">首页</a>
        <!-- 上一页 -->
        <?php if ($page - 1 >= 1) $pre = $page - 1;
        else $pre = 1  ?>
        <a href="<?php echo $_SERVER['PHP_SELF'] . '?p=' . $pre  ?>">上一页</a>
        <!-- 显示左边的省略号 -->
        <?php if (isset($startOmit)) :  ?>
            <a href="#"><?php echo $startOmit ?></a>
        <?php endif; ?>
        <?php for ($i = $startPage; $i <= $endPage; $i++) : ?>
            <?php
            $jump = sprintf("%s?p=%s", $_SERVER['PHP_SELF'], $i);
            $active = ($page == $i) ? 'active' : null;
            ?>
            <a href="<?php echo $jump ?>" class="<?php echo $active ?>"><?php echo $i ?></a>
        <?php endfor; ?>
        <!-- 显示左边的省略号 -->
        <?php if (isset($endOmit)) : ?>
            <a href="#"><?php echo $endOmit; ?></a>
        <?php endif; ?>
        <!-- 下一页 -->
        <?php if ($page + 1 <=  $pages) $next = $page + 1;
        else $next = $pages  ?>
        <a href="<?php echo $_SERVER['PHP_SELF'] . '?p=' . $next ?>">下一页</a>
        <!-- 尾页 -->
        <a href="<?php echo $_SERVER['PHP_SELF'] . '?p=' . $pages ?>">尾页</a>
    </p>
    <script>
        function del(id) {
            return confirm('是否删除?') ? location.href = "handle.php?action=del&id=" + id : false;
        }
    </script>
</body>
</html>

3.处理脚本

<?php
require 'connect.php';
$action = $_GET['action'];
$id = $_GET['id'];
switch ($action) {
    case 'edit':
        require 'edit.php';
        break;
    case 'doedit':
        $data = $_POST;
        $sql = 'UPDATE `staffs` SET `name`=:name, `age`=:age,`sex`=:sex,`position`=:position ,`mobile`=:mobile,`hiredate`=:hiredate WHERE `id`=:id';
        $stmt = $pdo->prepare($sql);
        $stmt->execute($data);
        if ($stmt->rowCount() === 1) echo '<script>alert("更新成功");location.href="demo1.php";</script>';
        else print_r($pdo->errorInfo());
        break;
    case 'del':
        var_dump($id);
        $sql = "delete from `staffs` where id={$id}";
        $stmt = $pdo->prepare($sql);
        $stmt->execute();
        if ($stmt->rowCount() === 1) {
            echo '<script>alert("删除成功");location.href="demo1.php";</script>';
        } else {
            echo '<script>alert("删除删除");location.href="demo1.php";</script>';
        }
}

4.处理编辑信息显示

<?php
$staff = $pdo->query("SELECT * FROM `staffs` where `id` = {$id}")->fetch();
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <h3>编辑员工信息</h3>
    <form action="<?php echo $_SERVER['PHP_SELF'] . '?action=doedit&id=' . $id ?>" method="post">
        <p>
            <label for="name">姓名</label>
            <input type="text" id="name" name="name" value="<?php echo $staff['name'] ?>">
        </p>
        <p>
            <label for="age">年龄:</label>
            <input type="number" id="age" name="age" value="<?php echo $staff['age'] ?>">
        </p>
        <p>
            <label for="sex">性别:</label>
            <input type="radio" id="sex" name="sex" value="1" <?php if ($staff['sex'] == 1) echo 'checked' ?>><label for="">男</label>
            <input type="radio" id="sex" name="sex" value="0" <?php if ($staff['sex'] == 0) echo 'checked' ?>><label for="">女</label>
        </p>
        <p>
            <label for="position">职位:</label>
            <input type="text" id="position" name="position" value="<?php echo $staff['position'] ?>">
        </p>
        <p>
            <label for="mobile">手机号:</label>
            <input type="text" id="tel" name="mobile" value="<?php echo $staff['mobile'] ?>">
        </p>
        <p>
            <label for="hiredate">入职时间:</label>
            <input type="text" id="hiredate" name="hiredate" value="<?php echo $staff['hiredate'] ?>">
        </p>
        <input type="hidden" name="id" value="<?php echo $staff['id'] ?>">
        <p>
            <button>保存</button>
        </p>
    </form>
</body>
</html>

5.css文件

* {
  margin: 0;
  padding: 0;
  box-sizing: border-box;
  color: #555;
}
body {
  display: flex;
  flex-direction: column;
  align-items: center;
}
/* 表格样式 */
table {
  width: 80%;
  border: 1px solid;
  border-collapse: collapse;
  text-align: center;
}
table caption {
  font-size: 1.2rem;
  margin: 10px;
}
table td,
table th {
  border: 1px solid;
  padding: 5px;
}
table tr:hover {
  background-color: #eee;
}
table thead tr:only-of-type {
  background-color: lightblue;
}
table button {
  width: 56px;
  height: 26px;
}
table button:last-of-type {
  color: red;
}
table button {
  cursor: pointer;
  margin: 0 3px;
}
/* 分页样式 */
p > a {
  text-decoration: none;
  color: #555;
  border: 1px solid;
  padding: 5px 10px;
  margin: 10px 2px;
}
.active {
  background-color: red;
  color: white;
  border: 1px solid red;
}