2 queries is ok imho, but let the database do all the work ;)
The first query should be a select count (*) QUERY for a count.
The second query should be the actual QUERY.
Eg something cut 'n' pasted from some code of mine (not nice, but hey ;) )
------------------------------
$db = mysql_connect($db_domain, $db_user,$db_password);
mysql_select_db($db_databasename,$db);
$sqlwhere = " where CuisineID = $CuisineID ";
$numresults=mysql_query( "select count(*) from restaurants" . $sqlwhere);
$numrows=mysql_num_rows($numresults);
// get results
$sqlstring= "select ID, Name,LanguageID from restaurants ". $sqlwhere .
"limit $offset,$limit";
$result=@mysql_query($sqlstring,$db);
//Do stuff with $result...
// calculate number of pages needing links
$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless there is a remainder from
division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
for ($i=1;$i<=$pages;$i++) { // loop thru
$newoffset=$limit*($i-1);
print "<a href=\"$PHP_SELF?offset=$newoffset\">$i</a> \n";
}
// check to see if last page
if (!(($offset/$limit)==$pages) && $pages!=1) {
// not last page so give NEXT link
$newoffset=$offset+$limit;
print "<a href=\"$PHP_SELF?offset=$newoffset\">NEXT</a><p>\n";
}
-----Original Message-----
From: Julian Wood [mailto:[EMAIL PROTECTED]]
Sent: September 14, 2001 1:34 AM
To: php general list
Subject: Re: [PHP] limiting rows and pages like google
It's not too hard to do the next/previous through all your results, as
has been shown by several people. What is a little trickier, at least
when using mysql and trying to take advantage of the limit clause, is
how to display how many results you have (ie 1 to 10 of 107 results). If
you use the limit, you can't get the total number of rows, without doing
a second search sans limit. It's also harder to do the next link,
because you don't necessarily know if there are any more rows (imagine
you are displaying 10 rows at a time, you are on page 3 and there are 30
total rows). Conversely, if you do a single query without the limit,
then you're doing a slower search (limiting a search is way faster), and
I'm not sure of the implications of the full result set residing
serverside - maybe higher memory requirements. So what do people do to
take advantage of limit? Two queries or a single query? Any other
solutions? Any more insights on how limit works?
Julian
On Wednesday, September 12, 2001, at 10:16 PM, Adrian D'Costa wrote:
>
> Hi,
>
> I am trying to find out the the best way to do the following:
>
> I have a script that select records from a table. The problem is that I
> need to limit the rows to 20. I know that I can use limit 20. But
> what I
> want to do is give the view a link to the next 20 till all the records
> are
> shown. What I don't know is how to maintain the search query and I want
> to use the same script.
>
> The logic would be to check if the script is called the first time, then
> the sql statement would be select travel.*, city.city from travel, city
> where travel.cityid=city.id limit 0,20.
> The second time or based on what has been selected on the page (1-20,
> 21-40, etc) add that to the sql statement. My question is how?
>
> Can someone guide me or give me some example.
>
> TIA
>
> Adrian
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
--
Julian Wood
Programmer/Analyst
University of Calgary
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
