title: PDO MySQL call returns unbuffered queries error
body: <p>I have researched online but most examples or instructions don't seem to apply to what I am trying to accomplish. </p>
  
  <p>In short my code should accomplish the following:</p>
  
  <p>A stored procedure is called from my php script which returns a dataset I want to loop through and produce rows in a table (for online display purposes). One of the fields within my table, however, must call on a separate table (doesn't even need to use the stored procedure in the first place) to count the total number of rows affected by a UserID.</p>
  
  <p>My script below returns this error: </p>
  
  <blockquote>
    <p>SQLSTATE[HY000]: General error: 2014 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.</p>
  </blockquote>
  
  <h2>Code:</h2>
  
  <pre><code>&lt;body&gt;
  &lt;table border='0' cellpadding='0' cellspacing='1'&gt;
      &lt;thead&gt;
          &lt;tr bgcolor='#E0EBF1'&gt;
              &lt;th&gt;Agent NO&lt;/th&gt;
              &lt;th&gt;Comm Lvl&lt;/th&gt;
              &lt;th&gt;Agent Name&lt;/th&gt;
              &lt;th&gt;Address&lt;/th&gt;
              &lt;th&gt;parent_agent_name&lt;/th&gt;
              &lt;th&gt;Contacts&lt;/th&gt;
              &lt;th&gt;45 Day&lt;/th&gt;
              &lt;th&gt;STS&lt;/th&gt;
          &lt;/tr&gt;
      &lt;/thead&gt;
      &lt;tbody&gt;
  
  &lt;?php
  
  $agetnumber = 123456789;
  
  
      try {   
              $db = new PDO('mysql:host=localhost;dbname=DBNAME', 'USER', 'PW');
              $db-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
              $db-&gt;beginTransaction();
  
              $stmt = $db-&gt;query('CALL hier($agentnumber)');
              foreach($stmt as $row)
              {
                  $sql = $db-&gt;query("SELECT AGENT FROM activity WHERE AGENT = '$row[AGTNO]");
                  $foundrows = $db-&gt;query("SELECT FOUND_ROWS()")-&gt;fetchColumn();          
  
                  echo sprintf("&lt;tr&gt;&lt;td&gt;%s&lt;/td&gt;&lt;td&gt;%s&lt;/td&gt;&lt;td&gt;%s&lt;/td&gt;&lt;td&gt;%s&lt;/td&gt;&lt;td&gt;%s&lt;/td&gt;&lt;td&gt;%s&lt;/td&gt;&lt;td&gt;%s&lt;/td&gt;&lt;td&gt;%s&lt;/td&gt;&lt;/tr&gt;",
                  $row["AGTNO"], $row["AGTCOMMLVL"],
                  $row["AGTFNAME"]."&lt;br&gt;&lt;i&gt;Contracted: ".$row["KDATE"],
                  $row["parent_agent_id"],
                  $row["parent_agent_name"],
                  $row["commission_level"],
                  $foundrows,
                  $foot);
              }           
              $db-&gt;commit();
          }
  
      catch (PDOException $e)
          {
              $db-&gt;rollback();
              echo $e-&gt;getMessage();
              exit;
          }
  </code></pre>
  
  <p>The lines affecting the code are:</p>
  
  <pre><code>$sql = $db-&gt;query("SELECT AGENT FROM activity WHERE AGENT = '$row[AGTNO]");
  $foundrows = $db-&gt;query("SELECT FOUND_ROWS()")-&gt;fetchColumn();
  </code></pre>
  
  <p>removing them "gets rid" of the error but I am then unable to pull the $foundrows variable I need for each result row set.</p>
  
  <p>Anybody ever faced this problem before?</p>