php mysql数据库操作分页类

复制代码 代码如下:
/*
*mysql数据库分页类
*@packagepagelist
*@authoryytcpt(无影)
*@version2008-03-27
*@copyrigthhttp://www.d5s.cn/
*/
/*
*分页样式
.page{float:left;font:11pxArial,Helvetica,sans-serif;padding:6px0;margin:0px10%;margin-top:10px;}
.pagea,.pagestrong{padding:2px6px;border:solid1px#ddd;background:#fff;text-decoration:none;}
.pagea:visited{padding:2px6px;border:solid1px#ddd;background:#fff;text-decoration:none;}
.page.break{padding:2px6px;border:none;background:#fff;text-decoration:none;}
.pagestrong{padding:2px6px;border-color:#999;font-weight:bold;font-size:13px;vertical-align:top;background:#fff;}
.pagea:hover{color:#fff;background:#0063DC;border-color:#036;text-decoration:none;}
.pagea:hoverdiv{color:#FFF;cursor:pointer!important;cursor:hand;}
*/
classpagelist{
var$page;//分页页码
var$sql;//分页sql
var$img_path;//图标路径
var$img;//图标名称
var$img_btn;//图标地址
var$page_size;//设置每页显示条数
var$num_btn;//设置数字分页的显示个数
var$total_pages;//一共分多少页
var$total_records;//一共有多少条记录
var$url;
var$table;
var$new_sql;//指定的SQL语句
var$db;
function__construct(){
global$db;
$this->db=$db;
$tmp_page=intval(trim($_GET["page"]));
$this->page=empty($tmp_page)?1:$tmp_page;
$this->set_table();
$this->page_size=20;
$this->num_btn=9;
$this->img_path='/images/';
$this->img=array("ico_first.gif","ico_front.gif","ico_next.gif","ico_last.gif");
}
functionset_table(){
$this->table["tablename"]="";
$this->table["id"]="id";
$this->table["orderby"]=$this->table["id"];
$this->table["descasc"]="DESC";
$this->table["fileds"]="*";
$this->table["where"]="";
}
functionset_img(){
$this->img_btn[0]="img_path.$this->img[0]."'alt='首页'border='0'align='absmiddle'/>";
$this->img_btn[1]="img_path.$this->img[1]."'alt='上一页'border='0'align='absmiddle'/>";
$this->img_btn[2]="img_path.$this->img[2]."'alt='下一页'border='0'align='absmiddle'/>";
$this->img_btn[3]="img_path.$this->img[3]."'alt='末页'border='0'align='absmiddle'/>";
}
functionset_show_page(){
$this->set_img();//设置翻页图片路径
$this->set_url();
$this->set_total_records();
if($this->total_records<$this->page_size){
$this->total_pages=1;
}else{
$this->total_pages=ceil($this->total_records/$this->page_size);
}
if($this->page>$this->total_pages){
$this->page=$this->total_pages;
}
}
functionshow_first_prv(){
if($this->page==1){
$str="".$this->img_btn[0]."".$this->img_btn[1]."";
}else{
$str="url."1"."'>".$this->img_btn[0]."";//此处1为首页，page值为1
$str.="url.($this->page-1)."'>".$this->img_btn[1]."";
}
return$str;
}
functionshow_next_last(){
if($this->page>=$this->total_pages){
$str="".$this->img_btn[2]."".$this->img_btn[3]."";
}else{
$str="url.($this->page+1)."'>".$this->img_btn[2]."";
$str.="url.$this->total_pages."'>".$this->img_btn[3]."";
}
return$str;
}
functionshow_num_text(){
$str="转到第page."'style='border:0;border-bottom:1pxsolid#CCC;text-align:center;width:20px;'/>页";
$str.="url."'+document.getElementById('go_num_text').value;"style='font-family:Arial,Helvetica,sans-serif;font-weight:bold;font-size:14px;'>[Go]";
return$str;
}
functionshow_num_select(){
if($this->total_pages<50){
$str="<selectonchange="if(this.options[this.selectedIndex].value!=''){location=this.options[this.selectedIndex].value;}">";
for($i=1;$i<=$this->total_pages;$i++){
$str.="url.$i."'".($this->page==$i?"selected='selected'":"").">".$i."";
}
$str.="";
}else{
$str="";
}
return$str;
}
functionshow_num_btn(){
if($this->page>=1and$this->page<=$this->total_pages){
$tmp_p=($this->num_btn-1)/2;
if(($this->page-$tmp_p)<=0){
$start_p=1;
}else{
if(($this->page-$tmp_p)>$this->num_btnand($this->page-$tmp_p)>($this->total_pages-$this->num_btn+1)){
$start_p=$this->total_pages-$this->num_btn+1;
}else{
$start_p=$this->page-$tmp_p;
}
}
if(($this->page+$tmp_p)<$this->total_pages){
$end_p=($this->page+$tmp_p)<$this->num_btn?$this->num_btn:($this->page+$tmp_p);
if($end_p>$this->total_pages){
$end_p=$this->total_pages;
}
}else{
$end_p=$this->total_pages;
}
}
$str="";
for($i=$start_p;$i<=$end_p;$i++){
if($i==$this->page){
$str.="".$i."";
}else{
$str.="url.$i."'>".$i."";
}
}
return$str;
}
functionshow_page_info(){
$str="共".$this->total_records."条/".$this->total_pages."页";
return$str;
}
functionshow_page(){
if($this->total_records<1){
$this->set_show_page();
}
$str=$this->total_pages>1?$this->show_first_prv().$this->show_num_btn().$this->show_next_last().$this->show_page_info().$this->show_num_text():"";
return$str;
}
//总页数
functionset_total_pages(){
$this->total_pages=ceil($this->total_records/$this->page_size);
}
//总记录数
functionset_total_records(){
if($this->total_records==0or!isset($this->total_records)){
if(empty($this->count_sql)and!empty($this->table["tablename"])){
$sql="SELECTcount(".$this->table["id"].")ascount_idFROM`".$this->table["tablename"]."`".($this->table["where"]!=""?"WHERE".$this->table["where"]:"");
}else{
$sql=preg_replace("/SELECT(.*?)FROM(.*?)/i","SELECTcount(id)AScount_idFROM2",$this->sql);
}
$arr=$this->db->row_query_one($sql);
$this->total_records=$arr["count_id"];
}
}
/*
*根据sql返回查询数据
*指定$sql时，不必指定limit
*/
functionget_rows_by_sql($sql){
$this->sql=$sql."LIMIT".$this->page_size*($this->page-1).",".$this->page_size;//指定的SQL;
return$this->db->row_query($this->sql);
}
/*
*最常用的分页方法，只需要传3个参数
*$tablename表名，$where查询条件，$orderby排序字段（默认以id倒序排列）
*/
functionget_rows($tablename,$where="",$orderby=""){
$this->table["tablename"]=$tablename;
$this->table["where"]=$where;
$orderby?$this->table["orderby"]=$orderby:"";
$arr=array(
"page"=>$this->show_page(),//分页代码
"rows"=>$this->get_rows_by_sql(),//记录数
"sum"=>$this->total_records,//总记录数
);
return$arr;
}
/*
*特殊查询，$sql_query查询sql语句,$row_count统计总数
*/
functionget_rows_sql($sql_query,$row_count=0){
$this->total_records=$row_count;
$arr["rows"]=$this->get_rows_by_sql($sql_query);
$arr["page"]=$this->show_page();
$arr["sum"]=$this->total_records;
return$arr;
}
functionget_sql(){
if($this->total_records>10000){
$this->sql="SELECT".$this->table["fileds"]."FROM`".$this->table["tablename"]."`".($this->table["where"]!=""?"WHERE".$this->table["where"].'AND'.$this->table["id"].'>=':'WHERE'.$this->table["id"].'>=').'(SELECT'.$this->table["id"].'FROM`'.$this->table["tablename"].'`ORDERBY'.$this->table["id"].'LIMIT'.$this->page_size*($this->page-1).',1)'."ORDERBY".$this->table["orderby"]."".$this->table["descasc"]."LIMIT".$this->page_size;
}else{
$this->sql="SELECT".$this->table["fileds"]."FROM`".$this->table["tablename"]."`".($this->table["where"]!=""?"WHERE".$this->table["where"]:"")."ORDERBY".$this->table["orderby"]."".$this->table["descasc"]."LIMIT".$this->page_size*($this->page-1).",".$this->page_size;
}
//SELECT*FROMarticlesORDERBYidDESCLIMIT0,20
//SELECT*FROMarticlesWHEREcategory_id=123ANDid>=(SELECTidFROMarticlesORDERBYidLIMIT10000,1)LIMIT10
return$this->sql;//SQL语句
}
functionset_url(){
$arr_url=array();
parse_str($_SERVER["QUERY_STRING"],$arr_url);
unset($arr_url["page"]);
if(empty($arr_url)){
$str="page=";
}else{
$str=http_build_query($arr_url)."&page=";
}
$this->url="http://".$_SERVER["HTTP_HOST"].$_SERVER["PHP_SELF"]."?".$str;
}
}
?>

复制代码 代码如下:
$db_config["hostname"]="127.0.0.1";//服务器地址
$db_config["username"]="root";//数据库用户名
$db_config["password"]="root";//数据库密码
$db_config["database"]="wap_blueidea_com";//数据库名称
$db_config["charset"]="utf8";
$config["charset"]="utf-8";//网站编码

include('db.php');
include('pagelist.php');
$db=newdb();
$db->connect($db_config);
header("content-type:text/html;charset=".$config["charset"]);//设置页面编码
$pl=newpagelist();
$arr=$pl->get_rows('table_name');
unset($pl);
echo'';
print_r($arr);
echo'';
//指定特殊sql时候
$pl=newpagelist();
$sql='SELECT*FROM`wap_article`ASa,`wap_article_info`ASbWHEREa.id=b.articleid';
$arr=$pl->get_rows_sql($sql);
unset($pl);
echo'';
print_r($arr);
echo'';
?>
当表中的记录总数在10000条以上时，使用了子查询分页，这样效率会更高一些，数据量小的时候，直接查询更快。