getting a row count before limiting rows

[andrewpasetti]

Is there a way to get a row count from a query before limiting the rows returned? For example:

Select name, count(id) from Users limit 0,20;

The query above breaks two rules:

1. I'm not sure how to count the records of the query before the limit clause it added.

2. You're not allowed to use count unless you group by something.

Can someone please suggest a way around these issues?

Thanks,

Andrew

[Lars_Berg]

In mysql it has to be two queries as I see it. One for the count and one for the data.

[tomhath]

What would you do differently that depends on the count?

[andrewpasetti]

The count is used for displaying the total records. For example:

Showing 1 - 20 of 200 (where 200 is the count).

[ultraslacker]

Yes, it has to be a separate query - but you should only do this once per result set, especially if you take an OO approach to pageable result sets. For a tutorial, go to http://www.webmasterbase.com/article.php?pid=0&aid=662