如何避免此PDO异常:在其他无缓冲查询处于活动状态时无法执行查询 [英] How to avoid this PDO exception: Cannot execute queries while other unbuffered queries are active

问题描述

我想在我的页面中打印一个简单的表，其中包含3列，分别为building name，tags和architecture style.如果我尝试检索building names和arch. styles的列表，则没有问题:

I'd like to print a simple table in my page with 3 columns, building name, tags and architecture style. If I try to retrieve the list of building names and arch. styles there is no problem:

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

我的问题开始于为我刚刚编写的查询的每一个建筑物撤回前5个标签.

My problem starts on retreaving the first 5 tags for every building of the query I've just wrote.

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

查询本身可以完美地工作，但在我认为要使用它的地方却不是这样:

The query itself works perfectly, but not where I thought to use it:

<?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;

我对查询没有经验，所以我虽然是这样，但是会引发错误:

I'm not experienced on queries, so i though something like this, but it throws the error:

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.

我该怎么做才能避免这种情况?我应该更改所有内容并以其他方式搜索以获得这种查询吗?

What can I do to avoid this? Should I change all and search a different way to get this kind of queries?

推荐答案

您说您发布了该代码的简化版本.在此处发布时，您还更改了其他内容吗?当您同时具有多个打开"查询时，通常会导致此错误.例如，您调用fetch()，但是直到耗尽后才调用它，然后尝试检索第二个查询.

You say that you posted a simplified version of the code. Did you change anything else when you posted it here? This error is normally caused when you have multiple queries "open" at the same time. For example, you call fetch(), but you don't call it until it's depleted, and then you try to retrieve a second query.

从上面的代码判断，这不应该发生，因为您正在使用fetchAll().通常，解决此问题的方法是调用closeCursor() [docs] .您可以尝试在每个fetchAll之后调用它，看看是否有任何作用.

Judging by your code above, this shouldn't happen because you're using fetchAll(). Normally, the solution to this problem is to call closeCursor() [docs]. You could try calling that after each fetchAll and see if that does anything.

这篇关于如何避免此PDO异常:在其他无缓冲查询处于活动状态时无法执行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！