Need Help: Calling multiple Stored Procedures

PHP
1

Hello,

  I'm having a hard time figuring out why it gives me this error

  "Commands out of sync; you can't run this command now"

  when I called 2 stored procedures.

  Here's the code


      //Stored Procedure 1
 
      $sql_result = mysqli_query("call sp1()");

      //List all users who are online
      while($row = mysqli_fetch_assoc()){
             //echo variable array row values here
      }
      //close current query here
      $sql_result->close();


       //Stored Procedure 2
 
      $sql_result2 = mysqli_query("call sp2()");

      // List all users offline/online
      while($row2 = mysqli_fetch_assoc()){
             //echo variable array row values here
      }
      //close current query here
      $sql_result2->close(); 

      //close db connection
      mysqli->close();
      

  Closing the per query should be working but unfortunately its not and I
  wonder why its not.

  This [doc page](http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html)  dont help at all nor I can't understand what it says.

   Any help is highly appreciated.
2

Can you post the code in the stored procs?

3

sp1

DELIMITER $$

DROP PROCEDURE IF EXISTS `home_database`.`building_type_list`$$

CREATE PROCEDURE `building_type_list`()
BEGIN
	select building_type_id as id, building_type_name as name, building_type_description as description, created_on as co, last_modified as lm from building_type order by building_type_name asc;
    END$$

DELIMITER ;

sp2

DELIMITER $$

DROP PROCEDURE IF EXISTS `home_database`.`building_list_find`$$

CREATE PROCEDURE `building_list_find`(bid int)
BEGIN
	SELECT building_type_id as bid, building_type_name as bn, building_type_description as bd from building_type where building_type_id=bid;
    END$$

DELIMITER ;
4

Have you checked some DB Abstraction layers like Zend_DB etc.?

5

Nope, Can I used that into a customized application that made from scratch? If so, can you post the code here. I was wondering if it will require something? ANy help is highly appreciated.

6

Nevermind, I had figure it out after reading all the mysqli class info and further my knowledge with SP. Using Zend DB is quite huge (as I can’t use the entire zend db) for me to use, So I rather use mysqli class as it gives me my needs for now. I also tested quite a huge(millions) records and its still working perfectly. If you guys need assistance on how to call multiple procedures at the same time, just let me know.

7

Why don’t you post your solution for others to benefit? I’m sure folk with a similar problem would be most appreciative. :wink: