PHP's PDO Prepare Method Fails While in a Loop

Question

Given the following code:

// Connect to MySQL up here

$example_query = $database->prepare('SELECT * FROM table2');
if ($example_query === false) die('prepare failed');

$query = $database->prepare('SELECT * FROM table1');
$query->execute();
while ($results = $query->fetch())
{
    $example_query = $database->prepare('SELECT * FROM table2');
    if ($example_query === false) die('prepare failed'); //it will die here
}

I obviously attempt to prepare statements to SELET everything from table2 twice. The second one (the one in the WHILE loop) always fails and causes an error.

This is only on my production server, developing locally I have no issues so it must be some kind of setting somewhere.

My immediate thought is that MySQL has some kind of max_connections setting that is set to 1 and a connection is kept open until the WHILE loop is completed so when I try to prepare a new query it says "nope too many connected already" and shits out.

Any ideas?

EDIT: Yes I know there's no need to do it twice, in my actual code it only gets prepared in the WHILE loop, but like I said that fails on my production server so after some testing I discovered that a simple SELECT * query fails in the WHILE loop but not out of it. The example code I gave is obviously very watered down to just illustrate the issue.

Answer 1

There shouldn't be a need to prepare $example_query more than once. Simply execute the query inside your loop.

EDIT: If you must prepare a new query in each loop iteration, an explicit $example_query->closeCursor() at the end of the loop should free any resources associated with the statement object.

Answer 2

Problem was that I couldn't do something while an unbuffered query was running (which it was in the WHILE loop)

Solution was to add the following line to ensure buffered queries were being used:

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