php分页功能案例

php分页功能案例

1. 基础

• 分页关键的概念就是：索引，偏移量，单页显示数量
• 偏移量计算公式：OFFSET = 每页显示的数量-（当前页数-1）

2. 实现分页条中的直接页码跳转功能

• 第一步：连接数据库

<?php
//使用pdo的方式
$dsn = 'mysql:host=localhost;dbname=php;port=3306;charset=utf8';
$username = 'root';
$password = 'root123456';
try {
    $pdo = new PDO($dsn, $username, $password);
    // 结果集获取方式
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch (Exception $e) {
    die($e->getMessage());
}

• 第二步，获取需要展示的内容

<?php
//1，连接数据库
require_once 'connect.php';
//2，当前查看的页数/页码
$page = $_GET['p'] ?? 1;
//3，每页显示的记录数量
$num = 5;
//4,总页数
// 4.1首先获取一共有多少条记录数
$sql = "SELECT CEIL(COUNT('id')/{$num}) AS `total`  FROM `staffs`";
// echo $sql;
$pages = $pdo->query($sql)->fetch()['total'];
//5，偏移量
$offset = $num * ($page - 1);
//6.分页数据
$sql = "SELECT * FROM `staffs` LIMIT {$num} OFFSET {$offset}";
$staffs = $pdo->query($sql)->fetchAll();
// print_r($staffs);

• 第三步，前端表格 html 代码以及 css 样式

<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 date('Y/m/d', $staff['hiredate']);   ?></td>
      <td><button>编辑</button><button>删除</button></td>
    </tr>
    <?php endforeach; ?>
  </tbody>
</table>

* {
  margin: 0;
  padding: 0;
  box-sizing: border-box;
  color: #555;
}
body {
  display: flex;
  flex-flow: column nowrap;
  align-items: center;
}
table {
  width: 80%;
  border: 1px solid;
  border-collapse: collapse;
  text-align: center;
}
table td,
table th {
  border: 1px solid;
  padding: 5px;
}
table caption {
  font-size: 1.5rem;
  margin: 10px;
}
table thead tr:only-of-type {
  background-color: lightblue;
}
table tr:hover {
  background-color: #eee;
}
table button {
  width: 56px;
  height: 26px;
  cursor: pointer;
  margin: 0 3px;
}
table button:last-of-type {
  color: red;
}
body > p {
  display: flex;
}
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;
}

• 第四步，简单分页条的实现

<p>
        <?php
        $prev = $page - 1;
        if ($page == 1) $prev = 1;
        ?>
        <a href="<?php echo $_SERVER['PHP_SELF'] . '?p=1' ?> ">首页</a>
        <a href="<?php echo $_SERVER['PHP_SELF'] . '?p=' . $prev ?> ">前一页</a>
        <?php for ($i = 1; $i <= $pages; $i++) : ?>
        <?php
            $jump = sprintf('%s?p=%s', $_SERVER['PHP_SELF'], $i);
            // echo $jump;
            $active = ($i == $page) ? 'active' : null;
            ?>
        <a href="<?php echo $jump ?>" class="<?php echo $active; ?>"><?php echo $i; ?></a>
        <?php endfor;  ?>
        <?php
        $next = $page + 1;
        if ($page == $pages) $next = $pages;
        ?>
        <a href="<?php echo $_SERVER['PHP_SELF'] . '?p=' . $next ?> ">下一页</a>
        <a href="<?php echo $_SERVER['PHP_SELF'] . '?p=' . $pages ?> ">尾页</a>
    </p>

3. 实现分页条中的页码省略功能

    <p>
        <?php
        //显示的页码
        $showPages = 5;
        // 起始页
        $startPage = 1;
        // 终止页码
        $endPage = $pages;
        // 偏移量
        $offsetPage = ($showPages - 1) / 2; //2
        if ($showPages < $pages) {
            if ($page > $offsetPage + 1) {
                $startOmit = '...';
            }
            if ($page > $offsetPage) {
                $startPage = $page - $offsetPage;
                $endPage = $page + $offsetPage;
                if ($endPage > $pages) {
                    $endPage = $pages;
                }
            } else {
                $startPage = 1;
                $endPage = $showPages;
            }
            if ($page + $offsetPage > $pages) {
                $startPage = $startPage - ($page + $offsetPage - $endPage);
            }
            if ($showPages < $pages && $page + $offsetPage < $pages) {
                $endOmit = '...';
            }
        }
        ?>
       <?php
        $prev = $page - 1;
        if ($page == 1) $prev = 1;
        ?>
        <a href="<?php echo $_SERVER['PHP_SELF'] . '?p=1' ?> ">首页</a>
        <a href="<?php echo $_SERVER['PHP_SELF'] . '?p=' . $prev ?> ">前一页</a>
        <?php
        if (isset($startOmit)) { ?>
        <a href=""><?php echo $startOmit;  ?></a>
        <?php } ?>
        <?php for ($i = $startPage; $i <= $endPage; $i++) : ?>
        <?php
            $jump = sprintf('%s?p=%s', $_SERVER['PHP_SELF'], $i);
            // echo $jump;
            $active = ($i == $page) ? '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 } ?>
        <?php
        $next = $page + 1;
        if ($page == $pages) $next = $pages;
        ?>
        <a href="<?php echo $_SERVER['PHP_SELF'] . '?p=' . $next ?> ">下一页</a>
        <a href="<?php echo $_SERVER['PHP_SELF'] . '?p=' . $pages ?> ">尾页</a>
    </p>

4. 实现编辑和删除功能

• 前端按钮

<td>
<button onclick="location.href='handle.php?action=edit&id=<?php echo $staff['id'];   ?>'">编辑</button>
<button onclick="location.href='handle.php?action=del&id=<?php echo $staff['id'];   ?>'">删除</button>
</td>

• 处理脚本

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

• 编辑脚本

<?php
//获取要被编辑的员工信息
$staff = $pdo->query("SELECT * FROM `staffs` WHERE `id`={$id}")->fetch();
// print_r($staff);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>编辑</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>
        <input type="hidden" name="id" value="<?php echo $staff['id'] ?>">
        <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 date('Y-m-d', $staff['hiredate']) ?>">
        </p>
        <p>
            <button>保存</button>
        </p>
    </form>
</body>
</html>

• 删除脚本

<?php
$sql = "DELETE FROM `staffs` WHERE `id`={$id}";
$stmt = $pdo->prepare($sql);
$stmt->execute();
if ($stmt->rowCount() === 1) echo '<script>alert("删除成功");location.href="fenye.php";</script>';

5. 总结

本次学习分页功能，让我基本熟悉了 php 如何与 html 结合起来开发项目的认识，非常的巧妙和灵活。分页显示,将从 mysql 数据库取出的数据有规律的一段段展示,利用 sql 语句中的 limit,将它的起始数据跟页数绑定,根据页数去数据库取数据