Francisco <fr*****@mailandnews.com> wrote:
I have this question:
I have a simple search to a db, something like:
"select description from games where year = '1990'"
suppose I get 300 results, I would like to display this in pages of 30
results per page, so I would use "limit 0, 30" for the first page.
But this way, I don't know how many results would I have if I didn't use
"limit".
So how do I know if I have more than one page to display?, the only way I
can thing is to do the fist query without limit just to know the number of
results and the second one with "limit". But this seems like a lot of
overhead just to know the total results, is there a more efficient way to
do it?
Thank you.
It depends on how complex the query is. As someone else suggested, you
can use count(), thats fairly quick for simple queries or cases where
your database is capable of deriving the answer quickly. (this sounds
like the approach that would work best for you)
If it's a complex query (with potentially thousands of results) things
get a lot more complicated. Most of the time I see people doing a "Next"
page (use Limit 0,31 and skip over the last result to see if you have
another page)
That still has a problem if the query is _complex_ because the database
will still have to iterate over thousands of rows when it's time to do a
LIMIT 5000 .. (this is not the case where the database can use indices
and stuff, but for LIKE('%query%'), or relations where the database
would have to sort on a temporary result or something, indices might not
be usable)
If it's really complex you could cache the results of the full query and
use the cache. It all depends on what your query is and how many hoops
you're willing to go through to get at it. (cache is fast but it can
result in having large files and/or give headaches with multiple web
servers, additional bugs etc.) I've never used the cache approach with
PHP, only perl it typically involves a fork & wait for the 'n' results
to become available.
Jamie
