php - php如何避免这里PDO异常: 无法执行其他未缓冲的查询时执行查询

  显示原文与译文双语对照的内容
81 2

我想在我的页面中打印一个包含 3列,building nametagsarchitecture style的简单表格。如果我尝试检索 building namesarch. styles 列表,则没有问题:

SELECT buildings.name, arch_styles.style_name
FROM buildings
INNER JOIN buildings_arch_styles
ON buildings.id = buildings_arch_styles.building_id
INNER JOIN arch_styles
ON arch_styles.id = buildings_arch_styles.arch_style_id
LIMIT 0, 10

我的问题开始于 retreaving,这是我刚编写的查询的每个构建的第 5个标签。

SELECT DISTINCT name
FROM tags
INNER JOIN buildings_tags
ON buildings_tags.tag_id = tags.id
AND buildings_tags.building_id = 123
LIMIT 0, 5

查询本身工作得很好,但不是我想使用它的地方:

<?php
//pdo connection allready active, i'm using mysql
$pdo_conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$sql ="SELECT buildings.name, buildings.id, arch_styles.style_name
 FROM buildings
 INNER JOIN buildings_arch_styles
 ON buildings.id = buildings_arch_styles.building_id
 INNER JOIN arch_styles
 ON arch_styles.id = buildings_arch_styles.arch_style_id
 LIMIT 0, 10";
$buildings_stmt = $pdo_conn->prepare ($sql);
$buildings_stmt->execute ();
$buildings = $buildings_stmt->fetchAll (PDO::FETCH_ASSOC);
$sql ="SELECT DISTINCT name
 FROM tags
 INNER JOIN buildings_tags
 ON buildings_tags.tag_id = tags.id
 AND buildings_tags.building_id = :building_id
 LIMIT 0, 5";
$tags_stmt = $pdo_conn->prepare ($sql);
$html ="<table>";//i'll use it to print my table
foreach ($buildings as $building) {
 $name = $building["name"];
 $style = $building["style_name"];
 $id = $building["id"];
 $tags_stmt->bindParam (":building_id", $id, PDO::PARAM_INT);
 $tags_stmt->execute ();//the problem is HERE
 $tags = $tags_stmt->fetchAll (PDO::FETCH_ASSOC);
 $html. ="... $name.. . $style";
 foreach ($tags as $current_tag) {
 $tag = $current_tag["name"];
 $html. ="... $tag.. .";//let's suppose this is an area of the table where I print the first 5 tags per building
 }
}
$html. ="...</table>";
print $html;

我对查询没有经验,因此我可以这样做,但它引发了错误:

PHP Fatal error: Uncaught exception 'PDOException' with message '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.

我该怎么做才能避免这种情况?我应该改变所有的并寻找不同的方法来获得这种查询?

时间:原作者:0个回答

107 1

你说你发布了一个简化版本的代码。你在这里发帖的时候有没有换什么东西?通常,如果同时有多个查询"打开",则会导致这里错误。例如调用 fetch(),但在它被耗尽之前不调用它,然后尝试检索第二个查询。

根据上面的代码判断,这不应该发生,因为你正在使用 fetchAll()通常,解决这个问题的方法是调用 closeCursor()[docs] 。你可以在每个 fetchAll 之后调用它,看看它是否能做。

原作者:
109 4

在循环中,你实际上正在获取 1st 语句( 请注意

$buildings_stmt->fetchAll() 调用):

$tags_stmt->execute ();
$tags = $buildings_stmt->fetchAll (PDO::FETCH_ASSOC);

你可能要做的是获取 $tags_stmt 语句?

$tags_stmt->execute ();
$tags = $tags_stmt->fetchAll (PDO::FETCH_ASSOC);
原作者:
...