Cannot execute queries while other unbuffered queries are active

Question

Okay, so I've developed this website (amateurishly) using a local test server with PHP 5.4 (working 100%) but upon uploading to the remote host, I've had non-stop problems, likely all caused by the difference in PHP version. The remote server uses PHP 5.1. On this page, a value is passed from a previous page and this value is used to fill a form.

I've traced this problem by trial and error but have no idea what is causing it and have read at least 30 related SO questions so far. The code on this page immediately stops executing at the prepare statement. I cannot get any type of error message.

I know that the $link connection is open because I have a query preceding this that works perfectly.

if (('POST' === $_SERVER['REQUEST_METHOD']) && (isset($_POST['details'])))
{
$sql='select * from this where that=:that';
echo $sql; //added for testing
$query=$link->prepare($sql);
echo "this never shows up"; //verified problem area
$query->bindValue(':that', $_POST['details']);
$query->execute();
$row=$query->fetch();
//more things
}

All help appreciated!

EDIT: Not paying proper attention, error:

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

Very obscure error that goes away when I comment out the preceding query. This is odd because the preceding query is on several pages and has been without issue. Additionally, it returns only a single result, so I'm not sure what this error really means. Here is the preceding query:

$sql='select bgcolor from settings inner join users on id=userid where username=:user';
$query=$link->prepare($sql);
$query->bindValue(':user', $_SESSION['Username']);
$query->execute();
$row=$query->fetch();
$rgb = str_split($row['bgcolor'], 3);

It runs on every page (because I'm new and inefficient and don't save SESSION and COOKIE) and works fine before this. It only returns one result though.

I guess syntax error in the query, see Errors and error handling — dlyaza
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); And works perfectly fine on localhost. Must be a difference in PHP issue. (Last problem was filter_var not supported before 5.2 :( ) — user3720386
You edit makes it sound like there is a result waiting to be enumerated. See this answer: stackoverflow.com/a/17582620/534109 — Tieson T.
Do you have a pending query that runs before the code you've shared? — Álvaro González

user3720386 user3720386 · Accepted Answer · 2014-07-27T18:23:24

I've used $query->closeCursor();, as noted in some of the linked SO questions, for every select query executed on this page to reach working status.

This is less than ideal and I wish we could determine a real cause and solution for this problem. Unfortunately I don't have server config access to check some of the other solutions.

This also worked, but some comments state that it didn't work for them:

$link->setAttribute (PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

Cannot execute queries while other unbuffered queries are active

2 Answers