SELECT FOUND_ROWS () returns 1 in mysql

Question

SELECT FOUND_ROWS () not working or not returning 1 I don't know where I am going wrong

$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

use this query

 $qry ="SELECT 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"; 

if the previous SELECT contains SQL_CALC_FOUND_ROWS, but if the previous SELECT does not contain SQL_CALC_FOUND_ROWS, FOUND_ROWS () returns the number of rows returned by this previous SELECT

Answer 2

Operator

A SELECT may include a LIMIT to limit the number of rows returned by the server to the client. In some cases, it is desirable to know how many lines the operator would have to return without LIMIT, but without restarting the operator. To get this row counter, include the SQL_CALC_FOUND_ROWS option in the SELECT , and then call FOUND_ROWS() after:

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

The second SELECT returns a number indicating how many rows will be returned by the first SELECT if it were written without the LIMIT .

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

Answer 3

The accepted answer seems to be wrong. since it suggests using found_rows () after a query with LIMIT in it.

ANSWER : this is about RETURN 1 and in some cases 0 as a result of found_rows ();

this happens when some other request is called after your first choice. especially when you use the IDE or any client to fulfill your requests, some additional “preparatory” requests are executed before and after your request.

and found_rows () will return the number of the result returned by running the LAST request on the server. which in this case is not the one we expect.

and therefore the error is RETURN 1 or 0.

Verification: You can verify this by enabling the general log on your server and running the queries. you will see a couple of additional queries related between the "first query and the query for the found row".

FIX stored procedure or old COUNT (*).

performance is reasonable, there is hardly any difference.

ADDITIONAL

if your object needs to find the total number of rows returned, then this will be fine. and using SQL_CALC_FOUND_ROWS becomes irrelevant.

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

those. at startup 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 be returned by the query if we would execute SELECT * from sometable_name; those. without limitation.

saying that

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

will give us the total number of results that the cos limit will be & lt = 10. and does not use any practical purposes, but this is NOT a mistake.

Answer 4

it should be:

 $qry ="SELECT 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); echo $total_records // display total record count 

No need to use SQL_CALC_FOUND_ROWS

Answer 5

SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations where you want to limit the number of rows returned by the query, as well as determine the number of rows in the full result set without repeating the query. An example would be a web page script that presents a paged screen containing links to pages displaying other sections of the search result. Using FOUND_ROWS() allows you to determine how many other pages are needed for the rest of the result.

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

The second SELECT returns a number indicating how many rows the first SELECT would return if it were written without a LIMIT . In the absence of the SQL_CALC_FOUND_ROWS option in the last successful SELECT , FOUND_ROWS() returns the number of rows in the result set returned by this statement. If the statement contains a LIMIT , FOUND_ROWS() returns the number of rows to the limit. For example, FOUND_ROWS() returns 10 or 60, respectively, if the instruction contains LIMIT 10 or LIMIT 50, 10 .

 $qry ="SELECT SQL_CAL_FOUND_ROWS * 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_query("SELECT FOUND_ROWS() as `found_rows`;"); echo $total_records // display total record count 

Answer 6

For general use of records simply:

  $total_records = array_shift(mysql_fetch_row(mysql_query('SELECT FOUND_ROWS()'))); 

Answer 7

You need to disable mysql.trace_mode.

Use this on every php page.

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

or you can set this in a .htaccess file

 php_value mysql.trace_mode "0" 

Here:

 <?php ini_set("mysql.trace_mode", "0"); $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); echo $total_records; ?>