"commands Out Of Sync . . " Mysqli Error

Databases
1

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.

2

You shouldn’t have to do multiple queries here. You should be able to prepare your record set to bring in your data once rather than create a new query for each record your looping through

3

Looking at your code, you need to first group all the prepare statements together before you execute any of them otherwise you will get an error “mysqli::prepare(): All data must be fetched before a new statement prepare takes place”. Also if you look at your code, you are performing the same SQL twice “SELECT optionname FROM pollchoices WHERE pollid = $uniqueid”.

The intention of the IF statement SQL version of code is not clear. Are you running a test to see if a row is returned? This should be handled in the WHILE loop.


$stmt = $mysqli->prepare("SELECT question, uniqueid FROM pollquestion WHERE status = 1") or die(mysqli_error($mysqli));
$stmt2 = $mysqli->prepare("SELECT optionname FROM pollchoices WHERE pollid = ?");

$stmt->execute();
$stmt->bind_result($question, $uniqueid);

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

	$stmt2->bind_param('s', $uniqueid);
	$stmt2->execute();
	$stmt2->bind_result($option);
	$found = false;

	while ($stmt2->fetch()) {
		echo "$option";
		$found = true;
	}
	if(!$found) {
		echo "ERROR: ...";
	}

	$stmt2->close();
	echo "</div>";
}
$stmt->close();