PDO unbuffered query

[9three]

Hey,

I have 2 queries I'm doing that are SELECTs but I get the following error:

Array ( [0] => HY000 [1] => 2014 [2] => 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. )

I found a temporary solution by closing the Cursor on PDO but now that I'm dealing with a different situation I'm unable to use this trick.

  public function RandomFilterBox($intID) {
    $intID = (int)$intID;
 
    if (empty($intID))
      return false;
 
    $strQueryCheckRandomSettings = 'SELECT u_id, random_settings
                                    from home_settings
                                    WHERE u_id = :id';
    $objStatement = $this->DB->prepare($strQueryCheckRandomSettings);
    $objStatement->bindParam(':id', $intID, PDO::PARAM_INT);
    $objStatement->execute();
    if ($row = $objStatement->fetch()) {
      if (!empty($row['random_settings']))
        $strQueryRandom = 'AND ('.$row['random_settings'].')';
 
      $strQuery = 'SELECT id, song_file
                   FROM customers
                   WHERE type = "A"
                   AND verified = "Y"
                   '.$strQueryRandom.' 
                   ORDER BY RAND()
                   LIMIT 0, 18';
      $objStatement->closeCursor();
      $objStatement = $this->DB->prepare($strQuery);
      $objStatement->execute();
    }
    else {
      $strQuery = 'SELECT id, song_file 
                   FROM customers
                   WHERE type = "A" 
                   AND verified = "Y" 
                   ORDER BY RAND() 
                   LIMIT 0, 18';
      $objStatement = $this->DB->prepare($strQuery);
      $objStatement->execute();
    }
      $strQueryVotes = 'SELECT v_id, a_id, n_stars
                        FROM votes
                        WHERE v_id = :voter_id';
      //$objStatement->closeCursor();
      $objStatement2 = $this->DB->prepare($strQueryVotes);
      print_r($this->DB->errorInfo());
      $objStatement2->bindParam(':voter_id', $intID, PDO::PARAM_INT);
      $objStatement2->execute();
 
      $intI = 0;
      while ($rowArtist = $objStatement->fetch()) {
        while ($rowVotes = $objStatement2->fetch()) {

So I would normally do $objStatement->closeCursor() but because I'm using the available information in a while loop, I can't do that.

I've heard of certain versions of PHP cause this problem. Anyone know of any other solution?

Thanks

[9three]

2nd Problem:

When I nest 2 while loops from the top code, if I do

while ($rowArtist = $objStatement->fetch()) {
  echo $rowArtist['id']; 

it will echo all the IDs

but if I do this

while ($rowArtist = $objStatement->fetch()) {
  while ($rowVotes = $objStatement2->fetch()) {
    echo $rowArtist['id']; 

it will not work. Anyone know how to fix this?

[crmalibu]

I would follow the advice in the error message and use fetchAll() + foreach

[9three]

Gonna go a different route for the first problem.

Have you ever experienced a similar problem with my 2nd problem?