blocks|key|1384067|text|好吧，这是与php+5.2.6捆绑在一起的mysql+php扩展的一个问题。Mysqli运行得很好，还有另一个php版本。很抱歉有噪音和不清楚的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1384068|如果你有同样的问题，我的建议是重新安装PHP或更改版本。|1384069|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Well, it was a problem with mysql php extension bundled with php 5.2.6. Mysqli run fine, and another php version too.
Sorry for noise and unclear question.

If you have the same problem, my advice is to re-install PHP or change version.

blocks|key|1599148|text|谢谢。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1599149|当我在mysql命令行上运行与您的示例类似的东西时，它可以工作；但是从php运行它，它失败了。第二个查询必须“知道”第一个查询，所以我认为连接这两个查询的持久性/内存被php搞得一团糟。|1599150|(事实证明，Wordpress使用这种类型的查询来进行分页-所以我们更大的问题是，当我们迁移到php+5.2.6时，wordpress安装中的分页突然停止了……最终追踪到FOUND_ROWS())。|1599151|只是为了给将来可能遇到这种情况的人发帖……对我来说，是php的设置"mysql.trace_mode“--在5.2.6中默认是"on”而不是以前的"off“，并且出于某种原因阻止了FOUND_ROWS()的工作。|1599152|作为一个“修复”，我们可以在每个php页面中(实际上，放在一个通用的“include”中)：|1599153|ini_set("mysql.trace_mode",+"0");|code-block|syntax|javascript|1599154|或者将以下内容添加到.htaccess：|1599155|php_value+mysql.trace_mode+"0"|1599156|再次感谢你，杰瑞|1599157|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Z|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|10|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|11|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|L|7|13|8|@]|9|@]|A|$M|N]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

Thank you.

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.

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

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.

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

<pre><code>ini_set("mysql.trace_mode", "0");
</code></pre>

or add this to the .htaccess:

<pre><code>php_value mysql.trace_mode "0"
</code></pre>

Thanks again,
Jerry

blocks|key|1384273|text|另一种方法是按照PHP手册中的说明使用mysqli_multi_query，方法是同时传递包含SQL_CALC_FOUND_ROWS和FOUND_ROWS的查询(用分号分隔|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1384274|<?php
$mysqli+=+new+mysqli("localhost",+"my_user",+"my_password",+"world");

/*+check+connection+*/
if+(mysqli_connect_errno())+{
++++printf("Connect+failed:+%25s\n",+mysqli_connect_error());
++++exit();
}

$query++=+"SELECT+SQL_CALC_FOUND_ROWS+*+FROM+db+limit+0,+3;";
$query+.=+"SELECT+FOUND_ROWS()";

/*+execute+multi+query+*/
if+($mysqli->multi_query($query))+{
++++do+{
++++++++/*+store+first+result+set+*/
++++++++if+($result+=+$mysqli->store_result())+{
++++++++++++while+($row+=+$result->fetch_row())+{
++++++++++++++++printf("%25s\n",+$row[0]);
++++++++++++}
++++++++++++$result->free();
++++++++}
++++++++/*+print+divider+*/
++++++++if+($mysqli->more_results())+{
++++++++++++printf("-----------------\n");
++++++++}
++++}+while+($mysqli->next_result());
}

/*+close+connection+*/
$mysqli->close();
?>|code-block|syntax|javascript|1384275|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Another way would be to use mysqli_multi_query as stated in the PHP manual by passing both queries containing SQL_CALC_FOUND_ROWS and FOUND_ROWS separated with a semicolon

<pre><code>&lt;?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
 printf("Connect failed: %s\n", mysqli_connect_error());
 exit();
}

$query = "SELECT SQL_CALC_FOUND_ROWS * FROM db limit 0, 3;";
$query .= "SELECT FOUND_ROWS()";

/* execute multi query */
if ($mysqli-&gt;multi_query($query)) {
 do {
 /* store first result set */
 if ($result = $mysqli-&gt;store_result()) {
 while ($row = $result-&gt;fetch_row()) {
 printf("%s\n", $row[0]);
 }
 $result-&gt;free();
 }
 /* print divider */
 if ($mysqli-&gt;more_results()) {
 printf("-----------------\n");
 }
 } while ($mysqli-&gt;next_result());
}

/* close connection */
$mysqli-&gt;close();
?&gt;
</code></pre>

blocks|key|1384328|text|最快的解决方案是像这样子查询您的实际查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1384329|SELECT+SQL_CALC_FOUND_ROWS+*+FROM+(SELECT+whatever+FROM+whatever+WHERE+whatever+LIMIT+whatever)+ax;+
select+FOUND_ROWS();|code-block|syntax|javascript|1384330|现在，您将获得正确的结果。我认为主要原因是SQL_CALC_FOUND_ROWS主要跟踪找到的行(即没有LIMITS的行)，而不是返回的行。|offset|length|style|CODE|1384331|entityMap^0|0|0|L|J|1G|6|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

The quickest solution is to subquery your actual query like this:

<pre><code>SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT whatever FROM whatever WHERE whatever LIMIT whatever) ax; 
select FOUND_ROWS();
</code></pre>

Now you will get the correct results. I think the main reason being that <code>SQL_CALC_FOUND_ROWS</code> mainly tracks rows found (i.e. without <code>LIMITS</code>) not rows returned.

blocks|key|1599254|text|我也有同样的问题。解决方案很愚蠢，我使用的是$wpdb->query而不是$wpdb->get_var。所以你想做的是|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1599255|$wpdb->get_var('select+FOUND_ROWS()');|code-block|syntax|javascript|1599256|如果你在使用WordPress|1599257|entityMap^0|M|C|11|E|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

I had the same issue. The solution was stupid, I was using <code>$wpdb-&gt;query</code> instead of <code>$wpdb-&gt;get_var</code>. So you want to do

<pre><code>$wpdb-&gt;get_var('select FOUND_ROWS()');
</code></pre>

if you're on WordPress

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

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

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.

SQL_CALC_FOUND_ROWS / FOUND_ROWS() does not work in PHP

服务器

WordPress

MySQL

我在Mysql SELECT语句中使用SQL_CALC_FOUND_ROWS，以获取在没有LIMIT子句的情况下SELECT将返回的行数。$sql = new mysqli('localhost', 'root', '');$sql->select_db('mysql');$s1 = $sql->query('sele...

问SQL_CALC_FOUND_ROWS / FOUND_ROWS()在PHP中不起作用
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL_CALC_FOUND_ROWS / FOUND_ROWS()在PHP中不起作用EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL_CALC_FOUND_ROWS / FOUND_ROWS()在PHP中不起作用
EN