PHP PDO multiple select query consistently dropping last rowset

Question

I'm experiencing what appears to be a bug using a PDO statement to make multiple selects. 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()); ``` 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_BUFFER…

Accepted Answer

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

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 multiple select query consistently dropping last rowset

Solution

Problem