php SELECT FOUND_ROWS() 在 mysql 中返回 1

SELECT FOUND_ROWS() return 1 in mysql

提问by okconfused

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

SELECT FOUND_ROWS() 不工作或返回 1 我不知道我在哪里做错了

$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);

采纳答案by Yogesh Suthar

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 preceding SELECT contain SQL_CALC_FOUND_ROWS, but if the preceding SELECT doesn't contain SQL_CALC_FOUND_ROWS, FOUND_ROWS() return the number of rows returned by this preceding SELECT

如果前面的 SELECT 包含 SQL_CALC_FOUND_ROWS，但如果前面的 SELECT 不包含 SQL_CALC_FOUND_ROWS，则 FOUND_ROWS() 返回此前面的 SELECT 返回的行数

回答by MortalViews

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

接受的答案似乎是错误的。因为它建议在包含 LIMIT 的查询之后使用 found_rows()。

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

答案：问题是关于 RETURN 1，在某些情况下是 0 作为 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.

在您的第一个选择语句之后执行其他一些查询时会发生这种情况。特别是当您使用 IDE 或某些客户端来运行查询时，会在查询之前和之后执行一些额外的“准备”查询。

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.

found_rows() 将返回在服务器上运行的 LAST 查询返回的结果数量。在这种情况下，这不是我们所期望的。

and hence the RETURN 1 or 0 error.

因此返回 1 或 0 错误。

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

验证：您可以通过在服务器上启用常规日志记录并执行查询来验证这一点。您将看到在“第一个查询和找到的行查询”之间执行的几个附加查询。

FIXstored procedure, or the ver old COUNT(*).

FIX存储过程，或旧的 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.

如果您的对象是要查找返回的总行数，那就没问题了。并且 SQL_CALC_FOUND_ROWS 的使用变得无关紧要。

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.

这是一般规则，找到的行不需要 LIMIT，也不需要 SQL_CALC_FOUND_ROWS。但是它们中的三个可以一起使用以产生非常有用的结果。

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.

如果我们从 sometable_name 运行 SELECT *，我们将获得查询将返回的行数；即：没有限制。

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.

将为我们提供限制的 cos <=10 的结果总数。并且没有任何实际用途，但这不是错误。

回答by Varun

A SELECTstatement may include a LIMITclause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWSoption in the SELECTstatement, and then invoke FOUND_ROWS()afterward:

一条SELECT语句可能包含一个LIMIT子句来限制服务器返回给客户端的行数。在某些情况下，希望知道在没有 LIMIT 的情况下语句将返回多少行，但无需再次运行该语句。要获得此行数，请SQL_CALC_FOUND_ROWS在SELECT语句中包含一个选项，然后再调用FOUND_ROWS()：

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

mysql> SELECT FOUND_ROWS();

The second SELECTreturns a number indicating how many rows the first SELECTwould have returned had it been written without the LIMITclause.

第二个SELECT返回一个数字，指示SELECT如果在没有LIMIT子句的情况下编写第一个将返回多少行。

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

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

回答by user2819570

You have to set mysql.trace_mode to off.

您必须将 mysql.trace_mode 设置为关闭。

Use this is in each php page.

在每个 php 页面中使用它。

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

or you can set this on .htaccess file

或者你可以在 .htaccess 文件上设置

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;
?>

回答by Devang Rathod

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

There is no need to use SQL_CALC_FOUND_ROWS

没有必要使用 SQL_CALC_FOUND_ROWS

回答by user2001117

SQL_CALC_FOUND_ROWSand FOUND_ROWS()can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS()allows you to determine how many other pages are needed for the rest of the result.

SQL_CALC_FOUND_ROWS并且FOUND_ROWS()在您想要限制查询返回的行数的情况下很有用，但也可以在不再次运行查询的情况下确定完整结果集中的行数。一个示例是一个 Web 脚本，它呈现一个分页显示，其中包含指向显示搜索结果其他部分的页面的链接。使用FOUND_ROWS()允许您确定结果的其余部分需要多少其他页面。

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

The second SELECTreturns a number indicating how many rows the first SELECTwould have returned had it been written without the LIMITclause. In the absence of the SQL_CALC_FOUND_ROWSoption in the most recent successful SELECTstatement, FOUND_ROWS()returns the number of rows in the result set returned by that statement. If the statement includes a LIMITclause, FOUND_ROWS()returns the number of rows up to the limit. For example, FOUND_ROWS()returns 10 or 60, respectively, if the statement includes LIMIT 10or LIMIT 50, 10.

第二个SELECT返回一个数字，指示SELECT如果在没有LIMIT子句的情况下编写第一个将返回多少行。如果SQL_CALC_FOUND_ROWS最近成功的SELECT语句中没有该选项，则 FOUND_ROWS()返回该语句返回的结果集中的行数。如果语句包含LIMIT子句，则FOUND_ROWS()返回达到限制的行数。例如，FOUND_ROWS()如果语句包含LIMIT 10或，则分别返回 10 或 60 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

回答by Sergey

For total records use simple:

对于总记录使用简单：

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