PHP中mysql查询输出及分页的综合示例
index.php
<?php
// 包含类文件
include 'page.class.php';
// 连接数据库
$link = mysql_connect("localhost", "root", "***") or die("数据库连接失败！");
// 创建一个分页对象，共521条数据，每页12条数据
$page = new Page(521, 12);
// 使用数据库的think表
mysql_select_db("think");
// sql查询语句
$sql = "selec t * from sa_user order by id {$page->limit()}";
// 结果资源输出
$result = mysql_query($sql);
// 制作表格
echo '<table width = "70%" border = "1" align = "center" >';
echo "<caption><h2>{$sql}</h2></caption>";
// 表头字段
echo "<tr>";
$fields = mysql_num_fields($result);
for ($i = 0; $i < $fields; $i++){
echo "<th>".mysql_field_name($result, $i)."</td>";
}
echo "</tr>";
// 内容输出
while($row = mysql_fetch_row($result)){
echo "<tr>";
foreach($row as $str){
echo "<td>{$str}</td>";
}
echo "</tr>";
}
// 尾部分页控件
echo '<tr><td colspan = "'.$fields.'" align = "right">'.$page->fpage().'</td></tr>';
echo "</table>";page.class.php<?php
class Page{
// 总数、每页条数、总页数、当前页码、基础url、起始条、结束条
private $total, $number, $pages, $cpage, $url, $start, $end;
public function __construct($total, $number){
$this->total = $total;
$this->number = $number;
$this->pages = $this->get_pages();
$cpage = $_GET["page"];
$this->cpage = empty($cpage) ? 1 : $cpage;
$this->url = "http://php.yusian.com";
$this->start = ($this->cpage - 1) * $this->number;
$end = $this->start + $number;
$this->end = ($end > $total) ? $total : $end;
}
// 计算总页数
private function get_pages(){
// ceil相当于(($this->total - 1)/$this->number)+1;
return ceil($this->total/$this->number);
}
// 首页 上一页
private function first(){
$prev_num = $this->cpage - 1;
// 首页链接
$home_str = "<a href={$this->url}>首页</a>";
// 上一页链接，通过url?page=n来实现
$prev_str = "<a href={$this->url}?page="."{$prev_num}>上一页</a>";
// 如果当前页是第1页，则不显示
return ($this->cpage > 1) ? $home_str." ".$prev_str : NULL;
}
// 中间页码表
private function flist(){
$list = "";
// 默认情况下显示9个页码链接，如果总页数少于9个，则按最大数显示
$count = $this->pages > 9 ? 9 : $this->pages;
// 如果是9个，则前面4个后面4个，中间一个，因此循环从当前页往前数4个
$num = ceil($this->cpage - $count / 2);
// 如果前面不足4个，则直接从第1页开始
if ($this->cpage < $count / 2){
$num = 1;
}
// 如果后面不足4个，则最大只到最后一页
if ($this->cpage > ($this->pages - $count / 2)){
$num = $this->pages - $count + 1;
}
// 开始循环拼接页码链接
for($i = 0; $i < $count; $i++){
$str = "<a href = {$this->url}?page={$num}>{$num}</a>";
// 如果是当前页，则只显示数字，不加超链接
if($num == $this->cpage) $str = $num;
$list = $list.$str;
// 排队最后一个，中间都加两个空格隔开
if ($i < $count - 1) $list .= " ";
$num++;
}
return $list;
}
// 下一页 末页
private function last(){
$next_num = $this->cpage + 1;
// 下一页链接，即为当前页加1
$next_str = "<a href={$this->url}?page={$next_num}>下一页</a>";
// 末尾页取总页码数加链接
$end_str = "<a href={$this->url}?page={$this->pages}>末页</a>";
// 如果当前为最后一页，则不显示
return ($this->cpage < $this->pages) ? $next_str." ".$end_str : NULL;
}
// 返回结果
function fpage(){
$array = array();
$number = $this->end - $this->start;
// 将各种下标放到一个数组里再拼接，可根据需要加减组合
$array[] = "第{$this->start}-{$this->end}条";
$array[] = $this->cpage."/".$this->pages;
$array[] = $this->first();
$array[] = $this->flist();
$array[] = $this->last();
$array[] = "共{$this->total}条记录";
$array[] = "本页显示{$number}条记录";
$string = "";
foreach($array as $title){
$string .= $title."";
}
return $string;
}
public function limit(){
return "LIMIT {$this->start}, {$this->number}";
}
}
页:
[1]