Calling stored procedure with MySQLi extension fails

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Griven
Forum Contributor
Posts: 165
Joined: Sat May 09, 2009 8:23 pm

Calling stored procedure with MySQLi extension fails

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Calling stored procedure with MySQLi extension fails

Post by Weirdan »

You need to use mysqli_multi_query for this because stored procedure call return multiple result sets.
Griven
Forum Contributor
Posts: 165
Joined: Sat May 09, 2009 8:23 pm

Re: Calling stored procedure with MySQLi extension fails

Post 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?
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Calling stored procedure with MySQLi extension fails

Post 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.
Griven
Forum Contributor
Posts: 165
Joined: Sat May 09, 2009 8:23 pm

Re: Calling stored procedure with MySQLi extension fails

Post by Griven »

Got it!

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

Thank you both. :D
Post Reply