PDO Cannot execute queries while other unbuffered queries are active

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 h开发者_如何学Pythonave 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);
}

You need to free up your connection using the PDOStatement::closeCursor() method

http://www.php.net/manual/en/pdostatement.closecursor.php

I believe

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

    $stmt->closeCursor()

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

should do it for you

I ran into this problem due to an error in my PDO connection clause. I was trying to change the timezone upon connecting:

PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8; SET time_zone = '$timezone';"

I changed it to:

PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8, time_zone = '$timezone';"

And it worked fine.

I just ran into this problem myself and the problem turned out to be the use of stacked queries. The above solution did not solve the problem.

We had this query running right before the one that triggered the error:

return $this->fquery('
  SELECT @follow_id:=COALESCE(MAX(follow_id) + 1, 0) FROM sync_delete_value; 
  INSERT INTO sync_delete_value (...)
  VALUES (%d, @follow_id, %d, "%s")',
  $val1, $val2, $val3
);

Everything resumed as usual when I changed this into:

$followId = $this->fquery('
  SELECT @follow_id:=COALESCE(MAX(follow_id) + 1, 0) FROM sync_delete_value'
);
return $this->fquery('
  INSERT INTO sync_delete_value (...)
  VALUES (%d, %d, %d, "%s")',
  $val1, $followId, $val2, $val3

);

It's sorta pseudo-code but you get the point.

If $stmt->closeCursor() does not work for you (it didn't for me), you can just unset the $stmt variable to free upp the cursor, like so:

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

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