SELECT FOUND_ROWS() return 1 in mysql

Question

SELECT FOUND_ROWS() not working or return 1 i dont know where i do mistake

$qry ="SELECT SQL_CALC_FOUND_ROWS DISTINCT user_id, login_date
FROM login_members
WHERE (login_date BETWEEN '2012-02-13 00:00:00' AND '2013-02-13 23:59:59') 
LIMIT 0, 10";

$rs = mysql_query($qry);

$total_records = mysql_result(mysql_query("SELECT FOUND_ROWS()"),0,0);

Answer 1

The accepted answer seems to be wrong. as it suggest an use of found_rows() after a query with LIMIT in it.

ANSWER: The question is about the RETURN 1 and in some case 0 as a result of found_rows();

it happens when some other query is exectued after your first select statment. especially when you are using an IDE or some client to run your queries, some additiona 'preparational' queries are executed before and after your query.

and found_rows() will return the numer of result returned by the LAST query run on the server. which in this case is not the one we are expecting.

and hence the RETURN 1 or 0 error.

Verification: You can verify this by enabling general logging on your server, and executing the queries. you will see couple of additonal queries exectued between 'the first query and the found row query'.

FIX stored procedure, or the ver old COUNT(*).

performance wise there is hardly any difference.

ADDITIONAL

if your object is to find the total number of rows returned then it's fine. and the use of SQL_CALC_FOUND_ROWS becomes immaterial.

here is a general rule, LIMIT is not required for found rows, nor SQL_CALC_FOUND_ROWS. But three of them can be used together to give a very useful result.

ie on running something like.

SELECT  SQL_CALC_FOUND_ROWS * from some_table_name LIMIT 0,10; 
SELECT FOUND_ROWS();

We will get the number of rows that would have been returned by the query had we run SELECT * from sometable_name; ie: without the LIMIT.

having said that,

SELECT * from some_table_name LIMIT 0, 10; 
SELECT FOUND_ROWS(); 

would give us the total number of results which cos of the limit will be <=10. and doesn't server any practical purpose, but it's NOT an error.