At 11:04 AM 5/2/2002, you wrote: >Hi Again... !! > > > 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. > >I'm a bit confused now... >You are building a "pager" to a webpage, right ??
Correct >But, if You select with LIMIT 100 then I assume that >You want to show 100 records per page... >If that's the case then You propably need to fetch >the count of ALL matching rows after all. Nope. :) The table could be in excess of 1 million rows. The user sees just the first 100 rows no matter what. If he wants to see a different group of records he can use the search fields at the top of the form. I want the web server to serve as many pages/sec as possible and limiting the # of rows returned to 100 (or less) is the best way to do it. If everyone started to do 1 million row queries, then it won't support many users. The mysql_num_rows() should work. I looked for something like this last night in the manual but couldn't find it. All I found was "mysql_affected rows" which of course only works for updates. I'm glad they have an equivalent for the Select statement. > > 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>). > >You can call mysql_num_rows() directly after query BEFORE you >loop through the recordset. (if it was that You ment with "delay"...) Yup Mike >---- Original Message ----- >Date: 2-May-2002 17:22:44 +0200 >From: mos <[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]> >Subject: RE: Re: How to Count(*) with LIMIT > > > 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 > > > > > >--------------------------------------------------------------------- >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 --------------------------------------------------------------------- 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