9

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

behz4d
  • 1,819
  • 5
  • 35
  • 59

5 Answers5

12

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.

Matt Gibson
  • 37,886
  • 9
  • 99
  • 128
  • My real query is like: `SELECT members.username, stock.id FROM members JOIN stock ON members.id = stock.user_owner LIMIT 10`, where would I put the `SQL_CALC_FOUND_ROWS` in this query? – behz4d Mar 25 '14 at 08:05
  • 1
    Right after the `SELECT`: `SELECT SQL_CALC_FOUND_ROWS members.username, stock.id ...` – Matt Gibson Mar 25 '14 at 08:05
  • For those who're reading this, to get the count you'll need this after the query: `$count = mysql_query("SELECT FOUND_ROWS()"); $count = mysql_result($count , 0); echo $count;` – behz4d Mar 25 '14 at 08:48
  • But bear in mind that [the mysql_ functions are deprecated and should no longer be used](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). – Matt Gibson Mar 25 '14 at 08:53
  • I dont know why but for me `FOUND_ROWS()` returning `1`. Nothing else. `SELECT SQL_CALC_FOUND_ROWS log.suma, ...blabla... FROM log INNER JOIN kategorie ON log.id_kategorie = kategorie.id WHERE ( kategorie.kategoria LIKE "%a%" ) ORDER BY id DESC LIMIT 0, 7; SELECT FOUND_ROWS() as pocet` – Dave Aug 11 '21 at 08:03
  • As of MySQL 8.0.17 `SQL_CALC_FOUND_ROWS` query modifier is deprecated and will be removed in a future version of MySQL. – user4035 Jan 26 '23 at 15:11
1

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.

xdazz
  • 158,678
  • 38
  • 247
  • 274
1

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
Sal00m
  • 2,938
  • 3
  • 22
  • 33
0
$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.

Dinesh Babu
  • 458
  • 4
  • 17
0

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.

Marius.C
  • 700
  • 6
  • 14