SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PDO unbuffered query

    Hey,

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

    Code:
    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.

    Code PHP:
      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

  2. #2
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    2nd Problem:

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

    PHP Code:
    while ($rowArtist $objStatement->fetch()) {
      echo 
    $rowArtist['id']; 
    it will echo all the IDs

    but if I do this

    PHP Code:
    while ($rowArtist $objStatement->fetch()) {
      while (
    $rowVotes $objStatement2->fetch()) {
        echo 
    $rowArtist['id']; 
    it will not work. Anyone know how to fix this?

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would follow the advice in the error message and use fetchAll() + foreach

  4. #4
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Gonna go a different route for the first problem.

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •