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.显示页面
<?phprequire '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.处理脚本
<?phprequire '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;}
总结：分页难点在于那个省略号的实现，针对不同的情况要考虑将起始页码和终止页码进行重置。
