PDO Cannot execute queries while other unbuffered queries are active

Question

I know this has to be a simple fix and I partially understand why I am getting this error but don't know how to fix it. I've looked over the docs but can't find a solution other than using buffered queries option. I have tried that as well but it doesn't work.

The error is: PDO Cannot execute queries while other unbuffered queries are active

The error is coming from the line where I am building the $result array.

foreach($phones as $phone)
{
    $stmt = db::getInstance()->prepare("CALL phones(:phone)");
    $stmt->bindParam(':phone', $phone, PDO::PARAM_INT, 10);
    $stmt->execute();

    $result[] = db::getInstance()->query("SELECT @phone;")->fetchAll(PDO::FETCH_ASSOC);
}

NOTE: This is an identical post. My browser crashed and killed the session so I couldn't get back in to answer. Can someone please delete the other post?

Answer 1

Let's take an example table

CREATE TABLE foo (
  id int auto_increment,  
  phone int,  
  primary key(id),  
  unique key(phone)  
)

an example procedure

delimiter //
CREATE PROCEDURE phones(in p INT)
BEGIN
  SELECT phone FROM foo WHERE phone > p;
END//
delimiter ;

and some sample data

INSERT INTO foo (phone) VALUES (1),(2),(3),(4),(5)

Using PDOStatement it is possible to fetch the result set from the stored procedure.

$pdo = new PDO('mysql:host=localhost;dbname=test', 'localonly', 'localonly');
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$stmt = $pdo->prepare("CALL phones(:phone)");
$stmt->bindParam(':phone', $phone);
$phone = 1;
$stmt->execute();
do {
  echo "\nresult set: ";
  while($row=$stmt->fetch()) {
    echo $row['phone'], " | ";
  }
} while($stmt->nextRowset());

prints result set: 2 | 3 | 4 | 5 |

(test environment: php 5.3.1/winxp, mysql 5.1.37)

The do { } while($stmt->nextRowset()); loop probably solves your "original" problem as well.