Get count query results with ignoring the LIMIT statement

Question

I have a simple query as follows:

$q = "SELECT * FROM blah WHERE disabled = '0'";

Now for pagination, I need to add LIMIT to my query, so:

$q = "SELECT * FROM blah WHERE disabled = '0' LIMIT 10,20";

And still, I want to know about the number of all rows with mysql_num_rows, but in the above query it is always 10, since I'm limiting the results, so for the number of all rows I need to do the same query again without LIMIT statement.

And it's somehow stupid to run the same query twice to just get the number of all rows, anybody has a better solution?

Thanks

Answer 1

MySQL supports a FOUND_ROWS() function to find the unlimited number of rows that would have been returned from the previous limited query.

SELECT SQL_CALC_FOUND_ROWS * FROM blah WHERE disabled = '0'  LIMIT 10,20
SELECT FOUND_ROWS();

Note that (a) you need to include the SQL_CALC_FOUND_ROWS option, and (b) that this is a specific MySQL extension that won't work on another RDBMS (though they each may have their own way of doing this.)

This isn't necessarily the best way of doing things, even if it might feel like it; you still have to issue two statements, you're introducing non-standard SQL, and the actual COUNTing is likely to be a similar speed to a simple SELECT COUNT(*)... anyway. I'd be inclined to stick to the standard way of doing it, myself.

Answer 2

For pagination, you have to run a count query to get the total first.

$q = "SELECT count(*) FROM blah WHERE disabled = '0'";

Two queries are necessary.

Answer 3

You need two queries if you want to know the total results, but COUNT ignore LIMIT so, you can do something like this:

SELECT * FROM blah WHERE disabled = '0' LIMIT 10,20 // Give 10 rows 

SELECT COUNT(*) FROM blah WHERE disabled = '0' LIMIT 10,20 // Give you a count of ALL records