PDO MySQL call returns unbuffered queries error_问答_开发者

PDO MySQL call returns unbuffered queries error

开发者 https://www.devze.com 2023-01-22 19:57 出处：网络

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

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?

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