Total Rowcount and LIMIT

[Ben_Davies]

Hi all,

I have a small question. Is it possible to return the total number of records in a database whilst using LIMIT at the same time?

I'm using a simple SELECT statement with a LIMIT, but would also like to grab the total number of records in the database. E.G

SELECT * FROM mytable LIMIT 0, 20

I dont want to run another select without LIMIT just to grab the rowcount, but mysql_num_rows() only returns the LIMITed value (e.g. 20 in this case).

I ask this, because I'd like to display something similar to "Page 1 of 10, Record 1 to 20" on my page, and I cant calculate the limt without the total record count.

If anyone has an answer or a suggestion, I would appreciate it.

Cheers,

Ben Davies

P.S. I'm new to MySQL and PHP, having recently (I.E. this week) moved from a Windows box with Win2k and Access (urgh!) driven websites, so please be gentle with me in your reply! Us ex-MS boys arent too technical!

[nnichols]

If you try your idea you will see that it produces an error -

Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

You have to use to separate queries. I always do.

Hope this helps 😉

[Ben_Davies]

Thats a shame. Obviously it would be nice to cut down of the number of connections to the database.

Oh well, if there is no way round it..

Thanks for the prompt reply!

Cheers,

Ben