mysqli_free_result

PHP
1

I am trying to run two mysqli_query’s on the same page. I can’t seem to get them to work even on a simple page. Below is my code (any help would be greatly appreciated). I keep getting this error message:

An error occurred in script ‘/home/content/j/w/a/jwarrenn1/html/texas-overland-outfitters/deleteme2.php’ on line 29:
mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

<ul>
<?php
$sCM = mysqli_query ($dbc, ‘CALL selectCategories_menu()’);
if (mysqli_num_rows($sCM) > 0) {
while ($row = mysqli_fetch_array($sCM, MYSQLI_ASSOC)) {
echo ‘<li>’ . $row[‘category’] . ‘</li>’;
}
}
mysqli_free_result($sCM);
?>
</ul>
<h2>This is the second list</h2>
<ul>
<?php
$r = mysqli_query ($dbc, ‘CALL selectProducts_new()’);
if (mysqli_num_rows($r) > 0) {
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
echo ‘<li>’ . $row[‘name’] . ‘</li>’;
}
} else {
echo ‘<li>Nothing to see here</li>’;
}
?>
</ul>

2

The easiest way to debug this issue would be to use the mysqli_error() function as it will give you the exact error to correct the problem.

if (!$sCM = mysqli_query ($dbc, 'CALL selectCategories_menu()')) {
    die('MySQL Error: ' . mysqli_error($dbc));
}
3

I tried putting that in the code in various places, and kept getting this response: MySQL Error: Commands out of sync; you can’t run this command now.

I don’t understand from everything I have found what this means.

4

Have a read of the following threads.

http://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now1/614741#614741

5

Thank you. I have added this code after the first CALL: $dbc->next_result();

Is that the best way to move on to the next section, or should I use:

mysqli_free_result($sCM);
unset ($sCM);
$sCM->close();

Before I run the next Stored Procedure? It seems to be working now with just the first command, I just don’t know which way is considered the best technique.

6

free only works on prepared sets I thought, you should be using close on result – and I’d suggest you stop mixing/matching the ancient procedural crutch with the proper use object namespace – the overhead’s a pig. Might also help to stop using string addition in your echo statements since that sucks more memory for nothing. (and can cause issues in procedural calls)… same goes for opening and closing php so much. (I generally consider any .php file that has more than one <?php ?> pairing in it to be rubbish) – to that end I’d also move the <ul></ul> inside the row conditionals, as you probably don’t want the UL in the code if there’s no results…

Also, if you just re-used the same variable for your result handler, it will automatically de-allocate the result set for you so you shouldn’t have to call close or free.


<?php

$result=$dbc->query('CALL selectCategories_menu()');
if ($result->num_rows>0) {
	echo '
		<ul>';
	while ($row=$result->fetch_array(MYSQLI_ASSOC)) {
		echo '
			<li>',$row['category'],'</li>';
 	}
 	echo '
 		</ul>';
}

/*
	$result->close(); shouldn't be necessary if we re-use $result
	which is also good since it means less memory overhead/thrashing
	as we re-use something that already is in the namespace
*/
 		
$result=$dbc->query('CALL selectProducts_new()');

if ($result->num_rows>0) {

	echo '
 		<h2>This is the second list</h2>
 		<ul>';
 		
	while ($row=$result->fetch_array(MYSQLI_ASSOC)) {
 		echo '
 			<li>',$row['name'], '</li>';
	}
	
	echo '
		</ul>';
		
} else {

	echo '
		<h2>Nothing to see here</h2>';
		
} 

?>

Much simpler. IF the second one bombs try adding the close back in… It shouldn’t be necessary, but mysqli can be a bit wonky on that. (part of why I prefer PDO)