PHP寫分頁功能時,只要用的還是MySQL,基本都是兩步走
1、取得總數,算頁數。SQL語句自然是
代碼如下 復制代碼 SELECT count(*) FROM tablename;2、根據指定的頁碼號,取得相應的數據。對應的SQL語句,在網上隨便查,都是一樣的:
SELECT f1,f2 FROM table LIMIT offset,length
實例分頁類
代碼如下 復制代碼<?php
/*********************************************
類名: PageSupport
功能:分頁顯示MySQL數據庫中的數據
***********************************************/
class PageSupport{
//屬性
var $sql; //所要顯示數據的SQL查詢語句
var $page_size; //每頁顯示最多行數
var $start_index; //所要顯示記錄的首行序號
var $total_records; //記錄總數
var $current_records; //本頁讀取的記錄數
var $result; //讀出的結果
var $total_pages; //總頁數
var $current_page; //當前頁數
var $display_count = 30; //顯示的前幾頁和後幾頁數
var $arr_page_query; //數組,包含分頁顯示需要傳遞的參數
var $first;
var $prev;
var $next;
var $last;
//方法
/*********************************************
構造函數:__construct()
輸入參數:
$ppage_size:每頁顯示最多行數
***********************************************/
function PageSupport($ppage_size)
{
$this->page_size=$ppage_size;
$this->start_index=0;
}
/*********************************************
構造函數:__destruct()
輸入參數:
***********************************************/
function __destruct()
{
}
/*********************************************
get函數:__get()
***********************************************/
function __get($property_name)
{
if(isset($this->$property_name))
{
return($this->$property_name);
}
else
{
return(NULL);
}
}
/*********************************************
set函數:__set()
***********************************************/
function __set($property_name, $value)
{
$this->$property_name = $value;
}
/*********************************************
函數名:read_data
功能: 根據SQL查詢語句從表中讀取相應的記錄
返回值:屬性二維數組result[記錄號][字段名]
***********************************************/
function read_data()
{
$psql=$this->sql;
//查詢數據,數據庫鏈接等信息應在類調用的外部實現
$result=mysql_query($psql) or die(mysql_error());
$this->total_records=mysql_num_rows($result);
//利用LIMIT關鍵字獲取本頁所要顯示的記錄
if($this->total_records>0)
{
$this->start_index = ($this->current_page-1)*$this->page_size;
$psql=$psql. " LIMIT ".$this->start_index." , ".$this->page_size;
$result=mysql_query($psql) or die(mysql_error());
$this->current_records=mysql_num_rows($result);
//將查詢結果放在result數組中
$i=0;
while($row=mysql_fetch_Array($result))
{
$this->result[$i]=$row;
$i++;
}
}
//獲取總頁數、當前頁信息
$this->total_pages=ceil($this->total_records/$this->page_size);
$this->first=1;
$this->prev=$this->current_page-1;
$this->next=$this->current_page+1;
$this->last=$this->total_pages;
}
/*********************************************
函數名:standard_navigate()
功能: 顯示首頁、下頁、上頁、未頁
***********************************************/
function standard_navigate()
{
echo "<div align=center>";
echo "<form action=".$_SERVER['PHP_SELF']." method="get">";
echo "<font color = red size ='4'>第".$this->current_page."頁/共".$this->total_pages."頁</font>";
echo " ";
echo "跳到<input type="text" size=Ř" name="current_page" value='".$this->current_page."'/>頁";
echo "<input type="submit" value="提交"/>";
//生成導航鏈接
if ($this->current_page > 1) {
echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->first.">首頁</A>|";
echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->prev.">上一頁</A>|";
}
if( $this->current_page < $this->total_pages) {
echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->next.">下一頁</A>|";
echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->last.">末頁</A>";
}
echo "</form>";
echo "</div>";
}
/*********************************************
函數名:full_navigate()
功能: 顯示首頁、下頁、上頁、未頁
生成導航鏈接 如1 2 3 ... 10 11
***********************************************/
function full_navigate()
{
echo "<div align=center>";
echo "<form action=".$_SERVER['PHP_SELF']." method="get">";
echo "<font color = red size ='4'>第".$this->current_page."頁/共".$this->total_pages."頁</font>";
echo " ";
echo "跳到<input type="text" size=Ř" name="current_page" value='".$this->current_page."'/>頁";
echo "<input type="submit" value="提交"/>";
//生成導航鏈接 如1 2 3 ... 10 11
$front_start = 1;
if($this->current_page > $this->display_count){
$front_start = $this->current_page - $this->display_count;
}
for($i=$front_start;$i<$this->current_page;$i++){
echo "<a href=".$_SERVER['PHP_SELF']."?page=".$i.">[".$i ."]</a> ";
}
echo "[".$this->current_page."]";
$displayCount = $this->display_count;
if($this->total_pages > $displayCount&&($this->current_page+$displayCount)<$this->total_pages){
$displayCount = $this->current_page+$displayCount;
}else{
$displayCount = $this->total_pages;
}
for($i=$this->current_page+1;$i<=$displayCount;$i++){
echo "<a href=".$_SERVER['PHP_SELF']."?current_page=".$i.">[".$i ."]</a> ";
}
//生成導航鏈接
if ($this->current_page > 1) {
echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->first.">首頁</A>|";
echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->prev.">上一頁</A>|";
}
if( $this->current_page < $this->total_pages) {
echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->next.">下一頁</A>|";
echo "<A href=".$_SERVER['PHP_SELF']."?current_page=".$this->last.">末頁</A>";
}
echo "</form>";
echo "</div>";
}
}
?>
調用:
<?php
include_once("../config_jj/sys_conf.inc");
include_once("../PageSupportClass.php");//分頁類
include_once('../Smarty_JsnhClass.php');
$smarty = new Smarty_Jsnh();
include_once("../include/Smarty_changed_dir.php");
$smarty->assign('title', "Smarty新聞分頁測試");
<?php
$pageSupport = new PageSupport($PAGE_SIZE); //實例化PageSupport對象
$current_page=$_GET["current_page"];//分頁當前頁數
if (isset($current_page)) {
$pageSupport->__set("current_page",$current_page);
} else {
$pageSupport->__set("current_page",1);
}
?>
$pageSupport->__set("sql","select * from news ");
$pageSupport->read_data();//讀數據
if ($pageSupport->current_records > 0) //如果數據不為空,則組裝數據
{
for ($i=0; $i<$pageSupport->current_records; $i++)
{
$title = $pageSupport->result[$i]["title"];
$id = $pageSupport->result[$i]["id"];
$news_arr[$i] = array('news' => array('id' => $id,'title' => $title));
}
}
//關閉數據庫
mysql_close($db);
$pageinfo_arr = array(
'total_records' => $pageSupport->total_records,
'current_page' => $pageSupport->current_page,
'total_pages' => $pageSupport->total_pages,
'first' => $pageSupport->first,
'prev' => $pageSupport->prev,
'next' => $pageSupport->next,
'last' => $pageSupport->last
);
$smarty->assign('results', $news_arr);
$smarty->assign('pageSupport', $pageinfo_arr);
$smarty->display('news/list.tpl');
?>
模板list.tpl
{* I am a Smarty comment, I don't exist in the compiled output *}
{*
{$pageSupport.total_records}<br/>
{$pageSupport.current_page}<br/>
{$pageSupport.total_pages}<br/>
{$pageSupport.first}<br/>
{$pageSupport.prev}<br/>
{$pageSupport.next}<br/>
{$pageSupport.last}<br/>
*}
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gbk" />
<title>{$title}</title>
</head>
<body>
{foreach item=o from=$results}
{$o.news.id} {$o.news.title}
<br>
{foreachelse}
沒有您要查看的數據!
{/foreach}
<br/>
{if ( $pageSupport.total_records > 0 )}
<form action="" method="get">
共{$pageSupport.total_records}記錄
第{$pageSupport.current_page}頁/共{$pageSupport.total_pages}頁
{if ( $pageSupport.current_page > 1 )}
<A href=?current_page={$pageSupport.first}>首頁</A>
<A href=?current_page={$pageSupport.prev}>上一頁</A>
{/if}
{if ( $pageSupport.current_page < $pageSupport.total_pages )}
<A href=?current_page={$pageSupport.next}>下一頁</A>
<A href=?current_page={$pageSupport.last}>末頁</A>
{/if}
跳到<input type="text" size="4" name="current_page" value="{$pageSupport.current_page}"/>頁
<input type="submit" value="GO"/>
</form>
{/if}
</body>
</html>
語法,不解釋了,數據量小的時候,這麼寫,沒事。
如果數據量大呢?不是一般大,上百萬呢。
試著運行一下:
代碼如下 復制代碼SELECT id FROM users LIMIT 1000000,10
在我的電腦上,第一次運行,顯示如下:
10 rows in set (9.38 sec)
之後再運行,顯示如下:
10 rows in set (0.38 sec)
這不奇怪。MySQL對已經運行的SQL語句有緩沖,可以很快把之前的數據拿出來。
無論如何,第一次的9秒多,我實在不能接受。
換個寫法:
代碼如下 復制代碼SELECT id FROM users WHERE id>1000000 LIMIT 10;
顯示:10 rows in set (0.00 sec)
事實上,用phpMyAdmin去看,“顯示行 0 - 9 (10 總計, 查詢花費 0.0011 秒)”,之後再運行,基本都在0.0003秒左右。
百萬級優化
.對查詢進行優化,應盡量避免全表掃描,首先應考慮在 where 及 order by 涉及的列上建立索引。
2.應盡量避免在 where 子句中對字段進行 null 值判斷,否則將導致引擎放棄使用索引而進行全表掃描,如:
代碼如下 復制代碼select id from t where num is null
可以在num上設置默認值0,確保表中num列沒有null值,然後這樣查詢:
代碼如下 復制代碼select id from t where num=0
3.應盡量避免在 where 子句中使用!=或<>操作符,否則將引擎放棄使用索引而進行全表掃描。
4.應盡量避免在 where 子句中使用 or 來連接條件,否則將導致引擎放棄使用索引而進行全表掃描,如:
代碼如下 復制代碼select id from t where num=10 or num=20
可以這樣查詢:
代碼如下 復制代碼select id from t where num=10
union all
select id from t where num=20
5.in 和 not in 也要慎用,否則會導致全表掃描,如:
代碼如下 復制代碼 select id from t where num in(1,2,3)
對於連續的數值,能用 between 就不要用 in 了:
代碼如下 復制代碼 select id from t where num between 1 and 3
6.下面的查詢也將導致全表掃描:
代碼如下 復制代碼 select id from t where name like '%abc%'
分類函數
代碼如下 復制代碼$db=dblink();
$db->pagesize=20;
$sql=”select id from collect where vtype=$vtype”;
$db->execute($sql);
$strpage=$db->strpage(); //將分頁字符串保存在臨時變量,方便輸出
while($rs=$db->fetch_array()){
$strid.=$rs['id'].’,';
}
$strid=substr($strid,0,strlen($strid)-1); //構造出id字符串
$db->pagesize=0; //很關鍵,在不注銷類的情況下,將分頁清空,這樣只需要用一次數據庫連接,不需要再開;
$db->execute(“select id,title,url,sTime,gTime,vtype,tag from collect where id in ($strid)”);
<?php while($rs=$db->fetch_array()): ?>
<tr>
<td> <?php echo $rs['id'];?></td>
<td> <?php echo $rs['url'];?></td>
<td> <?php echo $rs['sTime'];?></td>
<td> <?php echo $rs['gTime'];?></td>
<td> <?php echo $rs['vtype'];?></td>
<td> <a href=”?act=show&id=<?php echo $rs['id'];?>” target=”_blank”><?php echo $rs['title'];?></a></td>
<td> <?php echo $rs['tag'];?></td>
</tr>
<?php endwhile; ?>
</table>
<?php
echo $strpage;