RE: [PHP] Additional query for number of records in table
> I've run into this before, and if you use MySQL you can do something > like this: > > SELECT SQL_CALC_FOUND_ROWS * FROM Products LIMIT $From, $To > > SELECT FOUND_ROWS() > > http://dev.mysql.com/doc/refman/5.0/en/information-functions.html > > The second query will give you the number of rows that would have been > returned without the LIMIT clause. There's no way to do it with one > query though... at least easily or quickly. > I have to test it because I use 4 tables in the query. But, I think is worth trying. At least will learn something new. :) Thanks Ray -afan > Ray > > -Original Message- > From: Brad Bonkoski [mailto:[EMAIL PROTECTED] > Sent: Friday, November 17, 2006 1:57 PM > To: [EMAIL PROTECTED] > Cc: php-general@lists.php.net > Subject: Re: [PHP] Additional query for number of records in table > > [EMAIL PROTECTED] wrote: >> hi, >> I have query to select products for specific category from DB, > something >> like: >> SELECT prod_id, prod_name,... >> FROM products >> LIMIT $From, $To >> >> where $From and $To values depend of on what page you are. let say > there >> are 100 products and I'm listing 25 products per page. for page 1 > $From=0, >> $To=24. for page 2 $From=25 and$To=49, etc. >> >> works fine. >> >> though, to calculate how many pages I have I need total number of > records. >> do I have to run first a query (something like SELECT COUNT(*) as >> NoOfRecords FROM products) and then query above or there is solution > to >> have both info using one query? >> >> as a solution, I can run a query to grab all records and then list > just 25 >> products but I think it's not so smart idea :) >> >> thanks for any help. >> >> -afan >> >> > I would say the select count(*) from ... query would be a fairly low > cost query. > Perhaps you could store off the number of rows in a session variable so > you don't have to execute the count query when you move to the next > page. > -B > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Additional query for number of records in table
I've run into this before, and if you use MySQL you can do something like this: SELECT SQL_CALC_FOUND_ROWS * FROM Products LIMIT $From, $To SELECT FOUND_ROWS() http://dev.mysql.com/doc/refman/5.0/en/information-functions.html The second query will give you the number of rows that would have been returned without the LIMIT clause. There's no way to do it with one query though... at least easily or quickly. Ray -Original Message- From: Brad Bonkoski [mailto:[EMAIL PROTECTED] Sent: Friday, November 17, 2006 1:57 PM To: [EMAIL PROTECTED] Cc: php-general@lists.php.net Subject: Re: [PHP] Additional query for number of records in table [EMAIL PROTECTED] wrote: > hi, > I have query to select products for specific category from DB, something > like: > SELECT prod_id, prod_name,... > FROM products > LIMIT $From, $To > > where $From and $To values depend of on what page you are. let say there > are 100 products and I'm listing 25 products per page. for page 1 $From=0, > $To=24. for page 2 $From=25 and$To=49, etc. > > works fine. > > though, to calculate how many pages I have I need total number of records. > do I have to run first a query (something like SELECT COUNT(*) as > NoOfRecords FROM products) and then query above or there is solution to > have both info using one query? > > as a solution, I can run a query to grab all records and then list just 25 > products but I think it's not so smart idea :) > > thanks for any help. > > -afan > > I would say the select count(*) from ... query would be a fairly low cost query. Perhaps you could store off the number of rows in a session variable so you don't have to execute the count query when you move to the next page. -B -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Additional query for number of records in table
> [EMAIL PROTECTED] wrote: >> hi, >> I have query to select products for specific category from DB, something >> like: >> SELECT prod_id, prod_name,... >> FROM products >> LIMIT $From, $To >> >> where $From and $To values depend of on what page you are. let say there >> are 100 products and I'm listing 25 products per page. for page 1 >> $From=0, >> $To=24. for page 2 $From=25 and$To=49, etc. >> >> works fine. >> >> though, to calculate how many pages I have I need total number of >> records. >> do I have to run first a query (something like SELECT COUNT(*) as >> NoOfRecords FROM products) and then query above or there is solution to >> have both info using one query? >> >> as a solution, I can run a query to grab all records and then list just >> 25 >> products but I think it's not so smart idea :) >> >> thanks for any help. >> >> -afan >> >> > I would say the select count(*) from ... query would be a fairly low > cost query. > Perhaps you could store off the number of rows in a session variable so > you don't have to execute the count query when you move to the next page. > -B > not bad idea at all. :) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Additional query for number of records in table
[EMAIL PROTECTED] wrote: hi, I have query to select products for specific category from DB, something like: SELECT prod_id, prod_name,... FROM products LIMIT $From, $To where $From and $To values depend of on what page you are. let say there are 100 products and I'm listing 25 products per page. for page 1 $From=0, $To=24. for page 2 $From=25 and$To=49, etc. works fine. though, to calculate how many pages I have I need total number of records. do I have to run first a query (something like SELECT COUNT(*) as NoOfRecords FROM products) and then query above or there is solution to have both info using one query? as a solution, I can run a query to grab all records and then list just 25 products but I think it's not so smart idea :) thanks for any help. -afan I would say the select count(*) from ... query would be a fairly low cost query. Perhaps you could store off the number of rows in a session variable so you don't have to execute the count query when you move to the next page. -B -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
