转载:PHP+MySQL分页基础方法

”万事俱备只欠东风“,现在就来说说分页的核心思想。

那就是当前页,页面大小,总记录数。这三个通过总记录数和页面大小,我们就可以计算出总的页数。然后根据当前页来实现对应的显示。

总记录数

$sql_total_records = "select count(*) as cot from wxrank";
$total_records_result = $conn->query($sql_total_records);
$total_records = $total_records_result->fetch_assoc();
echo "总记录: ".$total_records["cot"]."<br>";

当前页

// 通过GET方式获得客户端访问的页码
$current_page_number = isset($_GET['page_number'])?$_GET['page_number']:1;
if($current_page_number<1) {
 $current_page_number =1;
}
if($current_page_number>$total_pages){
 $current_page_number = $total_pages;
}
echo "要访问的页码为:".$current_page_number;

分页核心

// 获取到了要访问的页面以及页面大小,下面开始分页
$begin_position = ($current_page_number-1)*$page_size;
$sql = "select * from innodb_table_stats limit $begin_position,$page_size";
$result = mysql_query($sql);

就这样我们就可以拿到自己想要的结果集了。接下来的就是如何在页面上进行展示而已。

页面展示

// 处理结果集
echo "<table border='#CCF solid 1px'><th>Mysql Fixed Assets Table</th>";
echo "<tr><td>DbName</td><td>TableName</td><td>Last_update</td><td>n_Nows</td><td>Clustered_Index_Size</td><td>Sum_od_Other_Index_sizes</td></tr>";
while(($row = mysql_fetch_row($result))){
 echo "<tr>";
 echo "<td>".$row[0]."</td>";
 echo "<td>".$row[1]."</td>";
 echo "<td>".$row[2]."</td>";
 echo "<td>".$row[3]."</td>";
 echo "<td>".$row[4]."</td>";
 echo "<td>".$row[5]."</td>";
 echo "</tr>";
}
echo "</table>";

// 循环显示总页数
?>
<?php
echo '<a href="SlicePage.php?page_number=1">首页</a>&nbsp;&nbsp;';
for($i=1;$i<=$total_pages;$i++){
 echo '<a href="./SlicePage.php?page_number='.$i.'">第'.$i.'页</a>&nbsp;&nbsp;'; 
}
echo '<a href="SlicePage.php?page_number='.($current_page_number-1).'">上一页</a>&nbsp;&nbsp;';
echo '<a href="SlicePage.php?page_number='.($current_page_number+1).'">下一页</a>&nbsp;&nbsp;';
echo '<a href="SlicePage.php?page_number='.($total_pages).'">尾页</a>&nbsp;&nbsp;';

分页实现

了解了上面的内容之后,下面来看一下这个完整的例子吧。

代码SlicePage.php

<meta charset="utf-8">
<?php
//解决中文乱码,发现不能奏效,则考虑MySQL客户端乱码情况
header("Content-type=text/html;charset=utf-8");

$host = "localhost";
$username = "root";
$password = "mysql";
$dbname = "mysql";

// 开始获取数据库连接
$conn = mysql_connect($host,$username,$password) or die(mysql_error());
// 手动更改客户端编码
mysql_query("set names utf8");
// 选择使用哪一个数据库
mysql_select_db($dbname);

// 获取总的记录数
$sql_total_records = "select count(*) from innodb_table_stats";
$total_records_result = mysql_query($sql_total_records);
$total_records = mysql_fetch_row($total_records_result);
echo "总的记录数位: ".$total_records[0]."<br>";


// 获得总页数,一般来说页面大小事固定的,所以这里暂且定为一页5个数据
$page_size = 3;
$total_pages = ceil($total_records[0]/$page_size);
echo "总页数为: ".$total_pages;


// 通过GET方式获得客户端访问的页码
$current_page_number = isset($_GET['page_number'])?$_GET['page_number']:1;
if($current_page_number<1) {
 $current_page_number =1;
}
if($current_page_number>$total_pages){
 $current_page_number = $total_pages;
}
echo "要访问的页码为:".$current_page_number;

// 获取到了要访问的页面以及页面大小,下面开始分页
$begin_position = ($current_page_number-1)*$page_size;
$sql = "select * from innodb_table_stats limit $begin_position,$page_size";
$result = mysql_query($sql);


// 处理结果集
echo "<table border='#CCF solid 1px'><th>Mysql Fixed Assets Table</th>";
echo "<tr><td>DbName</td><td>TableName</td><td>Last_update</td><td>n_Nows</td><td>Clustered_Index_Size</td><td>Sum_od_Other_Index_sizes</td></tr>";
while(($row = mysql_fetch_row($result))){
 echo "<tr>";
 echo "<td>".$row[0]."</td>";
 echo "<td>".$row[1]."</td>";
 echo "<td>".$row[2]."</td>";
 echo "<td>".$row[3]."</td>";
 echo "<td>".$row[4]."</td>";
 echo "<td>".$row[5]."</td>";
 echo "</tr>";
}
echo "</table>";

// 循环显示总页数
?>
<?php
echo '<a href="SlicePage.php?page_number=1">首页</a>&nbsp;&nbsp;';
for($i=1;$i<=$total_pages;$i++){
 echo '<a href="./SlicePage.php?page_number='.$i.'">第'.$i.'页</a>&nbsp;&nbsp;'; 
}
echo '<a href="SlicePage.php?page_number='.($current_page_number-1).'">上一页</a>&nbsp;&nbsp;';
echo '<a href="SlicePage.php?page_number='.($current_page_number+1).'">下一页</a>&nbsp;&nbsp;';
echo '<a href="SlicePage.php?page_number='.($total_pages).'">尾页</a>&nbsp;&nbsp;';
// 释放数据连接资源
mysql_free_result($result);
mysql_close($conn);

?>

结果初始页为:

初始化页面

点击页码

点击页码页面

下一页

点击下一页

总结

分页是一个很实用的技术,相比较于Java的实现,PHP实现起来灵活性还是很高的。解放了繁琐的面向对象的编程,在思路清晰的时候确实能给人以美感。

发表评论