Sql_Calc_Found_Rows/Found_Rows() Does Not Work in PHP

SQL_CALC_FOUND_ROWS / FOUND_ROWS() does not work in PHP

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

PHP PDO SQL_CALC_FOUND_ROWS / FOUND_ROWS() strange issue

Well looks like the culprit was New Relic. I disabled the daemon quick to see if that was the issue and counts are perfect again.

Found my answer here: PHP PDO returning inconsistent results for SELECT FOUND_ROWS()

I decided to wrap my queries with a single space at the beginning and end (only beginning is necessary). Note that "\n" characters work as well. This way I didn't have to mess with the New Relic configuration.

Thanks New Relic!

Error when using SQL_CALC_FOUND_ROWS()

You can use MySql's count() function to achieve what you are looking for ie.

SELECT `listing_id`, count(*) FROM (`listings`) LIMIT 100

SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() on UNION query (not UNION ALL) with LIMIT

Use temporary table for all union selects:

SELECT SQL_CALC_FOUND_ROWS * FROM 
(
  SELECT * FROM table1 WHERE [...]
  UNION
  SELECT * FROM table2 WHERE [...]
  UNION
  SELECT * FROM table3 WHERE [...]
  UNION 
  SELECT * FROM table4 WHERE [...]
)  temp_table
LIMIT 0,30

SQL_CALC_FOUND_ROWS not working

I suspect you have a syntax error, as names in SQL are not supposed to contain spaces. Try adding square brackets around [table 1], if that is the name of your table.

How to use MySQL Found_Rows() in PHP?

SQL_CALC_FOUND_ROWS is only useful if you're using a LIMIT clause, but still want to know how many rows would've been found without the LIMIT.

Think of how this works:

SELECT SQL_CALC_FOUND_ROWS * FROM Users;

You're forcing the database to retrieve/parse ALL the data in the table, and then you throw it away. Even if you aren't going to retrieve any of the rows, the DB server will still start pulling actual data from the disk on the assumption that you will want that data.

In human terms, you bought the entire contents of the super grocery store, but threw away everything except the pack of gum from the stand by the cashier.

Whereas, doing:

SELECT count(*) FROM users;

lets the DB engine know that while you want to know how many rows there are, you couldn't care less about the actual data. On most any intelligent DBMS, the engine can retrieve this count from the table's metadata, or a simple run through the table's primary key index, without ever touching the on-disk row data.

---

Related Topics