At 03:48 AM 5/2/2002, you wrote:

> > I have a Where clause like:
> >          select count(*) from table where ....  LIMIT 100
> >
> > Unfortunately the Count(*) ignores the LIMIT clause entirely. Why?
> >
>
>Because the query returns only ONE row and LIMIT limits rows, not values.
>See ex. below:
>
>SELECT count(login) FROM accounts WHERE domain_id=1 LIMIT 3
>+--------------+
>| count(login) |
>+--------------+
>|            6 |
>+--------------+
>
> > It seems to me that if a "select * from table where ... limit 100" returns
> > between 0 and 100 rows, you should be able to count it. Instead the count
> > returns 55,000 or some ridiculously large number that has no bearing on the
> > # of rows that will actually be returned (because of the LIMIT clause).
> > Since this is running on a webserver, I don't want it to physically count
> > more than 100 rows. Some of the tables may be over 1 million rows and
> > counting that many rows when only 100 rows are returned is overkill.
>
>Then why Use COUNT if You're not interested of number of records ??
>Could You maybe specify what you actually want to do with the Count ??

It is used to set up the navigator bar buttons for the grid on the web page 
(PHP) so it says  "<<  < Page 1 of 18   >  >>" with buttons on either side 
of it.


> > Is there a way around this counting problem? The only solution I've come up
> > with is to traverse all the rows returned by counting them in a loop. This
> > seems pretty lame and I'm hoping someone can come up with a better 
> solution.
>
>If You want to know the number of rows in the recordset returned by the query
>then You should use "mysql_num_rows()". How You do this depends on the
>language been used in Your application.

This seems like the most economical way to approach it. Of course I would 
then have to delay setting up the buttons until after the query is 
executed. Right now it is done when the page first loads.  But that should 
be a trivial matter (I hope!<g>).

Thanks for everyone's input.

Mike

sql, query


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to