mysqli_multi_query: Calling two stored procs to bind a grid + a dropdown

[Arlington]

Hi,

 

I'm trying to make two stored proc calls with mysqli_multi_query to bind a grid and a dropdown.

 

I have followed the code example from the PHP manual, got the grid/table to bind, but I can't fetch the second result set for the dropdown.

 

Here is my code:

<?
$query  = "CALL ListAllItemOnRecord;";
$query .= "CALL GetListData('varietal');";

if ($db->multi_query($query)) {
if ($result = mysqli_store_result($db)) {
	if (mysqli_num_rows($result) > 0) {
?>
<table> 
	<thead>
		<tr>
			<th align="center">Col1</th>
			<th align="center">Col2</th>
			<th align="center">Col3</th>
		</tr>
	</thead>
	<tbody>
<?
		while($row = mysqli_fetch_assoc($result)) {
			print "<tr><td></td>";
			print "<td class=\"bodybox\">".$row['field1']."</td>";
			print "<td class=\"bodybox\">".$row['field2']."</td>";
			print "<td class=\"bodybox\">".$row['field3']."</td></tr>";
		}
	}
	$result->free();
}
?>
	</tbody>
</table>
<?
//<select name="varietal">
if (mysqli_more_results($db) && mysqli_next_result($db)) {
	echo("test1");
	if ($result = mysqli_store_result($db)) {
               while($row = mysqli_fetch_assoc($result)) {
			echo("test2");
			//echo "<option value=\"$row[1]\" $selected>$row[1]</option>";
		}
		$result->free();
	}
}
}
//</select>
?>

 

Notice how I commented out the HTML for the dropdown.  By running the code I see the grid, and then "test1" but no "test2", which leads me to think the code detected a second result set but couldn't store it.

 

I've confirmed both stored proc calls return a result set.

 

Am I doing something wrong in the code?  Can someone please shed some light?  Thanks!

[mikosiko]

First a suggestion...

 

you are mixing OO coding style as in this line

       if ($db->multi_query($query)) {

 

and Procedural style as in this line (and the rest of your code)

	if ($result = mysqli_store_result($db)) {

 

is always better to stick only with one coding style (I will re-write your second line and some other lines in this way:

	if ($result = $db->store_result()) {

...

 

now... after your first $result->free() line add this:

     $result->free();   // But I rather prefer to user $result->close() instead
     // Procedures return always an additional result sets (status)... 
     // Here I dump the status result set from the first called procedure.
     $db->next_result();

 

try

 

 

[mikosiko]

To complement my previous answer... this is the reason to use the additional $db->next_result():

 

Multiple-result processing also is required if you execute CALL statements for stored procedures. Results from a stored procedure have these characteristics:

 

• Statements within the procedure may produce result sets (for example, if it executes SELECT statements). These result sets are returned in the order that they are produced as the procedure executes.
   
  In general, the caller cannot know how many result sets a procedure will return. Procedure execution may depend on loops or conditional statements that cause the execution path to differ from one call to the next. Therefore, you must be prepared to retrieve multiple results.
   
   
  
• The final result from the procedure is a status result that includes no result set. The status indicates whether the procedure succeeded or an error occurred.

 

pay attention to the last paragraph

 

source: http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html

 

 

[Arlington]

Thank you so much mikosiko, that solved the problem.