以前写过这么一句$sql="select top ".$pagesize." k.id as id,k.khmc as khmc,k.khwz as khwz,k.dp as dp,t.class as wzlb from khal k left join type t on k.wzlb=t.id ".$where." k.id not in (select top ".$offset." id from khal order by id desc) order by k.id desc";
function limit($query, $table_name, $primary_key, $criteria) { $start = $criteria->getStart(); $limit = $criteria->getLimit(); if ($start<=0 && $limit<=0) return $query; if ($limit>0) $query = preg_replace('/select/i','SELECT TOP '.$limit,$query); if ($start>0) { $split_sql = "(NOT ".$primary_key." in (select top $start ".$primary_key." from $table_name"; //table name if (isset($criteria) && is_subclass_of($criteria, 'criteriaelement')) { $split_sql .= ' '.$criteria->renderSort(); } $split_sql .= '))'; if(!preg_match('/where/i',$query)) { $query = preg_replace("/$table_name/i","$table_name WHERE $split_sql",$query); } else { $query = preg_replace("/where/i","WHERE $split_sql AND",$query); } } return $query; }这是我的用法,将 select * from usrs 转为 select top 10 * from usrs where not usrID in (select top 100 from usrs)请各位指正
try this need:PEAR/DB class <?php include_once"DB.php"; class TViewPage { var $Table; //表名 var $MaxLine; //每页显示行数 var $LinkID; //数据库连接号 var $db; //var $Id; //分页参考字段 var $Offset; //记录偏移量 var $Total = 0; //记录总数 var $Number; //本页读取的记录数 var $Result; //读出的结果 var $TPages; //总页数 var $CPages; //当前页数 var $Condition; //显示条件 如:where id='$id' order by id desc var $PageQuery; //分页显示要传递的参数 var $pageLink1;//分页显示链接 var $pageLink2;//分页显示链接 var $ctpages; //******构造函数************* //参数:表名、最大行数、偏移量 //分页参考字段请用一个自动编号字段 function TViewPage($TB,$ML,$OF=0,$db){ $this->Table=$TB; $this->MaxLine=$ML; $this->Offset=$OF; $this->Condition=" "; //$this->Id=$ID; $this->db=$db; } //********初始化数据库连接************* //参数:DSN、用户名、密码 //********设置显示条件********* //如:where id='$id' order by id desc //要求是字串,符合SQL语法(本字串将加在SQL语句后) function SetCondition($s){ $this->Condition=$s; } //******设置传递参数************ // key参数名 value参数值 // 如:setpagequery("id",$id);如有多个参数要传递,可多次调用本函数。 function setPageQuery($key,$value){ $tmp['k']=$key; $tmp['value']=$value; $this->PageQuery[]=$tmp; } //********读取记录*************** // 主要工作函数,根据所给的条件从表中读取相应的记录 // 返回值是一个二维数组,Result[记录号][字段名] function ReadList() { $SQL = "select * from ".$this->Table." ".$this->Condition; $result = $this->db->query($SQL); //printr($result); //计算总记录数 $x=0; while($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) $x++; $this->Total=$x; if($this->Total>0) { //根据条件 Condition //起始 $SQL="SELECT * FROM ".$this->Table." ".$this->Condition; $result = $this->db->limitQuery($SQL,$this->Offset,$this->MaxLine);
2、RecordSet对象本身是具有分页功能的。不要跟我说你是在非微软系统中操作access
大家可不可以给我个例子看看啊,急用啊,我是个菜鸟啊,谢谢了!!
$start = $criteria->getStart();
$limit = $criteria->getLimit();
if ($start<=0 && $limit<=0) return $query;
if ($limit>0) $query = preg_replace('/select/i','SELECT TOP '.$limit,$query);
if ($start>0) {
$split_sql = "(NOT ".$primary_key." in (select top $start ".$primary_key." from $table_name"; //table name
if (isset($criteria) && is_subclass_of($criteria, 'criteriaelement')) {
$split_sql .= ' '.$criteria->renderSort();
}
$split_sql .= '))';
if(!preg_match('/where/i',$query)) {
$query = preg_replace("/$table_name/i","$table_name WHERE $split_sql",$query);
} else {
$query = preg_replace("/where/i","WHERE $split_sql AND",$query);
}
} return $query;
}这是我的用法,将 select * from usrs 转为 select top 10 * from usrs where not usrID in (select top 100 from usrs)请各位指正
need:PEAR/DB class
<?php
include_once"DB.php";
class TViewPage { var $Table; //表名
var $MaxLine; //每页显示行数
var $LinkID; //数据库连接号
var $db;
//var $Id; //分页参考字段 var $Offset; //记录偏移量
var $Total = 0; //记录总数
var $Number; //本页读取的记录数
var $Result; //读出的结果 var $TPages; //总页数
var $CPages; //当前页数 var $Condition; //显示条件 如:where id='$id' order by id desc
var $PageQuery; //分页显示要传递的参数 var $pageLink1;//分页显示链接
var $pageLink2;//分页显示链接
var $ctpages;
//******构造函数*************
//参数:表名、最大行数、偏移量
//分页参考字段请用一个自动编号字段 function TViewPage($TB,$ML,$OF=0,$db){
$this->Table=$TB;
$this->MaxLine=$ML;
$this->Offset=$OF;
$this->Condition=" ";
//$this->Id=$ID;
$this->db=$db;
} //********初始化数据库连接*************
//参数:DSN、用户名、密码 //********设置显示条件*********
//如:where id='$id' order by id desc
//要求是字串,符合SQL语法(本字串将加在SQL语句后)
function SetCondition($s){
$this->Condition=$s;
} //******设置传递参数************
// key参数名 value参数值
// 如:setpagequery("id",$id);如有多个参数要传递,可多次调用本函数。 function setPageQuery($key,$value){
$tmp['k']=$key; $tmp['value']=$value;
$this->PageQuery[]=$tmp;
} //********读取记录***************
// 主要工作函数,根据所给的条件从表中读取相应的记录
// 返回值是一个二维数组,Result[记录号][字段名] function ReadList() { $SQL = "select * from ".$this->Table." ".$this->Condition;
$result = $this->db->query($SQL);
//printr($result);
//计算总记录数
$x=0;
while($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) $x++;
$this->Total=$x;
if($this->Total>0) { //根据条件 Condition //起始
$SQL="SELECT * FROM ".$this->Table." ".$this->Condition;
$result = $this->db->limitQuery($SQL,$this->Offset,$this->MaxLine);
$k=0;
$rows = array();
while($rows = $result->fetchRow(DB_FETCHMODE_ASSOC)){
$this->Result[] = $rows;
$k++;
}
$this->Number=$k;
} return $this->Result;
} //**********显示页数*************
//显示当前页及总页数 function ThePage() {
$this->TPages=ceil($this->Total/$this->MaxLine);
$this->CPages=$this->Offset/$this->MaxLine+1;
$this->ctpages = "第".$this->CPages."页/共".$this->TPages."页";
return $this->ctpages;
} //**********显示翻页按钮*************
//此函数要在ThePage()函数之后调用!!!
//显示首页、下页、上页、未页,并加上要传递的参数 function setPage() {
$this->TPages=ceil($this->Total/$this->MaxLine);
$this->CPages=$this->Offset/$this->MaxLine+1;
$first=1;
$next=$this->CPages+1;
$prev=$this->CPages-1;
$last=$this->TPages; $k=count($this->PageQuery);
$strQuery=""; //生成一个要传递参数字串
for($i=0;$i<$k;$i++){
$strQuery.="&".$this->PageQuery[$i]['k']."=".$this->PageQuery[$i]['value'];
} $this->pageLink1 = "";
if($this->CPages>1 && $this->CPages < $this->TPages){
$this->pageLink1 .="| <a href=".$_SERVER['PHP_SELF']."?currentpage=".$first.$strQuery.">首页</A> ";
$this->pageLink1 .= "<a href=".$_SERVER['PHP_SELF']."?currentpage=".$prev.$strQuery.">上一页 </a> ";
$this->pageLink1 .= "| <A href=".$_SERVER['PHP_SELF']."?currentpage=".$next.$strQuery.">下一页</A> ";
$this->pageLink1 .= "<A href=".$_SERVER['PHP_SELF']."?currentpage=".$last.$strQuery.">末页</A> |";
}
else if($this->CPages==1 && $this->CPages < $this->TPages) {
$this->pageLink1 .="| 首页 上一页 | ";
$this->pageLink1 .="<A href=".$_SERVER['PHP_SELF']."?currentpage=".$next.$strQuery.">下一页</A> ";
$this->pageLink1 .="<A href=".$_SERVER['PHP_SELF']."?currentpage=".$last.$strQuery.">末页</A> |";
}
else if($this->CPages==$this->TPages && $this->TPages>1) {
$this->pageLink1 .="| <A href=".$_SERVER['PHP_SELF']."?currentpage=".$first.$strQuery.">首页</A> ";
$this->pageLink1 .="<a href=".$_SERVER['PHP_SELF']."?currentpage=".$prev.$strQuery.">上一页 </a> ";
$this->pageLink1 .="| 下一页 末页 |";
}for($i=1;$i<=$this->TPages;$i++)
{
if($i>1) $this->pageLink2 .=" ";
$this->pageLink2 .= "<a href=".$_SERVER['PHP_SELF']."?currentpage=".$i.$strQuery.">".$i."</a>";
}
} //******end class
} ?>
//给$hou_data赋值
$page1 ='| 首页 上一页 | 下一页 末页 |';//分页链接
$page2 ='';
$total = 0;//总记录数
$pagestart = 0;//开始记录数
$pageend = 0;//结束记录
$currentpage = 1;//当前页
$totalpage = 0; //总页数
$list_data = array();//住房信息例表
$condition = " WHERE `isSource` = 1 ";//查询条件初始 已发布if(isset($_REQUEST['currentpage'])) $currentpage=$_REQUEST['currentpage'];//当前页
$pagestart=($currentpage-1)*$OFFICE['page']['num'];
include_once"../includes/TViewPage.inc.php";
$page = & new TViewPage(DEMAND_TABLE,$OFFICE['page']['num'],$pagestart,$db);if(isset($_GET['sort']))
{
$condition .= " and `properType` like '".$OFFICE['form']['properType'][$_GET['sort']]."'";
$page->setpagequery("sort",$_GET['sort']);
}
$condition .= " order by id desc ";
$page->SetCondition($condition);
$row = $page->ReadList();
$page->ThePage();
$currentpage = $page->CPages;
$totalpage = $page->TPages;
$total = $page->Total;
$pageend = $pagestart+$page->Number;if($total > 0)
{
$i=0;
while($row[$i]){
if( ($i%2) == 0 ) $x=1;
else $x=2;
$bgcolor = $OFFICE['list']['bgcolor'][$x];
$delLink = "<IMG height=2 src = images/dot_blue.gif> <a href = ".$_SERVER['PHP_SELF']."?id=".$row[$i]['id']."&delete=1>删除此信息</a>";
$delLink.="<IMG height=2 src = images/dot_blue.gif> <a href = ".$_SERVER['PHP_SELF']."?id=".$row[$i]['id']."&issue=1>取消发布</a>";
$delLink.="<IMG height=2 src = images/dot_blue.gif> <a href = editDemand.php?id=".$row[$i]['id'].">修改此信息</a>";
$price = $row[$i]['minPrice']."-".$row[$i]['maxPrice'];
$housetpldata = array (
'formName' => 'frm'.$i,
'detailName'=> 'detail'.$i,
'textName' => 'text'.$i,
'aspectX' => 'aspect'.$i,
'asp_fX' => 'asp_f'.$i,
'shapeX' => 'shape'.$i,
'shp_fX' => 'shp_f'.$i,
'BGCOLOR' => $bgcolor,
'clientName' => $row[$i]['clientName'],
'telephone' => $row[$i]['telephone'],
'region' => $row[$i]['region'],
'constructionAcreage'=> $row[$i]['constructionAcreage'],
'price' => $price,
'tcurrency' => $row[$i]['tcurrency'],
'unit' => $row[$i]['unit'],
'delete' => $delLink,
'contactInfo' => $row[$i]['contactInfo'],
'email' => $row[$i]['email'],
'demandZone' => $row[$i]['demandZone'],
'floor' => $row[$i]['floor'],
'equipment' => $row[$i]['equipment'],
'houseType' => $row[$i]['houseType'],
'decoration' => $row[$i]['houseType'],
'demandType' => $row[$i]['demandType'],
'fangXin' => $row[$i]['fangXin'],
'houseAge' => $row[$i]['houseAge'],
'availableDate' => $row[$i]['houseAge'],
'memo' => unhtmlentities($row[$i]['memo']),
'properType' => $row[$i]['properType'],
'registertime' => $row[$i]['registertime']
);
$list_data[$i++] = array ( getHouHtm($housetpldata,"user.tpl.htm") );//列表资料
if($i == ($page->Number)) break;
}//end while
$page->setPage();
}//end if
if( !empty( $page->pageLink1 )) $page1 = $page->pageLink1;
if($total == 0) {
$pagestart =-1;
$currentpage = 0;
}$hou_data = array (
'TOTAL' => $total,
'PAGESTART' => $pagestart+1,//开始记录
'PAGEEND' => $pageend,//结束记录
'CURRENPAGE'=> $currentpage,
'TOTALPAGE' => $totalpage
);
$page_data = array (
'PAGE1' => $page1,
'PAGE2' => $page->pageLink2
);