Monday, September 18, 2006

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.

21 Comments:

Blogger Viifog said...

Awesome - thanks for posting this information, it's not really up anywhere else.

1:04 AM, January 22, 2007  
Blogger Brad said...

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."

6:05 PM, June 09, 2007  
Blogger Earth said...

How do i actually enable the options multiple-statements connect option or the multiple-results option.

I am using a hosted server.

12:15 AM, February 02, 2008  
Blogger starlocke said...

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.

4:54 AM, February 20, 2008  
Blogger Amenthes said...

thanks for the usefull post :) I've just solved the problem:

mysql_connect(DB_SERVER, DB_USER, DB_PASS,0,131072);

me happy

4:17 PM, February 28, 2008  
Blogger GODnSurf said...

Thanks Bob. This was very good information. Saved me a lot of heartache.

10:13 AM, April 03, 2008  
Blogger Ed said...

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

6:01 PM, May 16, 2008  
Blogger Gautam Sarkar said...

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

12:44 AM, June 19, 2008  
Blogger Sapphire Technologies said...

Thanks mate, was googling for ages and then came across your article. I dont know why this isnt documented on the php site!

1:10 AM, August 09, 2008  
Blogger amit said...

Hey Bob,
Thanks man... that really helped me alot. Thanks once again.

10:31 PM, December 02, 2008  
Blogger Joey said...

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

7:46 PM, January 27, 2009  
Blogger Amit said...

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

8:58 AM, January 29, 2009  
Blogger Joe said...

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.

2:22 PM, February 08, 2009  
Blogger Tim said...

Thanks very much for posting this info. Very helpful.

9:44 AM, May 19, 2010  
Blogger sleepybutterfly said...

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

12:27 AM, May 21, 2010  
Blogger MuthuRaj said...

Same error for me also.

10:17 PM, June 17, 2010  
Blogger nnunn said...

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

12:43 PM, June 30, 2010  
Blogger proverbian said...

@Amenthes:

thank you so much. ive been looking for this.. and bob.. you're a genius!

thanks mam! im bookmarking this.. thanks

11:28 PM, February 04, 2011  
Blogger Giyassuddin said...

This is very helpful post. It's help me lot.
Giyassuddin

1:35 PM, June 26, 2012  
Blogger Giyassuddin said...

This is very helpful post for me. It's helps me lot.

1:36 PM, June 26, 2012  
Blogger April Sharma said...

Thank you so much. Its a very valuable information. It solved my problem. I feel lucky to have found this post.Thanks once again. :)

1:33 AM, December 19, 2013  

Post a Comment

Links to this post:

Create a Link

<< Home