分页查询的原理与偏移量的计算方法
先查询数据库有多少条数据,再根据每页显示的数量,进行查询,限制条数,然后分页显示.
语句类似:"SELECT * FROM {表名} LIMIT {偏移量},{每页显示数量};"
偏移量计算:偏移量= (页码-1)*每页显示数量;
2.实现分页查询
实例
<?php
$pdo = new PDO('mysql:host=127.0.0.1;dbname=php','root','root');
$page = isset($_GET['p']) ? $_GET['p'] : 1;
$offset = ($page -1)*5;
$prePage = ($page == 1) ? 1 :$page -1;
$sql = "SELECT * FROM `staff` LIMIT {$offset} ,5";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);
//计算总数
$sql = "SELECT count(*) FROM `staff`";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchColumn(0);
$total = ceil($result/5);
?>
<!doctype html>
<html lang="zh-Hans">
<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
{
border-collapse:collapse;
width:50%;
margin:30px auto;
text-align:center;
}
table tr:first-child
{
background: #4392ff;
}
table tr td {
padding: 10px;
}
table caption
{
font-size:1.5em;
margin-bottom:15px;
}
div{
width:50%;
margin:30px auto;
text-align: center;
}
div ul{
margin: 0 auto;
height:50px;
display: inline-block;
padding-left: 0;
}
div ul li{
margin: 0 auto;
list-style:none;
float:left;
padding:5px;
}
div ul li a{
display: inline-block;
text-decoration-line:none;
width:48px;
height:20px;
padding:5px;
border:1px solid black;
background-color: skyblue;
color:black;
border-radius:10px;
}
.active{
background-color: red;
color:white;
}
</style>
</head>
<body>
<table >
<caption>员工信息表</caption>
<tr>
<td>ID</td>
<td>姓名</td>
<td>年龄</td>
<td>性别</td>
<td>工资</td>
</tr>
<?php foreach($res as $value): ?>
<tr><td><?php echo $value['id'] ?></td>
<td><?php echo $value['name'] ?></td>
<td><?php echo $value['age'] ?></td>
<td><?php echo $value['sex']?'女':'男' ?></td>
<td><?php echo $value['salary'] ?></td>
</tr><?php endforeach;?>
</table>
<div>
<ul>
<li><a href="demo1.php?p=1">首页</a></li>
<li><a href="demo1.php?p=<?php echo (($prePage-1)==0)? '1':$prePage-1; ?>">上一页</a></li>
<?php $i=0;while($i<3): if($prePage+2<=$total){$x = $prePage;}else{ $x = $total-2;}?>
<li><a href="demo1.php?p=<?php echo $x+$i; ?>" <?php echo (($x+$i)==$page)?'class="active"': ''; ?>><?php echo $x+$i; ?></a></li>
<?php $i++;endwhile;?>
<li><a href="demo1.php?p=<?php echo (($page+1)>=$total)? $total:$page+1; ?>">下一页</a></li>
<li><a href="demo1.php?p=<?php echo $total; ?>">尾页</a></li>
</ul>
</div>
<div>
<form action="" METHOD="get">
<span>跳页</span>
<select name="p" id="p">
<?php $i=1;while($i<=$total): ?>
<option value="<?php echo $i ?>" <?php echo ($i==$page)? 'selected':''; ?>><?php echo $i ?></option>
<?php $i++;endwhile; ?>
</select>
<span><button>提交</button></span>
</form>
</div>
</body>
</html>运行实例 »
点击 "运行实例" 按钮查看在线实例
