"commands Out Of Sync . . " Mysqli Error

[cutielou22]

I been trying to figure this out for about 3 or so hours (usually I wait 3 days of trying to fix something before I post here, but I can't wait xP) . . . and I am sure - as always - someone sees a simple fix and saves the day on PHPfreaks.

 

Below is the code and details of what is happening is below.

 

$stmt = $mysqli->prepare("SELECT question, uniqueid FROM pollquestion WHERE status = 1");
$stmt->execute();
$stmt->bind_result($question, $uniqueid);

while ($stmt->fetch())
{
echo "<center><div class=tablebox><h5>$question</h5></div></center><br><div class=tablebox>";

if (!$mysqli->query("SELECT optionname FROM pollchoices WHERE pollid = $uniqueid")) {echo "Multi-INSERT failed: (" . $mysqli->errno . ") " . $mysqli->error;} else{
$stmt2 = $mysqli->prepare("SELECT optionname FROM pollchoices WHERE pollid = ?");
$stmt2->bind_param('s', $uniqueid);
mysqli_free_result();
$stmt2->execute();
$stmt2->bind_result($option);
while ($stmt2->fetch()){echo "$option";}
$stmt2->close();
}
echo "</div>";
}
$stmt->close();

 

With this I get the error "Multi-INSERT failed: (2014) Commands out of sync; you can't run this command now." I looked it up on Google and it was saying I should use multi_query. I replaced the query with multi_query and still no luck and no change in error. So I changed it back.

 

I do stuff like this a lot in my coding and never came across this error. Should I be using multi_query? I never used it before, if so - how should I be using it?

 

This is what I am trying to get to happen: I get the question for the poll and the unique id. With the unique id I can find out what poll options goes with it. That is all I want to do and I cannot get the poll options to show.

[requinix]

When you use only $stmt->execute() and $stmt->fetch() you have you fetch all the results before you can run any other queries. That's what is out of sync: running a second query while the first query isn't done sending data.

 

Fortunately all you have to do is buffer the results. Even more fortunately you don't have to do it yourself: call $stmt's store_result after you execute the query.

Edited November 6, 2012 by requinix

[PFMaBiSmAd]

And you could just JOIN your two queries, execute the one query, and loop over the results.

[cutielou22]

Adding store_result after the execute fixed the problem. I am not sure why I didn't even think to try and add that (because I usually do). Thanks for the help!