New Stored Procedure

[jwarrentx]

I am trying to run two Stored Procedure calls on the same page. I have added a new stored procedure that goes through the categories in a SuperFish menu on the top and links to the respective category pages. The other CALL would be for either the sales (in home.html) or select_products (in browse.php and shop.php). When I run this command, I get an error that reads: An error occured in script mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given.

 

When I run the two CALL commands separately, they work just fine. I believe that I need to end one before starting the other and free the resources from it. I have run the mysqli_free_result and gotten the same error. What am I doing wrong?

[Larry]

Please read the forum guidelines and then add the necessary information to your post.

[jwarrentx]

I am trying to add a SuperFish menu on all of the pages that cycles through the categories using a new stored procedure. I created this stored procedure for the drop-down menu:

 

 

DELIMITER $$

CREATE PROCEDURE selectCategories_menu()

BEGIN

SELECT categoryId, category

FROM categories

ORDER by category;

END$$

DELIMITER ;

 

After this command, I would like to be able to run the sales items on multiple pages and the products and categories on others. Unfortunately, I keep getting this error:

An error occurred in script '/home/content/j/w/a/jwarrenn1/html/texas-overland-outfitters/deleteme2.php' on line 48:

mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

 

To simplify things, I put two Stored Procedure CALLS in the same page and put the results in a list item. I made another Stored Procedure for this test:

 

 

DELIMITER $$

CREATE PROCEDURE selectProducts_new()

BEGIN

SELECT productsId, name

FROM products

ORDER by productsId;

END$$

DELIMITER ;

 

Here is the code in my page:

 

<?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>';

}

}

?>

 

</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>';

}

 

?>

 

I believe that I have to End the first Stored Procedure before I can CALL the second one. However, I have tried three things to get this to work:

 

 

mysqli_free_result($sCM);

unset ($sCM);

$sCM->close();

 

I did discover today that these two do work:

 

$dbc->next_result();

 

mysqli_next_result($dbc);

 

The first item doesn't appear to match the code on the rest of the shopping cart site however. Is this the only way, or best way to get the two Stored Procedures to work one after another?

[abigail]

I'm just trying to help you. You didn't give any of your version information and probably Larry won't answer your questions unless you do that (read the forum rules).

[jwarrentx]

I don't understand what version you would need. I am not running this on a localhost, I am running live on a website using phpMyAdmin. I believe that I am using the latest version of PHP and MySQL on the server. Those are the only versions that I can think of that might be needed. Is that correct?

[jwarrentx]

I am using:

MYSQL 5.0.77

PHP Version 5.2.14

 

Sorry for not including that before.

[Larry]

Thanks for including all the pertinent details. The problem is that the stored procedure returns not just the data but also information about the stored procedure call itself. The while loop handles all the data, but the information will be left remaining there to be handled, thereby causing the conflict when the next procedure is called. You can fetch and discard this remaining info using:

mysqli_next_result($dbc);

as you found.