PHP and MySQL stored procedures

March 31, 2008 in programming by banpei

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. :)