php和mysql分页显示详解

1.数据库连接的类dbClass.inc。
<?php
/**
* a class use to connect the MySQL database and do some query
*/
class dbClass {
private $hostName = "localhost:3306";
private $dbName = "ebooklib";
private $Login = "root";
private $Password = "";
private $conn;
private $result;

function dbClass(){
$this->conn = mysql_connect("$this->hostName","$this->Login","$this->Password");
mysql_select_db("$this->dbName", $this->conn);
}

function executeQuery($sql){
$this->result = mysql_query("$sql",$this->conn);
return $this->result;
}

function closeConn(){
mysql_close($this->conn);
}
}

?>

2.解决分页问题的PageQuery.inc
<?php
include("dbClass.inc");
class PageQuery extends dbClass {
    private $Offset;             // 记录偏移量
    private $Total;             // 记录总数
     
    private $maxLine;             // 记录每页显示记录数
    private $result;             // 读出的结果
   
    private $TPages;             // 总页数
    private $CPages;             // 当前页数
   
    private $PageQuery;         // 分页显示要传递的参数
    private $Query;             // query 语句
    private $QueryPart;         // " FROM " 以后的 query 部分
    private $QueryString;         // ? 以后部分 
     
    private $FilePath;
           
    // 每页显示行数
    function PageQuery($pageLine=10) {   
        $this->dbClass();
        $this->maxLine = $pageLine;
      }
     
      // 记录总数
    function getTotal(){
        return $this->Total;
    }
     
      // 显示总页数
    function getTotalPages() {
        return $this->TPages;
    }

    //显示当前所在页数
    function getCurrenPages() {         
        return $this->CPages;
    }
   
    function myQuery($sql, $flag=1){
            GLOBAL $offset;
            $this->Query = $sql;
       
        // 获取文件名
        //$this->FilePath = $GLOBALS["REQUEST_URI"];
            $this->FilePath = $GLOBALS["SCRIPT_NAME"];
           
            // 获取查询条件
            $this->QueryString = $GLOBALS["QUERY_STRING"];           
            //echo $this->QueryString . "<br>";           
           
            // 截取 " from " 以后的 query 语句
            $this->QueryPart = trim(strstr($sql, " from "));
           
            // 计算偏移量
            if (!isset($offset)) $this->Offset = 0;
            else $this->Offset = (int)$offset;
           
           
       
       
        // 计算总的记录条数
        $SQL = "SELECT Count(*) AS total " . $this->QueryPart;
        $this->result = $this->executeQuery($SQL);
            $this->Total = mysql_result($this->result,0);
           
            // 设置当前页数和总页数
        $this->TPages = (double)Ceil((double)$this->Total/$this->maxLine);
        $this->CPages = (double)Floor((double)$this->Offset/$this->maxLine+1);
       
       
        // 根据条件判断，取出所需记录
        if ($this->Total > 0) {
            //flag等于1表示要分页，否则不分页
            if($flag==1)
                $SQL = $this->Query . " LIMIT " . $this->Offset . " , " . $this->maxLine;
            else
                $SQL = $this->Query;           
            echo $SQL . "<br>";
            $this->result = $this->executeQuery($SQL);
        }
        return $this->result;
    }
   
    //**********显示翻页提示栏************* 
    // 显示首页、下页、上页、尾页
    function PageLegend() {       
     $str = "";
        $i = 0;
        $first = 0;
        $next = 0;
        $prev = 0;
        $last = 0;
   
            $next = $this->Offset + $this->maxLine;
            $prev = $this->Offset - $this->maxLine;
            $last = ($this->TPages - 1) * $this->maxLine;
           
            GLOBAL $offset;
            if (!isset($offset)) $this->QueryString .= "&offset=";
            else{
                $this->QueryString = substr($this->QueryString,0,strrpos($this->QueryString,'&')) . "&offset=";
            }
           
            if($this->Offset >= $this->maxLine)
            $str .=  " <A href=" . $this->FilePath . "?" . $this->QueryString . $first . ">首页</A> ";
            else $str .= " 首页 ";
       
        if($prev >= 0)
            $str .=  " <A href=" . $this->FilePath . "?" . $this->QueryString . $prev . ">上一页</A> ";
        else $str .= " 上一页 ";
       
        if($next < $this->Total)
            $str .=  " <A href=" . $this->FilePath . "?" . $this->QueryString . $next . ">下一页</A> ";
        else $str .= " 下一页 ";
       
        if($this->TPages != 0 && $this->CPages < $this->TPages)
            $str .=  " <A href=" . $this->FilePath . "?" . $this->QueryString . $last . ">尾页</A>";
        else $str .= " 尾页 ";

        $str .= " 页次：" . $this->getCurrenPages() . "/" . $this->getTotalPages() . "页 ";
        $str .= $this->maxLine . "条/页 " . "共" . $this->Total . "条";
            return $str;
    }
}
?>
3.用于显示结果的mysql_result_all.inc
<?
function mysql_result_all($result,$format="") {
echo "<table $format><tr>";
for($i=0;$i<mysql_num_fields($result);$i++) {
echo "<th>".mysql_field_name($result,$i)."</th>";
}
echo "</tr>";
while($row = mysql_fetch_array($result) ) {
for($i=0;$i<mysql_num_fields($result);$i++) {
echo "<td>".$row[$i]."</td>";
}
echo "</tr>";
}
echo "</table>";
}
?>
4.显示页面的代码：
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>php＆mysql分页显示</title>
</head>

<body>
<?php
include("PageQuery.inc");

$pq = new PageQuery(5); // 获取Connection
$res=$pq->myQuery("select * from users"); // 执行查询

require("mysql_result_all.inc");
mysql_result_all($res,"border=1");
echo $pq->PageLegend(2); // 翻页栏
?>
</body>
</html>

posted on 2005-09-01 17:21 扭转乾坤 阅读(296) 评论(0)  编辑  收藏 所属分类: JAVA使用技巧