SQL_CALC_FOUND_ROWS / FOUND_ROWS() does not work in PHP

Question

I use SQL_CALC_FOUND_ROWS in Mysql SELECT statement, to get the number of lines my SELECT would return without a LIMIT clause.

$sql = new mysqli('localhost', 'root', '');
$sql->select_db('mysql');
$s1 = $sql->query('select SQL_CALC_FOUND_ROWS * from db limit 0, 3');
$s2 = $sql->query('select FOUND_ROWS()');
if($row = $s2->fetch_row()) printf('%d/%d', $s1->num_rows, $row[0]);

On my WinXP dev station it return 3/0 everytime for several weeks. When I use another MySQL server from my station it return 3/0 too. On anothers PC the same code runs fine, and return the correct number (3/17 for example, if I have 17 records in mysql.db table). Every XP PC have the same PHP/Mysql version, and it ran fine in the past on my PC Using Mysql Query Browser with the same SQL queries I get the right number.

Could anyone give me an idea of solution, without re-install all?

Sorry, my previous request was awfully unclear.

Answer 1

What does "does not work" mean? It fails with some error? FOUND_ROWS() reports the value with LIMIT applied? Something else? What are the MySQL server versions that are working and not working?

Answer 2

Are you using a MySQL query method that allows for multiple queries.

From MySQL documentation.

To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward

Example:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

Also just for fun, there's a great discussion about the race condition of FOUND_ROWS()'s usage here.

Answer 3

Well, it was a problem with mysql php extension bundled with php 5.2.6. Mysqli run fine, and another php version too. Sorry for noise and unclear question.

If you have the same problem, my advice is to re-install PHP or change version.

Answer 4

Hello, I just ran into the same problem. Do you have any more information that you could pass on to me? For instance, what exactly is the problem? Is the only way to fix it to upgrade php? If so, what would be a "safe" version, where the FOUND_ROWS() works as expected?

Thank you, Jerry

Answer 5

Hello, Thank you.

When I ran something analogous to your example on the mysql command line, it would work; but running it from php, it failed. The second query has to "know about" the first one, so I figure somehow that persistence/memory linking the two queries was getting messed up by the php.

(It turns out that Wordpress uses this type of query to do its pagination - so our larger problem was that the pagination in a wordpress install suddenly stopped working when we moved to php 5.2.6 ... eventually tracked it down to the FOUND_ROWS()).

Just for the sake of posting for people who may run into this in the future... for me it was the php setting "mysql.trace_mode" - this defaulted "on" in 5.2.6 instead of "off" like previously, and for some reason prevents the FOUND_ROWS() from working.

As a "fix", we could either put this in every php page (actually, in a common "include"):

ini_set("mysql.trace_mode", "0");

or add this to the .htaccess:

php_value mysql.trace_mode "0"

Thanks again, Jerry