一.分页的基本原理
分析分页的原理:
* 1. LIMIT 参数的作用: 偏移量与显示数量
* 2. 如果控制每页显示的数量
* 3. 接收GET参数,用p表示当前页数,每页显示3条
* 4. 需要的参数:
* (1).totalPage 总页数
* (2).totalNumber 一共有多少条数据
* (3).pageSize 每页显示多少条数据
* (4)currentPage 当前第几页
* (5)*.rangeStart 起始页
* (6)*.rangeEnd 末页
* 5. 当前偏移量的计算公式: (页数-1)*每页显示的数量
* offset = (currentPage-1)*pageSize
二.分页的简单实现
实例
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>手工分页查询</title>
<style>
table,th,td {
border: 1px solid black;
}
table th {
background-color: lightskyblue;
}
table {
border-collapse: collapse;
width: 60%;
margin: 30px auto;
text-align: center;
}
table caption {
font-size: 1.5rem;
margin-bottom: 15px;
}
</style>
</head>
<body>
<?php
//连接数据库获取到全部的记录
$pdo = new PDO('mysql:host=localhost;dbname=phpcn','root','root');
//$sql = "SELECT * FROM staff LIMIT 5, 5;";
//手工修改url中的get参数可以实现翻页查询
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($page-1)*5;
$sql = "SELECT * FROM `staff` LIMIT {$offset}, 5;";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
//查看返回结果
// echo '<pre>'.print_r($rows,true).'</pre>';
?>
<table>
<caption>员工信息表</caption>
<tr>
<th>ID</th>
<th>姓名</th>
<th>年龄</th>
<th>性别</th>
<th>工资</th>
</tr>
<?php foreach ($rows as $row): ?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['age']; ?></td>
<td><?php echo $row['sex']?'女':'男'; ?></td>
<td><?php echo $row['salary']; ?></td>
</tr>
<?php endforeach;?>
</table>
</body>
</html>运行实例 »
点击 "运行实例" 按钮查看在线实例
本地测试访问的地址: http://www.phpcntest.com/test.php?page=3
三,分页中上一页.下一页,首页,尾页
实例
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>上一页和下一页的实现原理</title>
<style>
table,th,td {
border: 1px solid black;
}
table th {
background-color: lightskyblue;
}
table {
border-collapse: collapse;
width: 60%;
margin: 30px auto;
text-align: center;
}
table caption {
font-size: 1.5rem;
margin-bottom: 15px;
}
h3 {
text-align: center;
}
h3 a {
text-decoration: none;
margin-left: 10px;
border: 1px solid black;
display: inline-block;
height: 30px;
min-width: 30px;
padding: 0 10px;
background-color: lightgreen;
}
h3 a:hover{
background-color: red;
color: white;
}
</style>
</head>
<body>
<?php
//连接数据库获取到全部的记录
$pdo = new PDO('mysql:host=localhost;dbname=phpcn','root','root');
//$sql = "SELECT * FROM staff LIMIT 5, 5;";
//手工修改url中的get参数可以实现翻页查询
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($page-1)*5;
$sql = "SELECT * FROM `staff` LIMIT {$offset}, 5;";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
//查看返回结果
// echo '<pre>'.print_r($rows,true).'</pre>';
/**
* 获取总页数分2步:
* 1.获取总记录数
* 2.再除以每次的显示数量,结果向上取整
*/
$stmt = $pdo->prepare("SELECT COUNT(*) FROM `staff`");
$stmt->execute();
$total = $stmt->fetchColumn(0);
$pages = ceil($total / 5); //获取到总页数 $pages,这每页显示5条,写死了,不建议
?>
<table>
<caption>员工信息表</caption>
<tr>
<th>ID</th>
<th>姓名</th>
<th>年龄</th>
<th>性别</th>
<th>工资</th>
</tr>
<?php foreach ($rows as $row): ?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['age']; ?></td>
<td><?php echo $row['sex']?'男':'女'; ?></td>
<td><?php echo $row['salary']; ?></td>
</tr>
<?php endforeach;?>
</table>
<h3>
<a href="http://www.phpcntest.com/test.php?page=1">首页</a>
<a href="http://www.phpcntest.com/test.php?page=
<?php
// echo $page-1;//发现到第一页就挂掉了,怎么办?必须对$page=0进行控制
echo (($page-1)==0)? 1 : ($page-1);//默认第一页
?>">上一页</a>
<a href="http://www.phpcntest.com/test.php?page=
<?php
//发现越界了,怎么办?最大页不能超过总页数才可以.
echo (($page+1)>$pages)?$pages:($page+1);
?>">下一页</a>
<a href="http://www.phpcntest.com/test.php?page=<?php echo $pages; ?>">尾页</a>
</h3>
</body>
</html>运行实例 »
点击 "运行实例" 按钮查看在线实例
四.以上基础添加中间页的分页实现
实例
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>上一页和下一页的实现原理</title>
<style>
table,th,td {
border: 1px solid black;
}
table th {
background-color: lightskyblue;
}
table {
border-collapse: collapse;
width: 60%;
margin: 30px auto;
text-align: center;
}
table caption {
font-size: 1.5rem;
margin-bottom: 15px;
}
h3 {
text-align: center;
}
h3 a {
text-decoration: none;
margin-left: 10px;
border: 1px solid black;
display: inline-block;
height: 30px;
min-width: 30px;
padding: 0 10px;
background-color: lightgreen;
}
h3 a:hover{
background-color: red;
color: white;
}
</style>
</head>
<body>
<?php
//连接数据库获取到全部的记录
$pdo = new PDO('mysql:host=localhost;dbname=phpcn','root','root');
//$sql = "SELECT * FROM staff LIMIT 5, 5;";
//手工修改url中的get参数可以实现翻页查询
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($page-1)*5;
$sql = "SELECT * FROM `staff` LIMIT {$offset}, 5;";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
//查看返回结果
// echo '<pre>'.print_r($rows,true).'</pre>';
/**
* 获取总页数分2步:
* 1.获取总记录数
* 2.再除以每次的显示数量,结果向上取整
*/
$stmt = $pdo->prepare("SELECT COUNT(*) FROM `staff`");
$stmt->execute();
$total = $stmt->fetchColumn(0);
$pages = ceil($total / 5); //获取到总页数 $pages,这每页显示5条,写死了,不建议
?>
<table>
<caption>员工信息表</caption>
<tr>
<th>ID</th>
<th>姓名</th>
<th>年龄</th>
<th>性别</th>
<th>工资</th>
</tr>
<?php foreach ($rows as $row): ?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['age']; ?></td>
<td><?php echo $row['sex']?'男':'女'; ?></td>
<td><?php echo $row['salary']; ?></td>
</tr>
<?php endforeach;?>
</table>
<h3>
<a href="http://www.phpcntest.com/test.php?page=1">首页</a>
<a href="http://www.phpcntest.com/test.php?page=
<?php
// echo $page-1;//发现到第一页就挂掉了,怎么办?必须对$page=0进行控制
echo (($page-1)==0)? 1 : ($page-1);//默认第一页
?>">上一页</a>
<!--生成中间页码-->
<!--将当前页码的背景色锁定:当前页码等于GET中的参数p-->
<?php for($i=1; $i<=$pages; $i++): ?>
<a
href="http://www.phpcntest.com/test.php?page=<?php echo $i ?>"
<?php
echo ($i == $page) ? 'style="background-color: red;"' : '';
?>
>
<?php echo $i ?>
</a>
<?php endfor; ?>
<a href="http://www.phpcntest.com/test.php?page=
<?php
//发现越界了,怎么办?最大页不能超过总页数才可以.
echo (($page+1)>$pages)?$pages:($page+1);
?>">下一页</a>
<a href="http://www.phpcntest.com/test.php?page=<?php echo $pages; ?>">尾页</a>
</h3>
</body>
</html>运行实例 »
点击 "运行实例" 按钮查看在线实例
五.跳转到指定的页
实例
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>上一页和下一页的实现原理</title>
<style>
table,th,td {
border: 1px solid black;
}
table th {
background-color: lightskyblue;
}
table {
border-collapse: collapse;
width: 60%;
margin: 30px auto;
text-align: center;
}
table caption {
font-size: 1.5rem;
margin-bottom: 15px;
}
h3 {
text-align: center;
}
h3 a {
text-decoration: none;
margin-left: 10px;
border: 1px solid black;
display: inline-block;
height: 30px;
min-width: 30px;
padding: 0 10px;
background-color: lightgreen;
}
h3 a:hover{
background-color: red;
color: white;
}
form {
display: inline;
}
</style>
</head>
<body>
<?php
//连接数据库获取到全部的记录
$pdo = new PDO('mysql:host=localhost;dbname=phpcn','root','root');
//$sql = "SELECT * FROM staff LIMIT 5, 5;";
//手工修改url中的get参数可以实现翻页查询
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($page-1)*5;
$sql = "SELECT * FROM `staff` LIMIT {$offset}, 5;";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
//查看返回结果
// echo '<pre>'.print_r($rows,true).'</pre>';
/**
* 获取总页数分2步:
* 1.获取总记录数
* 2.再除以每次的显示数量,结果向上取整
*/
$stmt = $pdo->prepare("SELECT COUNT(*) FROM `staff`");
$stmt->execute();
$total = $stmt->fetchColumn(0);
$pages = ceil($total / 5); //获取到总页数 $pages,这每页显示5条,写死了,不建议
?>
<table>
<caption>员工信息表</caption>
<tr>
<th>ID</th>
<th>姓名</th>
<th>年龄</th>
<th>性别</th>
<th>工资</th>
</tr>
<?php foreach ($rows as $row): ?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['age']; ?></td>
<td><?php echo $row['sex']?'男':'女'; ?></td>
<td><?php echo $row['salary']; ?></td>
</tr>
<?php endforeach;?>
</table>
<h3>
<a href="http://www.phpcntest.com/test.php?page=1">首页</a>
<a href="http://www.phpcntest.com/test.php?page=
<?php
// echo $page-1;//发现到第一页就挂掉了,怎么办?必须对$page=0进行控制
echo (($page-1)==0)? 1 : ($page-1);//默认第一页
?>">上一页</a>
<!--生成中间页码-->
<!--将当前页码的背景色锁定:当前页码等于GET中的参数p-->
<?php for($i=1; $i<=$pages; $i++): ?>
<a
href="http://www.phpcntest.com/test.php?page=<?php echo $i ?>"
<?php
echo ($i == $page) ? 'style="background-color: red;"' : '';
?>
>
<?php echo $i ?>
</a>
<?php endfor; ?>
<a href="http://www.phpcntest.com/test.php?page=
<?php
//发现越界了,怎么办?最大页不能超过总页数才可以.
echo (($page+1)>$pages)?$pages:($page+1);
?>">下一页</a>
<a href="http://www.phpcntest.com/test.php?page=<?php echo $pages; ?>">尾页</a>
<!--实现页面的快速跳转-->
<form action="" method="get">
第
<select name="page" id="">
<?php for($i=1; $i<=$pages; $i++): ?>
<!-- 循环输出全部页码,并锁定当前页-->
<option value="<?php echo $i; ?>"
<?php
if($page==$i){
echo 'selected';
}
?>
>
<?php echo $i; ?>
</option>
<?php endfor; ?>
</select>
页
<button>跳转</button>
</form>
</h3>
</body>
</html>运行实例 »
点击 "运行实例" 按钮查看在线实例
六.page分类类的简单封装和使用
实例
<?php
//分页查询类
namespace model;
class Page
{
//查询起始偏移量
private $offset;
//每页记录数量
private $pageSize;
//数据库连接对象
private $pdo;
//构造方法
public function __construct($pagesize=5)
{
//初始化每页记录数量
$this->pageSize = $pagesize;
//查询起始偏移量: (页码-1)*数量
$this->offset = ($this->getPage()-1)*$this->pageSize;
}
//连接数据库
public function connect($type,$host,$dbname,$user,$pass)
{
try {
$this->pdo = new \PDO("{$type}:host={$host};dbname={$dbname}",$user,$pass);
} catch (\PDOException $e) {
die($e->getMessage());
}
}
//获取当前页码
public function getPage()
{
//如果url中存在页码变量p则取之,否则默认为1,即第一页
return isset($_GET['page']) ? $_GET['page'] : 1;
}
//获取总页数
public function getPages($table)
{
//因为是读操作,所有必须使用count()获取,不允许通过rowCount()进行判断
$stmt = $this->pdo->prepare("SELECT COUNT(*) FROM `{$table}` ");
$stmt->execute();
//获取结果集中的一行一列,即记录总数
$total = $stmt->fetchColumn(0);
// ceil()是向上取整函数,即当前页哪怕只有一条记录,也必须输出一个页面
return ceil($total / $this->pageSize);
}
//获取分页数据
public function getData($table)
{
//从指定数据表中的获取当前页需要显示的记录
$sql = "SELECT * FROM `{$table}` LIMIT {$this->offset}, {$this->num} ;";
$stmt = $this->pdo->prepare($sql);
$stmt->execute();
//获取分页数据并返回关联部分
return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}
}运行实例 »
点击 "运行实例" 按钮查看在线实例
七.具体用于page类
实例
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<link rel="stylesheet" href="./inc/style.css">
<title>封装分页类,实现代码复用</title>
</head>
<body>
<?php
//导入分页类
require 'Page.php';
use model\Page;
//实例化分页类
$page = new Page();
//连接数据库
$page->connect('mysql','localhost','phpcn','root','root');
//获取当前页
$currentPage = $page->getPage();
//获取总页数
$totalPages = $page->getPages('staff');
//获取分页数据
$data = $page->getData('staff');
?>
<table>
<caption>员工信息表</caption>
<tr>
<th>ID</th>
<th>姓名</th>
<th>年龄</th>
<th>性别</th>
<th>工资</th>
</tr>
<?php foreach ($data as $row): ?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['age']; ?></td>
<td><?php echo $row['sex']?'男':'女'; ?></td>
<td><?php echo $row['salary']; ?></td>
</tr>
<?php endforeach;?>
</table>
<h3>
<!-- 当前是第一页的时候,上一页和首页链接应该不显示-->
<?php if($currentPage != 1): ?>
<a href="http://www.phpcntest.com/test.php?page=1">首页</a>
<a href="http://www.phpcntest.com/test.php?page<?php echo $currentPage-1; ?>">上一页</a>
<?php endif; ?>
<!--生成中间页码-->
<?php for($i=1; $i<=$totalPages; $i++): ?>
<!------高亮显示当前页码----------->
<a class="<?php if($currentPage==$i){echo 'active';}?>" href="http://www.phpcntest.com/test.php?page=<?php echo $i ?>"><?php echo $i ?></a>
<?php endfor; ?>
<!--当前已经是最后一页的时候,下一页和最后一页也应该不显示-->
<?php if($currentPage != $totalPages) :?>
<a href="http://www.phpcntest.com/test.php?page=<?php echo $currentPage+1; ?>">下一页</a>
<a href="http://www.phpcntest.com/test.php?page=<?php echo $totalPages; ?>">尾页</a>
<?php endif; ?>
<!--实现页面的快速跳转-->
<form action="" method="get">
第
<select name="page" id="">
<?php for($i=1; $i<=$totalPages; $i++): ?>
<!-- 循环输出全部页码,并锁定当前页-->
<option value="<?php echo $i; ?>" <?php if($currentPage==$i){echo 'selected';} ?>><?php echo $i; ?></option>
<?php endfor; ?>
</select>
页
<button>跳转</button>
</form>
</h3>
</body>
</html>运行实例 »
点击 "运行实例" 按钮查看在线实例