PHP PDO多项选择查询始终删除最后一个行集 [英] PHP PDO multiple select query consistently dropping last rowset

问题描述

我正在经历使用PDO语句进行多项选择的错误.

I'm experiencing what appears to be a bug using a PDO statement to make multiple selects.

我正在构建一个包含许多SELECT的SQL查询，并且不管它产生多少SELECT语句，最后一个行集都会被删除.

I'm building a SQL query that has many SELECTs, and regardless of how many SELECT statements it makes, the last rowset is dropped.

这是发生的事情的截短示例

Here's a truncated example of whats happening

$pdo = /* connection stuff here */
$sql = "select 1; select 2; select 3; select 4;";
$statement = $pdo->query($sql);

do {
    $rowset = $statement->fetchAll();
    if($rowset) {               
        // Do stuff with $rowset
    }       
} while($statement->nextRowset());

执行上述操作后，成功地将1-3作为行集检索，但没有4作为行集.
我无法解释为什么会这样.使用相同的PDO对象进行任何后续查询都会导致错误:

Doing the above, 1-3 are successfully retrieved as rowsets, but 4 is not.
I cannot explain why that is the case. Making any subsequent queries with the same PDO object results in an error:

PDO::query(): SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. 
Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

上面的do ... while ...例程基于 http://us1.php.net/manual/en/pdostatement.nextrowset上nextRowset()函数的PHP文档中的内容. php"rel =" nofollow> http://us1.php.net/manual/en/pdostatement.nextrowset.php

The above do ... while ... routine was based off of what can be found in PHP's documentation on the nextRowset() function at http://us1.php.net/manual/en/pdostatement.nextrowset.php

最后致电$statement->closeCursor()似乎无效

我使用的例程要复杂得多，但是我可以确认sql的行为符合预期(通过使用PHPMyAdmin直接将其插入MySQL并使用mysqli->multi_query()运行它，这两个函数均返回预期的结果)

The routines I'm using are significantly more complex, but I can confirm that the sql behaves as expected (by plugging it into MySQL directly using PHPMyAdmin and running it using mysqli->multi_query(), both of which return the expected results)

我发现有人遇到类似的问题，并发布了一个PHP错误票证，该票证显然被标记为已修复:

I found someone who had a similar issue and issued a PHP bug ticket, which was apparently marked as fixed: https://bugs.php.net/bug.php?id=61207&edit=1

有人可以向我解释是什么导致最后一个行集被删除吗?谢谢！

Can anyone please explain to me what is causing the last rowset to be dropped? Thanks!

版本: PHP 5.4.12 ， MySQL 5.6.12

我试图通过将代码更改为...来使用MYSQL_ATTR_USE_BUFFERED_QUERY ...

Edit 1: I attempted to use MYSQL_ATTR_USE_BUFFERED_QUERY by changing the code to...

$pdo = /* connection stuff here */
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true); // added code
$sql = "select 1; select 2; select 3; select 4;";
$statement = $pdo->query($sql);

do {
    $rowset = $statement->fetchAll();
    if($rowset) {               
        // Do stuff with $rowset
    }       
} while($statement->nextRowset());

这不能解决问题

推荐答案

我认为您在使用do/while循环使事情复杂化.

I think you are over complicating things with your do/while loop.

尝试使用简单的while循环:

Try a simple while loop instead:

$pdo = /* connection stuff here */
$sql = "select 1; select 2; select 3; select 4;";
$statement = $pdo->query($sql);

while($rowset = $statement->fetchAll()){
    //do stuff

    $statement->nextRowset();
}

这将继续循环，而行集没有错误的值，该值将完全按照您的期望工作.

This will continue looping while rowset does not have a false value which should then work exactly as you expect.

这篇关于PHP PDO多项选择查询始终删除最后一个行集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！