Page 1 of 1

Calling stored procedure with MySQLi extension fails

Posted: Mon May 25, 2009 4:06 am
by Griven
Hey all.

I've got a stored procedured in MySQL 5.1 called getposts. Below is its SQL code:

Code: Select all

 
CREATE DEFINER=`root`@`localhost` PROCEDURE `getposts`()
BEGIN
SELECT
posts.post_id,
posts.post_title,
posts.post_body,
posts.post_created,
DATE_FORMAT (posts.post_created, '%M %d, %Y at %h:%i %p') AS post_date,
users.user_firstname,
categories.cat_name
FROM posts
INNER JOIN
users
ON
posts.author_fk = users.user_id
INNER JOIN
categories
ON
posts.cat_fk = categories.cat_id;
END
 
This stored procedure executes just fine from within MySQL, and returns the expected results.

However, when I call it up with the following code, it dies every time.

Code: Select all

 
$mysqli = new mysqli('localhost','username','password','database');
 
$result = $mysqli->query("CALL getposts()") or die ('Could not run query');
 
Although, if I use the SELECT statement from my stored procedure directly in the PHP code, it works.

Has anyone else run into this sort of issue and come across a solution?

Re: Calling stored procedure with MySQLi extension fails

Posted: Mon May 25, 2009 4:18 am
by Weirdan
You need to use mysqli_multi_query for this because stored procedure call return multiple result sets.

Re: Calling stored procedure with MySQLi extension fails

Posted: Mon May 25, 2009 4:40 am
by Griven
Thanks for your quick response, Weirdan.

I changed my code to be in line with what you suggested, yet it still dies.

Code: Select all

$result = $mysqli->multi_query("CALL getposts()") or die ('Could not run query');
From the PHP.net page on mysqli_multi_query:
Executes one or multiple queries which are concatenated by a semicolon.
That leads me to believe that it is not correct command in this case. Do you have any other ideas on why this might be failing?

Re: Calling stored procedure with MySQLi extension fails

Posted: Mon May 25, 2009 4:57 am
by jayshields
Weirdan is correct, see http://us2.php.net/manual/en/mysqli.query.php#65813

You could try debugging using some methods mentioned in the notes near the one I linked to.

Re: Calling stored procedure with MySQLi extension fails

Posted: Mon May 25, 2009 5:15 am
by Griven
Got it!

It was a permissions issue. Database users need "EXECUTE" rights in order to run stored procedures.

Thank you both. :D