Note: The code below will not work on all environments. I’m using php 5.2.6 with mysql driver 5.0.18.
In a previous post:
http://www.joeyrivera.com/2009/using-mysql-stored-procedures-with-php-mysqlmysqlipdo/
I explained how to use MySQL stored procedures with different db adapters such as mysql, mysqli, and pdo in PHP. In those examples, I demonstrated calling a sp with in/out variables and calling a stored procedure that returns a recordset. One of the comments I received was a person asking how to call a stored procedure that uses in/out parameters as well as returns a recordset. It’s not much different and here’s how.
The trick is to combine both methods in one. Here’s an example of what the stored procedure looks like:
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`get_users` $$
CREATE PROCEDURE `get_users`(
IN firstName VARCHAR(100),
OUT totalUsers INT
)
BEGIN
SELECT COUNT(*)
INTO totalUsers
FROM users
WHERE first_name = firstName;
SELECT *
FROM users
WHERE first_name = firstName;
END $$
DELIMITER ;
Notice there are two statements in the body of this stored procedure. The first select count(*) statement counts the total number of people who’s first name is equal to the in variable firstName. Once it’s gets the count, it sets the out variable totalUsers to that value.
The next statement is a simple select. This will select all fields for users who’s first name is equal to the in variable firstName and return the recordset. So by calling this stored procedure and passing in two parameters (first name, total), a recordset will be returned and an out variable will be set – that can then be queried.
I’m using the same users table from my previous post on stored procedures. Click on the link above to access that post and copy the create statement. I’m using a bit more data on the users table this time for this example to work. Here’s the insert statement:
INSERT INTO users
VALUES (null, 'Joey', 'Rivera'), (null, 'John', 'Doe'), (null, 'Joey', 'Tester'),
(null, 'Joey', 'Test'), (null, 'Billy', 'Bob');
The code is pretty simple, pretty much you want to do two queries. The first calls the stored procedure and passes in both variables. This first query will return a recordset (the second select statement in the body of the stored procedure. The next query will select totalUsers to get that variable and display it. Here’s the php code to do this in both mysql and pdo_mysql:
MySQL
$mysql = mysql_connect('localhost', 'test', 'test', false, 65536);
mysql_select_db('test', $mysql);
$rs = mysql_query("call get_users('joey', @total);");
while($row = (mysql_fetch_assoc($rs)))
{
print_r($row);
echo '<br />';
}
// now get the @total var
$rs2 = mysql_query("select @total;");
$total = mysql_fetch_assoc($rs2);
print_r($total);
PDO
$pdo = new PDO('mysql:dbname=test;host=127.0.0.1', 'test', 'test');
$data = $pdo->query("call get_users('joey', @totalUsers);")->fetchAll(PDO::FETCH_ASSOC);
$total_count = $pdo->query("select @totalUsers;")->fetchAll(PDO::FETCH_ASSOC);
print_r($data);
echo '<br />';
print_r($total_count);
This code should output:
MySQL
Array ( [users_id] => 6 [first_name] => Joey [last_name] => Rivera )
Array ( [users_id] => 8 [first_name] => Joey [last_name] => Tester )
Array ( [users_id] => 9 [first_name] => Joey [last_name] => Test )
Array ( [@total] => 3 )
PDO
Array ( [0] => Array ( [users_id] => 6 [first_name] => Joey [last_name] => Rivera ) [1] => Array ( [users_id] => 8 [first_name] => Joey [last_name] => Tester ) [2] => Array ( [users_id] => 9 [first_name] => Joey [last_name] => Test ) )
Array ( [0] => Array ( [@totalUsers] => 3 ) )
Like I mentioned before, both sets of code are doing the same thing, calling two queries. First the stored procedure (passing in joey for firstname and @total as the out var) then query for the out variable @total. I took a few shortcuts with pdo so it seems like less code. I personally like pdo better and it’s what I use on my projects. I hope you all find this helpful and feel free to ask any question or post any comments.
Thanks for your help!
I run your code but didn’t get back @total. What is wrong??? I created SP and pass your code to php and I can not get value of OUT parameter Total.
My code same like your:
******************************************
print ‘MYSQL: calling sp with out variables’;
$rs = mysql_query(“call get_users(‘joey’, @total);”);
while($row = (mysql_fetch_assoc($rs)))
{
print_r($row);
echo ‘ ‘;
}
echo ‘ GET TOTAL‘;
// now get the @total var
$rs2 = mysql_query(“select @total;”);
$total = mysql_fetch_assoc($rs2);
print_r($total);
print ‘MYSQL: END’;
*********************************
And here is result from php page.
*********************************************************
MYSQL: calling sp with out variables
Array ( [users_id] => 1 [first_name] => Joey [last_name] => Rivera )
Array ( [users_id] => 7 [first_name] => Joey [last_name] => Rivera )
Array ( [users_id] => 9 [first_name] => Joey [last_name] => Tester )
Array ( [users_id] => 10 [first_name] => Joey [last_name] => Test )
GET TOTAL
MYSQL: END
*******************************************************
What is missing????
Also, how I can see result of this SP from mySQL.
In MySQL Query Browser, which is the tool I use, open a ‘Resultset Tab’ and type:
call get_users(‘joey’, @total);
select @total;
Now I click on the ‘Start Transaction’ button (since it’s two queries where the second relies on the first you need to start a transaction else the variable set in your first query will be lost before running the second query). Then I select the first query and hit execute – this returns the recordset. Now I select the second query and hit execute – this returns the variable @total. Let me know if you are seeing this on your database end.
Dear Joey!
Could you please give us code on php. I still can not get @Total. Also I am using dbForge Studio Express for MySQL tools for test SP. If I execute this SP then I get all records for second select, and value = 4 for count all first_name = ‘Joey’. How I can get this value from php code???
Valentina, I just tried this code on another machine and total is not returning as well. I’ll have to compare both machine setups to see what’s different to pin-point this issue. I assume it may have something to do with how php is setup. What version of php are you running?
So here’s what I did. I tried my code on another machine with PHP 5.2.6 and this is what I saw:
Array ( [users_id] => 1 [first_name] => Joey [last_name] => Rivera )
Array ( [users_id] => 3 [first_name] => Joey [last_name] => Tester )
Array ( [users_id] => 4 [first_name] => Joey [last_name] => Test )
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Documents and Settings\jorivera\My Documents\htdocs\sp.php on line 15
After doing some investigation work, it seems that the mysql driver in machine a (which works) is version 5.0.18 and in machine b (didn’t work) is version 5.0.51a. I found these version numbers by checking phpinfo() on both. To see if that was the problem, I copied the working libmysql.dll file from one machine to the other and restarted Apache so they both have the same version and now this code works on both machines.
I’m glad you brought this problem up because now I’m concerned with the fact that this code doesn’t work on all environments. I’m curious to try newer versions of php to see if they bring a mysql lib that will work. I’ll make a note on the top of this post so people are aware of this and if I find a better solution I’ll let you know.
Did you try to see if the pdo code works for you?
I have PHP Version 5.2.5.
Also I found a way to get multyrecords from SP. I created Store Procedure with INPUT parameters. Under SP I created Temporary table. Then I can do anything with data that we must get (sum, max, min, count…..). Finally I selected data from Temporary table.
Here is my SP:
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
DROP PROCEDURE `get_users_2`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_users_2`(IN FName VARCHAR(100))
BEGIN
CREATE TEMPORARY TABLE TTest (
firstName VARCHAR(100),
lastName VARCHAR(100),
cnt INT default 0,
lastdate TIMESTAMP NOT NULL DEFAULT NOW()
)ENGINE=INNODB;
INSERT INTO TTest( firstName, lastName, cnt, lastdate )
SELECT first_name, last_name, 0, Now( )
FROM users
WHERE users.first_name = FName;
UPDATE TTest SET cnt = ( SELECT COUNT( * )
FROM users
WHERE users.first_name = FName ) ;
SELECT * FROM TTest;
END
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
This is working with php same like your code with SP get_users.
I am so appreciate for your help. Have a nice weekend.
Hi,
I tried to create a procedure like this
DELIMITER $$ CREATE PROCEDURE `myproc`() BEGIN SELECT* from admin; $$ END DELIMITER;
but it did not work, so tried like this
CREATE PROCEDURE `myproc`() BEGIN SELECT* from admin; END
This time i got it right, but when i try to call it, i get an error like this
PROCEDURE test.myproc can’t return a result set in the given context
I used the following code : call myproc();
Please help me how to fix this problem, is there anything to do with the version
mine is php 5.1.6
mysql 5.0.22
HK, are you getting this error on MySQL or when you run the code in PHP? Can you ‘call myproc()’ in the MySQL query browser or any db tool you use? I just checked google real quick and it could have something to do with your PHP version if that’s where you are getting your error. Here’s more info: http://bugs.php.net/bug.php?id=42548
Great article & sequel Joey. Thank you very much. My two cents on HK’s problem:
Looking at the code you wrote HK, it seems you did not use the delimiters correctly. I may be mistaken (and Joey will help us out here), but I think your code should have been as follows:
DELIMITER $$ CREATE PROCEDURE `myproc`() BEGIN SELECT* from admin; END $$ DELIMITER;
Tash, good catch, you are correct about the delimiter. The first code HK posted would result in a syntax error having ‘$$’ before the ‘END’ and not after.
If I got it right, recordset will always come first, then the variables? And everything should be done in transaction, as some other query would wipe the data?
krdr, I would have to try what you just mentioned as far as some other query wiping out the data. I assume as long as the connection is open the variables will exist but I would have to try it first. And yes, in the example above when you call the sp it will return a recordset. Then you have to run a second query to get the values of the out variables.
Hi Joey,
It’s a very very good blog and i found all procedure related queries solved.
It will be great it you can post same article related the Mysql Triggers with example and
calling them from php.
Thanks a lot
Joey Rivera, I am using Php 5.2.5, with 5.0.45, I am curious to retrieve multiple records using procedures,
My SP calling is
$sql=”CALL EMployee()”;
$res=mysql_query($sql) or die(mysql_error());
I am getting this error:
PROCEDURE test.EMployee can’t return a result set in the given context
How can I resolve this issue,
I am keenly waiting for your rep.
Regards,
Zeetarain, can you call your stored procedure directly without php and get the expected results? Maybe pasting your EMployee sp code might help recreate the problem on my end.
Hi Joey, I am trying to return a recordset with my SP. I am using uwamp and loaded mysql client api is 5.0.51a, in which you have mentioned in your comments that it will not work.
do you have any suggestion on how this can be achieve with the loaded client api? if not, then I have to find the 5.0.18
Zul,
Unfortunately in my tests I was unable to get it to work with 5.0.51a so I started to use the dll from my other machine and all worked fine then.
MySQL version 5.0.51 (all variants) was extremely buggy in a number of areas. It has long been strongly recommended to either upgrade or downgrade to get away from that version.
Gracias por publicar esta información, me fue de gran utilidad, saludos desde Monterey NL México.
Fatal error: Call to a member function fetchAll() on a non-object on line “$data = $pdo->query(“call get_users(‘joey’, @totalUsers);”)->fetchAll(PDO::FETCH_ASSOC);”.
Can you please help me, with this issue?
Hey Jatin, I think your query must not be working or not returning anything therefor causing the fetchAll to not work or be available. Double check that your stored procedure is setup correctly and you are calling and passing the variables correctly as well.
Thanks a lot. I had a hard time finding a good, non-spammy tutorial on PHP and MySQL stored procs. This article and the previous one about adding additional parameters to the function call helped me out and fixed a problem I was having. I am new to PHP but not to programming in general. Great articles.