Using stored Procedures MYSQL-PHP

Usually MySQLi API fails when 2 subsiquent calls are made to a stored procedure from PHP and after much googling around and playing with code i finally found fix ...

Create procedure

delimiter $$
create procedure getLoginIds()
begin
select loginid from user order by id desc limit 100;
end$$


delimiter $$
create procedure getLatestSchedules()
begin
select * from tschedules where active=1 order by id desc limit 100;
end$$

1. 
   



2. <?php




3. 
   



4. 
   



5. $con=new mysqli('dev.host.com','uname','pwd','db');




6. 
   



7. if(mysqli_connect_errno()){




8.  die("Failed to connect");




9. }




10. 
    



11. 
    



12. $sql='call getLoginIds()';




13. 
    



14. $result=$con->query($sql);




15. if(!$result){




16.  die("failed");




17. }




18. //$result=$result1->fetch_assoc();




19. 
    



20. while($row=$result->fetch_row()){




21.   echo $row[0]."<br>";








24.  }




25.  $result->free();






27.  cleanSP($con);




28. 
    



29. //calling second sp




30. $sql='call getLatestSchedules()';




31. 
    



32. $result=$con->query($sql);




33. if(mysqli_connect_errno()){




34.  die("Failed to connect");




35. }




36. 
    



37. 
    



38. while($row=$result->fetch_row()){




39.  echo $row[0]."<br>";




40. 
    





42. }




43. cleanSP($con);




44. 
    



45. //cleans the result set in the connection




46. function cleanSP($con){




47.  while($con->next_result()){




48.  }




49. }




50. ?>