Avoiding this PDO exception: Unable to execute queries while other unbuffered queries are active

Question

I would like to print a simple table on my three-column page, building name , tags and architecture style . If I try to get a list of building names and arch. styles arch. styles , 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 

My problem starts with restoring the first 5 tags for each building request I 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 request itself works fine, 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 suppose this is an area of the table where I print the first 5 tags per building } } $html .= "...</table>"; print $html; 

I do not experience requests, so I, although something like this, but it causes an 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 everything and look for another way to get such requests?

Answer 1

You say that you have published a simplified version of the code. Did you change anything else when you posted it here? This error usually occurs when you open several requests at the same time. For example, you call fetch() , but you do not call it until it is exhausted, and then you try to get a second request.

Judging by your code above, this should not happen because you are using fetchAll() . The usual solution to this problem is to call closeCursor() [docs] . You can try calling it after each fetchAll and see what it does.

Answer 2

in the loop, you again extract the first statement (note the

$buildings_stmt->fetchAll() ):

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

what you probably want to do is get the $tags_stmt operator?

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