A better way than this? (next / previous) [Archive]

View Full Version : A better way than this? (next / previous)

quadrant6

01-18-03, 15:36

Hi,

I think it's called 'database paging' in other words limiting the results to a specified # per page and dynamically creating next/previous links accordingly.

I used this on a site i created a wee while ago and accomplished it by using SQL LIMIT function

Something like:

f(!isset($page)){
$page = 1;
}

$sqlNum = "SELECT stuff
FROM table
WHERE this = that;

$resultNum = mysql_query($sqlNum);
$numRows = mysql_num_rows($resultNum);
.....

$perPage = 6;
$offset = ($page-1)*$perPage;

$sql = "SELECT stuff
FROM table
WHERE this = that LIMIT $offset,$perPage";

This means that you have to run 2 queries instead of 1. It also means that it carries out the query on each page.

It would be better if it only had the main query which iw carries out once and then somehow limits the actual output and spreads it accross the pages, perhaps some way of putting the results into arrary or caching etc.

If anyone has suggestions please let me know.
?

arfman

01-22-03, 05:16

you can mount your result in a table, save it in a session and use it whenever your are not on the first page ..

In your code, i don't understand the necessity of the first request, you get the number of rows, yes great ... but why ?

quadrant6

01-22-03, 16:49

So that It knows whether there needs to be a 'next' page or not :)

I can pass the total number which i get in thr 1st query through the querystring to the next and previous pages which means that 1st query only needs to be carried out on the first page. This is a bit of an improvement i guess.

I thought putting the results into a session could cause a performence problem due the possible size of the result set?