PHP and MySQL Stored Procedures
Amid recent discussions about calling MySQL stored procedures from PHP or similar API-type interfaces, and mentions of difficulties in doing so, I thought I would post some notes about my experiences. When I first tried to use stored procedures from PHP, I discovered that it worked some of the time, but I did not take the time to delve into the whys or the wherefores. It was also mentioned by some that the mysqli interface was necessary in order to properly interact with stored procedures, but I have since determined that that is in fact not the case. I now conclude that the mysql interface works with stored procedures just fine, and that is fine with me since I am perfectly at home with this interface.
The only issue appears to be when returning a resultset. It's not at all intuitive, but in order to return a resultset from a stored procedure to PHP, one must have either the multiple-statements connect option or the multiple-results option (or both) enabled. If the routine does not return a resultset, neither option is required.
Since the defined names are not available from PHP, one must use the numeric equivalents. You can grep these from mysql_com.h. I used option masks of 65536, 131072, and 199608 with equal success. I confess I do not understand why both these options perform equally well, and why a single resultset from a stored procedure evidently falls into the 'multi' category, but no matter, it works.
The only issue appears to be when returning a resultset. It's not at all intuitive, but in order to return a resultset from a stored procedure to PHP, one must have either the multiple-statements connect option or the multiple-results option (or both) enabled. If the routine does not return a resultset, neither option is required.
Since the defined names are not available from PHP, one must use the numeric equivalents. You can grep these from mysql_com.h. I used option masks of 65536, 131072, and 199608 with equal success. I confess I do not understand why both these options perform equally well, and why a single resultset from a stored procedure evidently falls into the 'multi' category, but no matter, it works.
posted by Bob Field at 3:06 PM
21 Comments:
Awesome - thanks for posting this information, it's not really up anywhere else.
Bob - So how do I actually do this? Where do I set these options?
Thanks
Brad Kelley
"The only issue appears to be when returning a resultset. It's not at all intuitive, but in order to return a resultset from a stored procedure to PHP, one must have either the multiple-statements connect option or the multiple-results option (or both) enabled. If the routine does not return a resultset, neither option is not required."
How do i actually enable the options multiple-statements connect option or the multiple-results option.
I am using a hosted server.
Viifog said...
Awesome - thanks for posting this information, it's not really up anywhere else."
The MySQL Manual just barely mentions that a look towards multi_query is needed to get a resultset out of CALL queries, in the "Stored Procedures and Functions" section (v5.0), amid the C++ API talk, which is apparently similar to PHP extensions.
thanks for the usefull post :) I've just solved the problem:
mysql_connect(DB_SERVER, DB_USER, DB_PASS,0,131072);
me happy
Thanks Bob. This was very good information. Saved me a lot of heartache.
Man I looked high and low for this information...
I found it on another site but this one corroborated it nicely. I've posted a wrap up of creating and executing stored procedures/functions/triggers on my forum and referenced your blog here:
Using PHP for MySQL Stored Procedures Functions and Triggers
Bob,
You have really posted a nice comments on this issues. It is superb. I have got ride of my problem through your this article and hope other will also get benefited from your this article.
Thanks from the bottom of my heart
Gautam Sarkar
Thanks mate, was googling for ages and then came across your article. I dont know why this isnt documented on the php site!
Hey Bob,
Thanks man... that really helped me alot. Thanks once again.
Thanks for this info, helped me solve my issue with mysql and recordsets. I created a quick tutorial on calling stored procedures with mysql, mysqli, and pdo for people using the other database extensions:
using mysql stored procedures with php mysql mysqli pdo
hi it runs perfect and gets multiple result from stored proc. but after that if i run another query using same link.
Invalid Query :2014:Commands out of sync; you can't run this command now
Thanks for this solution. I couldn't figure out for hours. I wrote all my stored procedures before I coded the php - this saves me tons of efforts.
Thanks very much for posting this info. Very helpful.
how can i call stored procedure from mysql like ths ...
$query_site = "CALL proc_displaysite($projek,$region,$negeri,$daerah,$fasa);";
$result_site = mysql_query($query_site, $db)or die(mysql_error());
$row_site = mysql_fetch_array($result_site);
$count_site = mysql_num_rows($result_site);\
i have run already this code but got an error : "PROCEDURE promis.proc_displaysite can't return a result set in the given context"
what happened..??please help me on this thing..tq
Same error for me also.
Hi Bob -- thanks for the info. I'm able to call stored proc using the method you describe.
Do you have insight into how one retrieves multiple result sets returned without resorting to Mysqli?
Looking for functionality akin to store_result(), more_result(), next_result().
@Amenthes:
thank you so much. ive been looking for this.. and bob.. you're a genius!
thanks mam! im bookmarking this.. thanks
This is very helpful post. It's help me lot.
Giyassuddin
This is very helpful post for me. It's helps me lot.
Thank you so much. Its a very valuable information. It solved my problem. I feel lucky to have found this post.Thanks once again. :)
Post a Comment
<< Home