PDO MySQL call returns unbuffered queries error
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
加载中,请稍侯......
精彩评论