question about displaying the results of a search

[Francisco]

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.

Francisco

[Jon Kraft]

Re: question about displaying the results of a search

Francisco <fran575@mailan dnews.com> wrote:
[color=blue]
> 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?[/color]

SELECT COUNT(*) AS mycount FROM games WHERE year = '1990'

Is very quick.

JOn

[thumb_42@yahoo.com]

Re: question about displaying the results of a search

Francisco <fran575@mailan dnews.com> wrote:[color=blue]
> 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.[/color]

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

[Francisco]

Re: question about displaying the results of a search

Thank you very much, I'll use the approach that Jon sugested since my db is
small, but it's good to know the other alternatives.

Francisco

[Geoff Berrow]

Re: question about displaying the results of a search

I noticed that Message-ID: <Xns94AFB671CF9 02jonjonuxcouk@ 130.133.1.4>
from Jon Kraft contained the following:
[color=blue]
>SELECT COUNT(*) AS mycount FROM games WHERE year = '1990'[/color]

As this is a PHP group shouldn't you be recommending mysql_num_rows( ) ?
:-)
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Chung Leong]

Re: question about displaying the results of a search

Uzytkownik "Francisco" <fran575@mailan dnews.com> napisal w wiadomosci
news:c3a819$24d bi3$1@ID-99245.news.uni-berlin.de...[color=blue]
> Thank you very much, I'll use the approach that Jon sugested since my db[/color]
is[color=blue]
> small, but it's good to know the other alternatives.[/color]

No! That approach is only needed when you use a brain-dead DB like MSSQL
Server. MySQL is optimized for such operation. See Geoff Berrow's post.

[Jon Kraft]

Re: question about displaying the results of a search

Geoff Berrow <blthecat@ckdog .co.uk> wrote:
[color=blue]
> I noticed that Message-ID: <Xns94AFB671CF9 02jonjonuxcouk@ 130.133.1.4>
> from Jon Kraft contained the following:
>[color=green]
>>SELECT COUNT(*) AS mycount FROM games WHERE year = '1990'[/color]
>
> As this is a PHP group shouldn't you be recommending mysql_num_rows( ) ?
>:-)[/color]

I would if it didn't return the number of rows returned by the query,
which would be 30 ;)

JOn