-
Mar
16thWhy Does FOUND_ROWS() Keep Returning 1?
MySQL comes with the handy little function known as SQL_CALC_FOUND_ROWS which can be used in normal LIMITed SQL statements to force the DBMS to calculate the number of rows fetched had the LIMIT statement not been applied and leave the result for a later FOUND_ROWS() call to be applied.Quite useful for pagination really.
Anyhow, if you are using PHP and connecting to MySQL, you’ll sometimes find that using the FOUND_ROWS() call after running a SQL_CALC_FOUND_ROWS statement previously will continuously bring up the result of 1 – which is obviously utterly incorrect.
It turns out that there exists an acknowledged bug that if the PHP .ini setting of mysql.trace_mode is set to true, i.e. on, FOUND_ROWS() will always return 1.
So the solution? Well considering that mysql.trace_mode simply controls the display of warnings for table/index scans, non free result sets and SQL errors, you could probably just turn it off I suppose.
Related Link: http://bugs.php.net/bug.php?id=33021
- ‹ The House of C 015: That’s not Cricket!Tries and Bullseyes ›
Related Posts:
To check if a column exists in a table with SQL using a MySQL database is pretty easy, thanks to the nifty SHOW COLUMNS command. To find if a specific column exists, we simply extend the standard SHOW COLUMNS FROM statement with a LIKE parameter, which allows us to search for a specific string or for a partial s ...
To check if a table exists with SQL in a MySQL database is pretty easy, thanks to the nifty SHOW TABLES command. To find if a specific table exists, we simply extend the standard SHOW TABLES statement with a LIKE parameter, which allows us to search for a specific string or for a partial string using the standar ...
To reuse an existing result set returned from a query is actually pretty simple thanks to PHP's mysql_data_seek function and the fact that mysql_query returns a buffered result set by default. So essentially all we want to do is rewind the entire pointer right to the start in order to reuse the set. And to ...
I have a specific sequence of ID numbers and I want the result set retrieved when looking up more information on those records in the exact same sequence that I have it. This sequence is not your usual ascending or descending order, but rather in a very specific order. So how does one do this? Well the answer li ...
To find out what collation and character set your database is using turns out to be pretty trivial thanks to MySQL's SHOW VARIABLES SQL statement that reveals all the inner workings of your setup. First, select your database. If you are working from the command line: USE mydatabase Then, run the SHOW VARIABLES ...
