php pdo自动分页类代码与例子

WBOY

发布： 2016-07-25 08:51:48

原创

1483 人浏览过

/**
* 类名: PdoPage
* 作者：谢声涛 shishengsoft@gmail.com
* 描述: 继承自PDO类，增加了自动分页功能，类似MS ADO组件的自动分页功能。
*/ bbs.it-home.org
//-------------开始---------------
class PdoPage extends PDO {
public $RecordCount = 0; // 记录集的记录总数
public $AutoPage = false;// 启用自动分页功能
public $PageSize = 0;// 每页的记录行数
public $CurrentPage = 0; // 当前页
public $Pages = 0;// 总页数
public $BOF = false; // 游标到记录集之前
public $EOF = false; // 游标到记录集之后
private $RecordSet = null; // 记录集
private $mCurrentRow = -1; // 记录集中当前游标位置
private $Rows = 0;//总记录数
// 关闭连接
public function Close(){unset($this);}
// 分页查询
public function QueryEx($SqlString){
// 是否启用自动分页功能
if($this->AutoPage){
// 检查PageSize参数
if ($this->PageSize // 计算总记录数
$rs = @parent::query($this->rebuildSqlString($SqlString));
$this->Rows = $rs->fetchColumn();
// 计算总页数
if ($this->Rows PageSize) {$this->Pages = 1;}
elseif ($this->Rows % $this->PageSize) {$this->Pages = intval($this->Rows/$this->PageSize)+1;}
else {$this->Pages = intval($this->Rows/$this->PageSize);}
// 约束CurrentPage值，使之位于1到Pages之间。
if($this->CurrentPage CurrentPage =1;}
if($this->CurrentPage > $this->Pages) {$this->CurrentPage = $this->Pages;}
//计算偏移量
$Offset = $this->PageSize * ($this->CurrentPage - 1);
// 重组SQL语句,SqlString有分号则去掉
$SqlString = str_replace(";","",$SqlString) . " LIMIT $Offset,$this->PageSize;";
}
// 查询并返回记录集
$rs = new PDOStatement();
$rs = @parent::query($SqlString);
$this->RecordSet = $rs->fetchAll();//returns an array.
$this->RecordCount = count($this->RecordSet);
if(!$this->AutoPage){$this->Pages = (!$this->RecordCount)?0:1;}
return $this->RecordCount;
}
// 取得字段值
public function FieldValue($FieldName=""){
return ($this->RecordSet[$this->mCurrentRow][$FieldName]);
}
//--------移动记录集游标---------------
public function Move($RowPos){
if ($RowPosif ($RowPos > $this->RecordCount-1) $RowPos = $this->RecordCount-1;
$this->mCurrentRow = $RowPos;
$this->EOF = false;
$this->BOF = false;
}
public function MoveNext(){
if($this->mCurrentRow RecordCount-1){
$this->mCurrentRow++;
$this->EOF = false;
$this->BOF = false;
}
else{
$this->EOF = true;
}
}
public function MovePrev(){
if($this->mCurrentRow > 0){
$this->mCurrentRow--;
$this->EOF = false;
$this->BOF = false;
}else{
$this->BOF = true;
}
}
public function MoveFirst(){
$this->mCurrentRow = 0;
$this->EOF = false;
$this->BOF = false;
}
public function MoveLast(){
$this->mCurrentRow = $this->RecordCount-1;
$this->EOF = false;
$this->BOF = false;
}
//--------------------------------------------------
// 用于执行插入、修改、删除等操作
public function Execute($SqlString){
return @parent::query($SqlString);
}
//-----------------私有函数-----------------------------
// 重新构造SQL语句，如将"select * from tb2"改写为"select count(*) from tb2"，旨在提高查询效率。
private function rebuildSqlString($SqlString){
if(preg_match("/select[ ,./w+/*]+ from/",$SqlString,$marr)){
$columns = preg_replace("/select|from/","",$marr[0]);
$columns = preg_replace("//*/","/*",$columns);
$result = preg_replace("/$columns/"," count(*) ",$SqlString);
return $result;
}
}
//-------------结束-----------------------------------
}
//-------------结束-----------------------------------
?>

复制代码

2、使用示例：需修改MySQL用户名、密码、数据库名等信息。

include_once("./pdopage_class.php");
$db = new PdoPage("mysql:host=localhost;dbname=mydb","root","123456");
$db->Execute("set character set gbk;");
$db->AutoPage = false;
$db->PageSize = 6;
$db->CurrentPage = 1;
$db->QueryEx("select * from tb2;");
$db->MoveFirst();
while (!$db->EOF) {
echo $db->FieldValue("id"),"/t",$db->FieldValue("name"),"/t",$db->FieldValue("age"),"/n";
$db->MoveNext();
}
$db->Close();
?>

复制代码