How to get the number of results MySql would have returned without limit?

Question

I have a table with a lot of data, so I retrieve it and display it one page at a time (my request is lengthy so there is no way I run it on the entire table).

But I would like to paginate the results, so I need to know what is the total number of elements in my table.

If I perform a COUNT(*) in the same request, I get the number of selected elements (in my case, 10).

If I perform a COUNT(*) on my table in a second request, the result might be wrong because of the where, join and having clauses in my main query.

What is the cleanest way to:

Retrieve the data
Know the maximum number of elements in my table for this specific request

One solution seems to be using the Mysql function FOUND_ROWS :

I tried this, as mysql_query performs one query at a time: (taken here)

$query = 'SELECT SQL_CALC_FOUND_ROWS * FROM Users';
$result = mysql_query($query);
// fetching the results ...

$query = 'SELECT FOUND_ROWS()';
$ result = mysql_query($query);

// debug
while ($row = mysql_fetch_row($result)) {
    print_r($row);
}

And I got an array with 0 results:

Array ( [0] => 0 )

Whereas my query does returns results.

What is wrong with my approach ? Do you have a better solution ?

You did not store the results to $result variable anywhere ... — Vafliik, Oct 09 '12 at 14:48
@MihaiIorga mysql_num_rows is affected by the LIMIT clause. In my case, it returns 10. — Benjamin Crouzier, Oct 09 '12 at 15:05

score 0 · Answer 1 · answered Oct 09 '12 at 14:49

Set mysql.trace_mode to Off if it is On.

ini_set('mysql.trace_mode','Off'); may also work depending on your host configuration if you cannot edit my.cnf

If that doesn't make the code you posted above work, then you will need to run the query again without LIMIT and count it that way.

score 0 · Accepted Answer · answered Oct 09 '12 at 14:57

0

The code above works fine. I wasn't opening the connection correctly.

Output is :

Array ( [0] => 10976 )

I am still interested for an other way to do it, especially something that is not mysql dependent.

answered Oct 09 '12 at 14:57

Benjamin Crouzier

40,265
44
171
236

MySQL is the best way to do it, it knows more about the database than anything you could do with PHP. – MatthewMcGovern Oct 09 '12 at 14:58
What choice you ever will choose, but I strongly recommend to run benchmarks on your queries. Though SQL_CALC_FOUND_ROWS looks nice, but mostly it's a bad choice regarding performance. See [http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows] – Christopher Will Oct 09 '12 at 15:08

How to get the number of results MySql would have returned without limit?

2 Answers2