PHP and MySQL stored procedures
Another programming entry today. 😉
Basically it isn’t really much, but it’s more or less a short description I encountered last week. Apparently this is very unclear and not well documented. So I thought I’d share it with the world. 🙂
Normally people use the Mysqli interface of PHP to invoke MySQL. Standard usage is open connection, query and then get result rows and close the connection. So you would use the functions Open, Query, Fetch_row, Close.
This scenario works for 99,9999% of the MySQL queries used in the world. However this does not work for stored procedures with multiple result sets!
The Mysqli Query function is capable of invoking the stored procedure, however if the stored procedure consists of a multiple result set it will only run the stored procedure up till the point of the first result set. The stored procedure can only be run properly by using Mysqli Multi_query function along with the Next_result function.
See also here:
http://nl2.php.net/manual/en/function.mysqli-query.php#65813
And this is how it should be solved:
http://nl2.php.net/manual/en/function.mysqli-multi-query.php
I hope this helps someone in the future. 🙂
One Reply to “PHP and MySQL stored procedures”
Apparently there is a bug in PHP which makes PHP believe there is still another recordset. This causes an infinite loop when you don’t implement it the way as indicated as stated in the multi-query example.
See also:
http://nl2.php.net/manual/en/function.mysqli-store-result.php#74478