I'm wondering what the best way is to list results over a number of pages.
I first went with the idea of using the LIMIT clause, and selecting the results per page, plus 1 (to see if there's any more to show), then using a number in the LIMIT clause in the query to get the next page etc.
But then I ran into the problem of having no total results.
I could have had another query with no LIMIT, and run it only in the first page, and keep the mysql_num_rows handy in a $var. But that seems a little silly to ask a larger query just to get that.
So I then thought about using one query at the beginning, and using mysql_result($result,$currentResult,$pagesPerResult) to get the results. And then passing $result to the next page.
Only question with this is; how does the MySQL resource ID work? If I use $result ($result = mysql_query($query)), on the next page, will it use the same resource ID? Or will it be re-asking the query? Does this mean that the resource ID is only useful within the PHP script that's currently running?
I also know that mysql_result is slower than mysql_fetch_row. But I have to use mysql_result for when the user clicks the 'previous' link, to go to the previous page of results. Is this right? Any alternatives? Using LIMIT method would also work-around this aswell.
Any help or ideas would be much appreciated.
Thanks,
Justin
you could always use this query at the beginning with your LIMIT example:
SELECT COUNT(*)
FROM yourTable
WHERE your_where_statement
that query will return the number of rows. it's only a shortcut to your global query at the beginning, although it could speed things up depending on how much data is returned by a simple select.
I don't know about passing $result between pages, I might be wrong but I think all the data resulting from the query would actually get transfered between pages, could slow things down, not too sure.
hope that helps.
yoda
