SQL_CALC_FOUND_ROWS/FOUND_ROWS()在PHP中不起作用 [英] SQL_CALC_FOUND_ROWS / FOUND_ROWS() does not work in PHP

问题描述

我在Mysql SELECT语句中使用SQL_CALC_FOUND_ROWS，以获取没有LIMIT子句的SELECT返回的行数.

I use SQL_CALC_FOUND_ROWS in Mysql SELECT statement, to get the number of lines my SELECT would return without a LIMIT clause.

$sql = new mysqli('localhost', 'root', '');
$sql->select_db('mysql');
$s1 = $sql->query('select SQL_CALC_FOUND_ROWS * from db limit 0, 3');
$s2 = $sql->query('select FOUND_ROWS()');
if($row = $s2->fetch_row()) printf('%d/%d', $s1->num_rows, $row[0]);

在我的WinXP开发站上，它在几周内每次都返回3/0.当我从工作站使用另一个MySQL服务器时，它也返回3/0. 在另一台PC上，相同的代码运行良好，并返回正确的数字(例如，如果我在mysql.db表中有17条记录，则返回3/17).每台XP PC都具有相同的PHP/Mysql版本，并且过去在我的PC上运行良好 将Mysql Query Browser与相同的SQL查询一起使用，我会得到正确的编号.

On my WinXP dev station it return 3/0 everytime for several weeks. When I use another MySQL server from my station it return 3/0 too. On anothers PC the same code runs fine, and return the correct number (3/17 for example, if I have 17 records in mysql.db table). Every XP PC have the same PHP/Mysql version, and it ran fine in the past on my PC Using Mysql Query Browser with the same SQL queries I get the right number.

有人可以给我一个解决方案的想法，而无需重新安装所有程序吗?

Could anyone give me an idea of solution, without re-install all?

对不起，我之前的要求非常不清楚.

Sorry, my previous request was awfully unclear.

推荐答案

谢谢.

当我在mysql命令行上运行与您的示例类似的内容时，它将起作用.但是从php运行它却失败了.第二个查询必须知道"第一个查询，因此我认为以某种方式将两个查询的持久性/内存链接弄乱了.

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.

(事实证明，Wordpress使用这种类型的查询来进行分页-因此，更大的问题是，当我们移至php 5.2.6时，wordpress安装中的分页突然停止了工作……最终将其跟踪到FOUND_ROWS()).

(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()).

仅出于发布目的，以便将来可能会遇到此问题的人...对我来说，它是php设置"mysql.trace_mode"-在5.2.6中默认为"on"，而不是像"off"那样以前，由于某种原因使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.

作为修复程序"，我们可以将其放置在每个php页面中(实际上，在通用的"include"中):

As a "fix", we could either put this in every php page (actually, in a common "include"):

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

或将其添加到.htaccess:

or add this to the .htaccess:

php_value mysql.trace_mode "0"

再次感谢， 杰瑞

这篇关于SQL_CALC_FOUND_ROWS/FOUND_ROWS()在PHP中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！