PDO Unable to execute queries while other unbuffered queries are active

Question

I know this should be a simple fix, and I partially understand why I get this error, but I don’t know how to fix it. I looked through the documents, but I can’t find a solution other than using buffered request parameters. I tried it too, but it does not work.

Error: PDO Unable to execute requests while other unbuffered requests are active

The error comes from the line where I create 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);
}

Answer 1

Take an example table

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

procedure example

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

and some data examples

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

Using PDOStatement, you can get a result set from a 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 |

( : php 5.3.1/winxp, mysql 5.1.37)

do { } while($stmt->nextRowset());, , "" .