SQL_CALC_FOUND_ROWS / FOUND_ROWS() does not work in PHP

Solution 1:

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

Solution 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.

Solution 3:

Another way would be to use mysqli_multi_query as stated in the PHP manual by passing both queries containing SQL_CALC_FOUND_ROWS and FOUND_ROWS separated with a semicolon

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "SELECT SQL_CALC_FOUND_ROWS * FROM db limit 0, 3;";
$query .= "SELECT FOUND_ROWS()";

/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

/* close connection */
$mysqli->close();
?>