CODEWITHSUNDEEP
mysqlsql
behz4d
behz4d

Reputation: 1849

Get count query results with ignoring the LIMIT statement

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

Upvotes: 9

Views: 7203

Answers (5)

Matt Gibson
Matt Gibson

Reputation: 38238

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.

Upvotes: 12

Marius.C
Marius.C

Reputation: 710

There are several ways to do this,

first you can cache the total rows if you know that your data does not change alot - not a very good sollution, I don't like it at least.

secondly you may want to have a look at SQL_CALC_FOUND_ROWS but sometimes COUNT(*) is faster because first one will scan the full table while count will scan the index. It's up to you to test and use what ever you wish.

If you don't have lots of data SQL_CALC_FOUND_ROWS will be a good sollution.

Upvotes: 0

Dinesh Babu
Dinesh Babu

Reputation: 456

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

if($total > 0)
{
    $q = "SELECT * FROM blah WHERE disabled = '0' LIMIT 0,10";
}

it's better to include a pagination class and call the LIMIT and OFFSET dynamically.

Upvotes: 0

Sal00m
Sal00m

Reputation: 2916

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

Upvotes: 1

xdazz
xdazz

Reputation: 160833

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.

Upvotes: 1

Related Questions