PDO MySQL call returns unbuffered queries error

Question

I have researched online but most examples or instructions don't seem to apply to what I am trying to accomplish.

In short my code should accomplish the following:

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.

My script below returns this error:

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.

Code:

<body>
<table border='0' cellpadding='0' cellspacing='1'>
    <thead>
        <tr bgcolor='#E0EBF1'>
            <th>Agent NO</th>
            <th>Comm Lvl</th>
            <th>Agent Name</th>
            <th>Address</th>
            <th>parent_agent_name</th>
            <th>Contacts</th>
            <th>45 Day</th>
            <th>STS</th>
        </tr>
    </thead>
    <tbody>

<?php

$agetnumber = 123456789;


    try {   
            $db = new PDO('mysql:host=localhost;dbname=DBNAME', 'USER', 'PW');
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $db->beginTransaction();

            $stmt = $db->query('CALL hier($agentnumber)');
            foreach($stmt as $row)
            {
                $sql = $db->query("SELECT AGENT FROM activity WHERE AGENT = '$row[AGTNO]");
                $foundrows = $db->query("SELECT FOUND_ROWS()")->fetchColumn();          

                echo sprintf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>",
                $row["AGTNO"], $row["AGTCOMMLVL"],
                $row["AGTFNAME"]."<br><i>Contracted: ".$row["KDATE"],
                $row["parent_agent_id"],
                $row["parent_agent_name"],
                $row["commission_level"],
                $foundrows,
                $foot);
            }           
            $db->commit();
        }

    catch (PDOException $e)
        {
            $db->rollback();
            echo $e->getMessage();
            exit;
        }

The lines affecting the code are:

$sql = $db->query("SELECT AGENT FROM activity WHERE AGENT = '$row[AGTNO]");
$foundrows = $db->query("SELECT FOUND_ROWS()")->fetchColumn();

removing them "gets rid" of the error but I am then unable to pull the $foundrows variable I need for each result row set.

Anybody ever faced this problem before?

Did you consider the instructions in the actual error message? — prodigitalson, Oct 27 '10 at 19:44
@prodigitalson - hence the first line in my question. I have searched about based on the error given but using fetchALL does not apply in terms of how my code is laid out rigth now (it can't because the loop for my rows takes place DURING the foreach). — JM4, Oct 27 '10 at 22:38
also - I have tried using the unbuffered queries by using: $db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY); — JM4, Oct 27 '10 at 22:48
Whats the difference between fetching the entire result set as an array of rows and looping over the results in a Traversable PDOStatement? There is no code you have shown which would make you use one or the other. — prodigitalson, Oct 28 '10 at 01:58
I dont think you can set the buffered query attrib via set attribute... you have to do it in the options array to the `PDO` constructor. — prodigitalson, Oct 28 '10 at 02:01

Joe Stefanelli · Answer 1 · 2010-10-27T19:05:47.717

0

Since your query doesn't contain a LIMIT, I'm not sure why you'd be using FOUND_ROWS(). Couldn't you simply SELECT COUNT(*) FROM activity WHERE AGENT = '$row[AGTNO] instead?

EDIT:

In fact, if I read between the lines a little more, I think you could get everything you need in one query. This may be over-simplified since I don't have all the details of the hier procedure, but it'd be something like:

SELECT ag.AGTNO, ag.AGTCOMMLVL, /* etc. */, count(ac.AGENT) as foundrows
    FROM agent ag
        LEFT JOIN activity ac
            on ag.AGTNO = ac.AGENT

edited Oct 27 '10 at 19:05

answered Oct 27 '10 at 18:39

Joe Stefanelli

132,803
19
237
235

@Joe - i'd run into the same issue as above regardless of the query that was made. It is an error in the way the cursor is set so my primary issue is the fact I can't make the call period (I'm assuming because the first call is still running with the foreach loop. – JM4 Oct 27 '10 at 18:43
also - it has been documented in several places that script is the proper method for PDO calls: http://stackoverflow.com/questions/460010/work-around-for-php5s-pdo-rowcount-mysql-issue – JM4 Oct 27 '10 at 18:44
@JM4: See my edited answer for a possible way to get the count in your first query. – Joe Stefanelli Oct 27 '10 at 19:04
@Joe - the count is not the issue. Please see the error posted above, foundrows or not, I have to make a query which stops at the $sql statement above before foundrows is ever touched. – JM4 Oct 27 '10 at 22:39
@JM4: What I'm trying to point out is that I think you can incorporate the activity count into your hier procedure and completely eliminate the need for the second SQL call. – Joe Stefanelli Oct 27 '10 at 23:08
@Joe - sorry if the code was not clear. The second sql call is not finished - the number of rows is irrelevant. I am using the 2nd sql call to actually pull additional data from another table within the same database. I need to run another (foreach statement after the call to the 'activity' table to capture additional data). – JM4 Nov 02 '10 at 03:38

PDO MySQL call returns unbuffered queries error

Code:

1 Answers1

Linked