PDO MySQL呼叫传回未缓冲的查询错误 [英] 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:
从我的php脚本中调用了一个存储过程，该过程返回了我想循环通过的数据集并在表中产生行(用于在线显示).但是，表中的一个字段必须调用一个单独的表(甚至根本不需要使用存储过程)来计算受UserID影响的总行数.
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]:常规错误:2014当其他未缓冲的查询处于活动状态时，无法执行查询.考虑使用PDOStatement :: fetchAll().另外，如果您的代码只能在mysql上运行，则可以通过设置PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY属性来启用查询缓冲.
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.
代码:
<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;
}
影响代码的行是:
$sql = $db->query("SELECT AGENT FROM activity WHERE AGENT = '$row[AGTNO]");
$foundrows = $db->query("SELECT FOUND_ROWS()")->fetchColumn();
将它们消除"错误，但是我无法提取每个结果行集所需的$ foundrows变量.
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?
实际上，如果我在两行之间读得更多，我认为您可以在一个查询中获得所需的一切.因为我没有hier过程的所有详细信息，所以这可能被简化了，但它可能是这样的:
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
这篇关于PDO MySQL呼叫传回未缓冲的查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
