better way to create a page navigation function
| From: | daniel at electroteque dot org | Date: | Wed, 21 May 2003 04:05:13 +0000 |
| Subject: | better way to create a page navigation function | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-148354@lists.php.net to get a copy of this message | ||
hi there i have probably messaged this before , but i am trying to work out
the best way to create a page navigation function for splitting mysql
results into pages , i now worked out that my previous way to do something
didnt work on results over 1000 ! , as it took too damn long to get the
total rows of the results on the first page , here is the function , i am
basically getting the total rows with SQL_CALC_FOUND_ROWS on the very first
page , then on the concurrent pages the normal query is executed to save on
response time , and if i got bak to the first page there is no need to
reset the total rows as the session variable has already been set , what
i'd like to ask is , is setting and unsetting a session variable the best
way to accomplish this ? this is the quickest way i have found to achieve
this , all the rest mysql_num_rows, count(*) all require heavy sql :\
i had also just noticed a bug when going to other scripts using navigation
its getting the total rows from the previous page i dont particularly know
where to unset the session variable on this one :\
function page_query($per_page, $query, $page, $debug=null) {
if(!$page) {
$this->page = 1;
$this->start = 0;
} else {
$this->page = $page;
$this->start = ($this->page - 1) * $per_page;
}
if (!$page && !$_SESSION['total_rows']) {
$query = preg_replace("/SELECT|select/","\\0
SQL_CALC_FOUND_ROWS",$query);
$query = "$query LIMIT $this->start, $per_page";
$result = $this->query($query);
if ($debug) $this->debug();
$row = $this->getOne("SELECT FOUND_ROWS() as
total_rows");
$_SESSION['total_rows'] = $row['total_rows'];
$this->total = $_SESSION['total_rows'];
} else {
$query = "$query LIMIT $this->start, $per_page";
$result = $this->query($query);
if ($debug) $this->debug();
$this->total = $_SESSION['total_rows'];
}
$this->pages = ceil($this->total / $per_page);
return $result;
}