Wondering how to use stored procedures with PHP and MySQL? So was I and here’s what I’ve learned. In this tutorial I’ll explain how to use PHP (I’m using 5.2.6) to call MySQL (I’m using 5.0.2) stored procedures using the following database extensions:
- MySQL – http://php.net/manual/en/book.mysql.php
- MySQLi – http://php.net/manual/en/class.mysqli.php
- PDO – http://php.net/manual/en/class.pdo.php
First we need to setup our enviroment which consists of a new database with one table and two stored procedures. In your db tool of choice (I’ll be using the MySQL Query Browser) create a new database named test. After you create the new database, make sure to add a user called example with password example to the database and give it read access.
CREATE DATABASE `test`;
Now create the table users:
DROP TABLE IF EXISTS `test`.`users`;
CREATE TABLE `test`.`users` (
`users_id` int(10) unsigned NOT NULL auto_increment,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
PRIMARY KEY (`users_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Before we create the stored procedures, lets put some dummy data in the users table. To do that just run the following query:
INSERT INTO `test`.`users` VALUES (null, 'Joey', 'Rivera'), (null, 'John', 'Doe');
Next create the first stored procedure get_user:
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`get_user`$$
CREATE PROCEDURE `test`.`get_user`
(
IN userId INT,
OUT firstName VARCHAR(100),
OUT lastName VARCHAR(100)
)
BEGIN
SELECT first_name, last_name
INTO firstName, lastName
FROM users
WHERE users_id = userId;
END $$
DELIMITER ;
Finally create the second and last stored procedure get_users:
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`get_users`$$
CREATE PROCEDURE `test`.`get_users`()
BEGIN
SELECT *
FROM users;
END $$
DELIMITER ;
If you understand the sql above, skip this section. The first script we ran to create a database is pretty self explanitory. The second script will delete the table users if it’s already in your database then it will recreate it. The table will consist of three fields: users_id, first_name, and last_name. The insert script will create two users: ‘Joey Rivera’ and ‘John Doe’.
If stored procedures are new to you, don’t worry. They aren’t that complicated once you start playing with them. When looking at the code for the first stored procedure, drop procedure works the same way as dropping a table. First you want to check if the stored procedure is there and deleted before you recreate it. Create does just that, create the stored procedure in the database. get_user has three parameters: userId, firstName, and lastName. IN means when this stored procedure is called, this variable should be passed with a value. OUT means after the stored procedure executes, it will set the OUT variables with a value that can then be retrieved. You can also have INOUT variables but we don’t need them for this example.
The blulk of the code for the stored procedure goes in the BEGIN to END block. get_user is selecting the first and last name fields from the table users where the user id is equal to the userId variable being passed in. The other thing happening here is the two OUT variables are getting the values retrieved from the select statement. Variable firstName is set to the field first_name and lastName is being set to last_name. That’s it for get_user. get_users doesn’t have any IN nor OUT variables. When that stored procedure is executed it will return a recordset instead of variables.
Now that we have our environment set, we are ready to start our tests. Depending on what you are trying to achieve, you may be using mysql, mysqli, or PDO. I’m going to run the same tests with all three to show you the difference as well as the limitation of mysql compared to mysqli and PDO. One of the tests I’ll be running doesn’t work with mysql while all the tests work with mysqli and PDO.
The three tests will be:
- A simple select statement
- Calling stored procedure passing IN variable and retrieve OUT variables – get_user
- Calling stored procedure with no parameters and returns a recordset – get_users
Below is the code to run all three tests with each of the database extensions:
<?php
// MYSQL
$mysql = mysql_connect('localhost', 'example', 'example');
mysql_select_db('test', $mysql);
print '<h3>MYSQL: simple select</h3>';
$rs = mysql_query( 'SELECT * FROM users;' );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}
print '<h3>MYSQL: calling sp with out variables</h3>';
$rs = mysql_query( 'CALL get_user(1, @first, @last)' );
$rs = mysql_query( 'SELECT @first, @last' );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}
print '<h3>MYSQL: calling sp returning a recordset - doesn\'t work</h3>';
$rs = mysql_query( 'CALL get_users()' );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}
// MYSQLI
$mysqli = new mysqli('localhost', 'example', 'example', 'test');
print '<h3>MYSQLI: simple select</h3>';
$rs = $mysqli->query( 'SELECT * FROM users;' );
while($row = $rs->fetch_object())
{
debug($row);
}
print '<h3>MYSQLI: calling sp with out variables</h3>';
$rs = $mysqli->query( 'CALL get_user(1, @first, @last)' );
$rs = $mysqli->query( 'SELECT @first, @last' );
while($row = $rs->fetch_object())
{
debug($row);
}
print '<h3>MYSQLI: calling sp returning a recordset</h3>';
$rs = $mysqli->query( 'CALL get_users()' );
while($row = $rs->fetch_object())
{
debug($row);
}
// PDO
$pdo = new PDO('mysql:dbname=test;host=127.0.0.1', 'example', 'example');
print '<h3>PDO: simple select</h3>';
foreach($pdo->query( 'SELECT * FROM users;' ) as $row)
{
debug($row);
}
print '<h3>PDO: calling sp with out variables</h3>';
$pdo->query( 'CALL get_user(1, @first, @last)' );
foreach($pdo->query( 'SELECT @first, @last' ) as $row)
{
debug($row);
}
print '<h3>PDO: calling sp returning a recordset</h3>';
foreach($pdo->query( 'CALL get_users()' ) as $row)
{
debug($row);
}
function debug($o)
{
print '<pre>';
print_r($o);
print '</pre>';
}
?>
When you run this code you get the following results:
MYSQL: simple select
Array
(
[users_id] => 1
[first_name] => Joey
[last_name] => Rivera
)
Array
(
[users_id] => 2
[first_name] => John
[last_name] => Doe
)
MYSQL: calling sp with out variables
Array
(
[@first] => Joey
[@last] => Rivera
)
MYSQL: calling sp returning a recordset - doesn't work
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ***test.php on line 24
MYSQLI: simple select
stdClass Object
(
[users_id] => 1
[first_name] => Joey
[last_name] => Rivera
)
stdClass Object
(
[users_id] => 2
[first_name] => John
[last_name] => Doe
)
MYSQLI: calling sp with out variables
stdClass Object
(
[@first] => Joey
[@last] => Rivera
)
MYSQLI: calling sp returning a recordset
stdClass Object
(
[users_id] => 1
[first_name] => Joey
[last_name] => Rivera
)
stdClass Object
(
[users_id] => 2
[first_name] => John
[last_name] => Doe
)
PDO: simple select
Array
(
[users_id] => 1
[0] => 1
[first_name] => Joey
[1] => Joey
[last_name] => Rivera
[2] => Rivera
)
Array
(
[users_id] => 2
[0] => 2
[first_name] => John
[1] => John
[last_name] => Doe
[2] => Doe
)
PDO: calling sp with out variables
Array
(
[@first] => Joey
[0] => Joey
[@last] => Rivera
[1] => Rivera
)
PDO: calling sp returning a recordset
Array
(
[users_id] => 1
[0] => 1
[first_name] => Joey
[1] => Joey
[last_name] => Rivera
[2] => Rivera
)
Array
(
[users_id] => 2
[0] => 2
[first_name] => John
[1] => John
[last_name] => Doe
[2] => Doe
)
As you can see from the results above, mysql could not get the recordset returned by the stored procedure while mysqli and PDO could. After some more research, some people mentioned (Bob’s World, php.net) that by adding ‘false,65536’ to the end of the mysql_connect line, mysql could then get recordsets from stored procedures. I tried this and in fact it does work. So by changing
$mysql = mysql_connect('localhost', 'example', 'example');
to:
$mysql = mysql_connect('localhost', 'example', 'example',false,65536);
all the different database extensions work on all tests. So in the end, it seems all of these can work with stored procedures just as well.
Get the PHP code file: test.php
Get the DB script file: php_sp_example.sql
I hope this was helpful and feel free to leave any questions or comments.
EDIT: I have made a new post about doing the above but with a stored procedure that has in/out params as well as returns a recordset. Post at: Using MySQL stored procedures with in/out and returns a recordset
Hi Joey Rivera,
Lots of thanks for your helpful document.
Thanks
Apurba Mondal
Hi Joey
Thanks for the sample and explanation it’s really help me a lot
Hi joe…
Thanks a lot…
Thanks
Thank you, great tutorial
I would like to know how to fix this problem PROCEDURE test.usuarios can’t return a result set in the given context , the table test just have two camps … usuario and pass then the procedure is a simple select * from usuarios the php looks like it
$resultado=mysql_query(‘CALL usuarios()’)or die(mysql_error().”\n”);
while($resfila=mysql_fetch_assoc($resultado)){
debug($resfila);
}
Try adding: ‘,false,65536’ to the end of your mysql_connect. That fixed it for me:
$mysql = mysql_connect(‘localhost’, ‘example’, ‘example’,false,65536);
thanx, Joey! frankly – the best resource online on stored procedures and triggers, helped me a lot. good luck.
thanx, Joey! frankly – the best resource online on stored procedures and triggers, helped me a lot. good luck.
Thanks for your tutorial.
Great and simple tutorial.
Thanks…
Hello. Thanks for the great information.
I like the procedures with no parameters where you get the result set with one call to $mysqli->query(). For that to happen, do there have to be no parameters at all, or will it work if there are no OUT parameters, just IN parameters?
I got it working. The answer is yes, with no OUT parameters and only IN parameters, it will return a result set with multiple rows.
Thanks
Thanks alot for this simple tutorial.. it was very helpful.
Hey Joey, thanks a lot for the awesome tutorial,
best regards
Hello…thanks for the info. It helps a lot to me… God bless
JOEY THANKS !! you have changed my life 🙂
Great Joey Rivera,
Thank you very much dude!!!! You did great job………
Since from long time I was looking for Stored Procedure in PHP.
I searched lots of sites and no site could gave me help…but you have done it………
Really great effort….You have given great blessings to PHP programmers….
Againg very very very thank you……
Sumit Joshi,
sumitjoshi@themacrosoft.com
Hi Joey,
Am transiting from Oracle to MySQL so am very accustomed to the way Oracle works. And thus am a bit confused about this. In oracle these stored procedures exist in the DB and we just throw a call for them. Is it that in MySQL as well they are stored in the DB? And if it is so then we are supposed to compile these stored procedures just once before we start using them. If it is so when and where do we compile them ? Or is it that these stored procedures are to be compiled everytime they are called ?
Rachit, stored procedures in MySQL are also stored on the database. You create them once and execute when needed. Running the ‘CREATE PROCEDURE’ scripts above will store the routine in a table in the DB. Here are a couple great resources with more information:
Using Stored Routines:
http://dev.mysql.com/doc/refman/5.0/en/stored-routines.html
Stored Procedures and Function FAQ:
http://dev.mysql.com/doc/refman/5.0/en/faqs-stored-procs.html
Thanks a lot for clearing sp out for me!
Thanks a lot for your help Joey.
Thanks a lot mann totally appreciate it
The code is not giving me desired output. May be i suspect this b’cos the engine type is still showing MYISAM instead of INNODB.Could someone help me out in resolving the issue.
I am getting the following output :-
MYSQL: simple select
FIRST NAME :: Joey === LAST NAME ::Rivera
FIRST NAME :: John === LAST NAME ::Doe
MYSQL: calling sp with out variables
FIRST NAME :: === LAST NAME ::
MYSQL: calling sp returning a recordset – doesn’t work
Using MyISAM over InnoDB shouldn’t affect this test. My recommendation would be to make sure the stored procedure is working on the database first. I use MySQL query browser but you can try the command prompt if you are comfortable with it. In query browser, I click on the transaction button first, then run ‘call get_user(1, @first, @last)’. Then I click on the next line and run ‘select @first, @last’ and I see the expected results (@first = Joey, @last = Rivera). Are you getting this?
Also, try turning on error reporting on for all in PHP to see if you are getting some warning that may explain why this isn’t working correctly: http://us.php.net/manual/en/function.error-reporting.php
Great resources! Thanks! Working properly!
Only: If I have store procedure with parameter In and parameter Out and this store procedure must return more then 1 record then it doesn’t work (Error from page -Result consisted of more than one row). I am using MySQL and php. Connection to MySQL database $mysql = mysql_connect(‘localhost’, ‘example’, ‘example’,false,65536);
Can somebody help me resolve this problem? How I can get multi results from storeprocedure with in and out parameters?????
Valentina, I’ll post some more later with an example – maybe as a new blog post, on how to do what you are asking. Pretty much, you do a mix of the in/out sp plus the recordset examples above.
Dear Joey! I was impress how fast you responded for my request. Thanks a lot!
I play a lot around this problem. I am not PHP or MySQL guru. I am more familiar with SQL Store Procedures.
Looks like sometning Is missing if I try get result from SP that have IN parameters and OUT parameters. Your example for SP get_user is working OK, but for example you need get last_name for all of users with first_name like ‘John’.
This is my code:
1. Add couple users with same name:INSERT INTO `test`.`users` VALUES (null, ‘John’, ‘Smith’), (null, ‘John’, ‘Dream’);
2. Create SP with IN and OUT parameters:DELIMITER $$
DROP PROCEDURE IF EXISTS get_lastname_user $$
CREATE PROCEDURE get_lastname_user
(
IN firstName VARCHAR(100),
OUT lastName VARCHAR(100)
)
BEGIN
SELECT last_name
INTO lastName
FROM users
WHERE first_name = firstName;
END $$
DELIMITER ;
call get_lastname_user(‘Joey’, @OUT);
select @OUT as Last_Name;
You will be get Last_Name – Rivera.
But if you pass IN ‘John’ then
error coming: #1172 – Result consisted of more than one row .
How get all records in this situation???? What is missing??? Could somebody help me resolve this issue??
Valentina, the reason you are getting that error message is because the stored procedure is trying to set the variable lastName to a field value last_name but it can only do that for 1 row. Since your select is bringing back multiple rows it’s breaking. I just created a new post explaining how to do what you are asking for – using a stored procedure with in/out vars that also returns a recordset:
http://www.joeyrivera.com/2009/using-mysql-stored-procedure-inout-and-recordset-w-php/
I hope this new post will answer your question.
Dear Joey Rivera,
Thanks for such useful document. It clear my doubts regarding stored procedure.
How we can determine that use of stored procedure reduces execution time of web page? And one more thing I want to know does it good to give load on mysql instead of php?
Personally I don’t use stored procedures to reduce load time, I do it to organize my application better (as well as other reasons). I like having the db take care of itself and have the least amount of queries in my code. It almost makes it feel like the db can function as a standalone ‘app’ with any code you want to build around it.
To answer your question though, I do care about load time and I’m always working on optimizing code or queries to get the fastest execution times. For my projects I always use caching. Once the stored procedure is optimized and runs great – you can use explain to check your queries (http://dev.mysql.com/doc/refman/5.0/en/explain.html), my code will cache the results returned by the stored procedure so I don’t have to keep calling it unnecessarily. If the data changes, then I clear the cache file and query the sp again.
I also – in my development environment, print out all the queries I run per page, how long each query took, and how long each page took to execute. Here’s an example of the data I post for me to analyze and make sure things are running fast:
connect
CALL sp_user_select_view(10000, @id, @msg);
SELECT @id, @msg;
CALL sp_bio_user_select(10000, 1, @id, @msg);
SELECT @id, @msg;
Executed 5 queries in 0.0021560192108154 seconds
Average query length: 0.00043120384216309 seconds
Queries per second: 2319.0887979653
Longest query length: 0.00091314315795898
Longest query: connect
page load time: 0.092291116714478 (tracks how long the page took to completely execute)
As you can tell, the stored procedures are running extremely quick, they don’t slow down the load times at all. Connecting to the db was the slowest part. In reality my biggest load is the Zend Framework itself which is no issue to me since it offers so much and it’s not too bad of a hit anyways. I use a method in the ZF to display this information but you can get it from the basic mysql class in php as well. The page load time is simply a starttime before anything gets called and then an endtime when the page is done and I show the difference.
Your second question is whether to do more heavy lifting in code or in the database. This probably depends on the application but for things I’ve done, I know I will be using caching so I know I won’t be hitting the database as much and put a bit more work in the database end by using the stored procedures. I tend to run a couple extra queries in my stored procedures to validate data and error check instead of relying on the data coming from the code. I validate in code as well, but I want my database to work as a standalone and not rely on an outside source.
Thanks Joey Rivera… this is really educating…
Hi Joey,
May I request for a tutorial for using zend framework.. i’d like to learn that technology but I dont know where to start… already downloaded zend zip files, wamp, eclipse, and tomcat 5.5 as required from another website… Your tutorial is pretty much simpler than all of those I downloaded so I would like to request from you.
Reggie, I’ll give this one some thought but it won’t be easy. I myself read two books on the zend framework before I felt comfortable enough to use for real projects. Now I find it easy to use and understand how it works but it took some playing around to get there. I started by doing their quick start first to get a general idea of how it works:
http://framework.zend.com/docs/quickstart
And these are the two books I read, there are probably newer ones out now:
Zend Framework in Action
php/architect’s Guide to Programming with Zend Framework
Now every time I work with zend, I keep their reference guide open in another window to check methods, properties, examples, etc quickly:
http://framework.zend.com/manual/en/
hey joey,
just wondering if this is the correct way to call a stored procedure via the mysqli class?.
$mysqli = new mysqli(“localhost”,”user”,”ps”,”db”);
$rs = $mysqli->query(‘CALL updateResultsByEvent(?,?,?,?)’,
array($id,$event->name,$event->distance,$event->unit));
I seem to have trouble mapping my parameter values into the stored procedure input params, and was wondering can you spot anything.
Hi Paul,
I don’t think you can pass an array like that to the mysqli->query although I haven’t used it much. You can try binding params using a prepare statement:
http://us.php.net/manual/en/mysqli.prepare.php
You could also escape each variable individually and pass the variables to your query. For example:
// escape vars
$event = $mysqli->real_escape_string($event);
$unit = $mysql->real_escape_string($unit);
// then call sp
$rs = $mysqli->query(‘CALL updateResultsByEvent($id,$event,$distance,$unit)’);
http://us.php.net/manual/en/mysqli.real-escape-string.php
Thank you so very much for sharing this tutorial!
hey Joey,
can we use the same stored procedure to get both output parameters passed and result set.
for example lets reformat the SP called “get_user” to the following
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`get_user`$$
CREATE PROCEDURE `test`.`get_user`
(
IN userId INT,
OUT firstName VARCHAR(100),
OUT lastName VARCHAR(100)
)
BEGIN
SELECT first_name, last_name
INTO firstName, lastName
FROM users
WHERE users_id = userId;
select * from users where users_id = userId;
END $$
DELIMITER ;
—————————–
then use
$result = mysql_query ( “call get_user (1, @firstName, @lastName)” );
$result2 = mysql_query ( “select @firstName, @lastName” );
where variable $result represents the result set
while variable $result2 represents the output parameters
i’ve tried this code already and didn’t get the both. any mistake here? or is there another method to get what i want (result set and out params from the same Stored Procedure) ?
Hey Hossam, actually you can. Here is a post talking more about that topic in detail:
http://www.joeyrivera.com/2009/using-mysql-stored-procedure-inout-and-recordset-w-php
I found that trying to do this didn’t always work with the mysql adapter depending on your mysql driver version. But your code seems correct.
With mysql extension You will be unable to execute stored procedure returning record set twice or more. As a result of second call to mysql_query you will get Error: “Commands out of sync; you can’t run this command now”.
This because of calling to stored procedures treated as multi result set query and you need to tell mysql to give you next result.
Solution is using mysqli extension, and mysqli functions or object methods:
– mysqli_multi_query
– mysqli_use_result
– mysqli_store_result
– mysqli_more_results
– mysqli_next_result.
After each call to stored procedure and getting result set, you need to call mysqli_next_result and check if there more results with mysqli_more_results.
Somethink like this:
print ‘MYSQLI: calling sp returning a recordset’;
$rs = $mysqli->query( ‘CALL get_users()’ );
while($row = $rs->fetch_object())
{
debug($row);
}
$mysqli->next_result();
while ($mysqli->more_results())
{
$mysqli->next_result();
}
print ‘MYSQLI: calling sp returning a recordset’;
$rs = $mysqli->query( ‘CALL get_users()’ );
while($row = $rs->fetch_object())
{
debug($row);
}
$mysqli->next_result();
while ($mysqli->more_results())
{
$mysqli->next_result();
}
You also can get multiple recordsets from one stored procedure, if you want, i suppose.
Hello Joey,
Thanks for your tutorials. I hope you can help me with my problem.
I have a stored procedure:
delimiter |
drop procedure if exists pget_info2 |
create procedure pget_info2 ()
begin
declare mheadid int;
declare mhdname char(50);
declare mhdstat char(1);
declare mnorows boolean;
declare mnmrows int default 0;
declare ckbhead cursor for
select headid, hdname, hdstat
from kbhead
where headid <= 10;
declare continue handler for not found
set mnorows = true;
open ckbhead;
select found_rows() into mnmrows;
the_loop: loop
fetch ckbhead
into mheadid, mhdname, mhdstat;
if mnorows then
close ckbhead;
leave the_loop;
end if;
set mhdname := concat(mhdname, ‘_test’);
select mheadid, mhdname, mhdstat;
end loop the_loop;
end |
delimiter ;
When I call this procedure from mysql command line I receive all rows from cursor. But when I call this procedure from PHP-script I receive only one row (the first row from cursor).
This is my PHP-script:
$conn = mysql_connect(‘localhost’, ‘xxxx’, ‘xxxx’, false, 65536);
mysql_select_db(‘xxxx’, $conn);
$rs = mysql_query(“call pget_info2();”);
while ($row = (mysql_fetch_assoc($rs)))
{
print_r($row);
echo “”;
}
mysql_close($conn);
How I can receive all rows from stored procedure? Maybe I do some mistakes?
In advance thanks.
P.S. Excuse for my English
Going to put this artclie to good use now.
hi joe
Its very nice for beginners
Thanks a lot
Hello Joey,
I think you should write a book. Your explanations on matters that arises are wonderful. please am developing an Application for a private telecommunications company. Please, I need tutorials on “caching and procedures” for mySQL or mySQLi. My Server machine is slow now as much people are query the Database.
Thank
Christian,
Thank you. I actually like the idea of writing a blog post on caching. I’ll probably do that next.
Hi Joey,
Thank you for your blog, your site has been the only one that has provided me with any success whatsoever.
I’ve modified your example code to fit my own needs and for some reason, I’m still unable to have my OUT variables to return. I’m at a loss as my procedure is as simple as they come. My test case and example has been driving me crazy therefore I figured it may be time for me to reach out for some assistance.
Stored Procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS `UpgradeAvailable` $$
(
IN Platform VARCHAR(45),
IN Version VARCHAR(20),
OUT Version_ID INT(10),
OUT DownloadURL VARCHAR(250))
BEGIN
SELECT Pinglet_Version_ID, VersionDownloadURL
INTO Version_ID, DownloadURL
FROM Version_Table PV
WHERE PV.Platform = Platform
AND PV.Version = Version;
END $$
DELIMITER ;
I’ve thrown together a form to pass various values into the procedure call:
Platform:
Version:
A modification to your PHP page to display the results of your Stored Procedure Call:
<?php
// MYSQL
$mysql = mysql_connect(‘host’, ‘user’, ‘pass’); //,false,65536 // add this to fix issue with recordsets
mysql_select_db(‘database’, $mysql);
print ‘MYSQL: simple select’;
$rs = mysql_query( ‘SELECT * FROM Version_Table;’ );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}
print ‘Print Post Variables’;
print ‘Platform IN ‘;
debug($_POST[Platform]);
print ‘Version IN ‘;
debug($_POST[Version]);
print ‘MYSQL: calling sp with out variables’;
$rs = mysql_query( ‘CALL UpgradeAvailable($_POST[Platform], $_POST[Version], @Version_ID, @DownloadURL)’ );
$rs = mysql_query( ‘SELECT @Version_ID, @DownloadURL’ );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}
function debug($o)
{
print ”;
print_r($o);
print ”;
}
?>
You can access this temporary page demonstrating the above pages that I have published by accessing the http://pinglet.mobi/usage.data/spform.html with the following parameters:
Platform: ‘Joeys Example’
Version: ‘1.0’
Please let me know if you have any questions and thanks again for your willingness to help others.
Danielle
Danielle,
I think you are having the same issue Valentina commented on my post about in/out vars with mysql and php:
http://www.joeyrivera.com/2009/using-mysql-stored-procedure-inout-and-recordset-w-php/#comment-479
What she did was store the ‘out’ values in a temporary table and then query them. It seems that depending on what mysql driver you are using (you can get the number by checking your phpinfo() and looking for client api under mysql I believe) you may not be able to get the out variables back. Try using PDO if you have that installed since I haven’t had this issue using PDO. Unfortunately, I haven’t spent much time trying to find any better workaround using the mysql adapter.
Hi Joey Rivera
Thanks for your nice blog post. this really help me a lot.
hi Joey, i am greatful of your article, i learnt the sp from this article and now using it, i have one problem -when i am using sp on multiple time it is not working so please tell us the reason and solution of that.
shailesh
Shailesh,
I’m glad you found this helpful. Could you give me some more information on what exactly you are trying to do and what isn’t not working? Maybe post an example. Are you getting an error message? Does you example work in a MySQL tool and not in PHP?
This helps me a lot! Thanks bro! nway. I want to control the result by in html, may i know how to convert the result to string?
Jick, once you have the data in the $row variable you can just call the item in the array to get the string value. For example, if you want to get the ‘first_name’ you can do $echo $row[‘first_name’] in the loop for each user to get their first name.
Hey Joey,
Thanks alot for your very much helpful blog . This helped me much as I am just starting discovering how SP is helpful on us.
I did tried to search for a tutorial but found nothing. Thanks finally, It is in here in your blog.
God bless You always and I’ll be frequently visit your site to learn more.
Hi,
It was really helpful.
My Query is …is any way i can change the output array format
FROM
[
stdClass Object
(
[users_id] => 1
[first_name] => Joey
[last_name] => Rivera
)
stdClass Object
(
[users_id] => 2
[first_name] => John
[last_name] => Doe
)
]
TO
ARRAY
(
[users_id] => 1
[users_id] => 2
)
ARRAY
(
[first_name] => Joey
[first_name] => John
)
Thanks,
Dharan V
MuraliDharan, instead of using fetch_object try using fetch_array.This should give you the results you are looking for: http://us.php.net/manual/en/mysqli-result.fetch-array.php
Thanks for the article – just excellent.
I haven’t found anything that can help me figure out how to call a new query inside original query result’s loop.
Played with free_result and close, but no luck using mysqli. Queries work great individually, but not when I try to use another one back to back.
Do you have any ideas?
Here’s the code that comes back with error now:
// First find all matches
$qPicks = $mysqli->query(“CALL $proc ($searchDate, $maxRSI, $incRSI, $minMACD, $minVol, $minTrades, $minClose, $maxClose)”);
// process one row at a time
while($pick = $qPicks->fetch_array(MYSQLI_ASSOC)) {
$symbol = $pick[‘Symbol’];
$qPicks->close();
$qData = $mysqli->query(“CALL prcGetLastMACDDatesBelowZero(‘$symbol’, $searchDate)”); //, MYSQLI_USE_RESULT
$data = $qData->fetch_array(MYSQLI_ASSOC);
echo “$symbol … Num Dates: ” . $data[‘NumDates’];
$qData->close();
}
// close connections
$mysqli->close();
Thanks for any suggestions
Don
Got it figured and added example to PHP manual notes at http://us3.php.net/manual/en/mysqli.query.php – but probably won’t show up there right away.
For others that are interested, hope this example helps…
// Connect to server and database
$mysqli = new mysqli(“$dbServer”, “$dbUser”, “$dbPass”, “$dbName”);
// Open First Stored Procedure using MYSQLI_STORE_RESULT to retain for looping
$resultPicks = $mysqli->query(“CALL $proc ($searchDate, $maxRSI, $incRSI, $minMACD, $minVol, $minTrades, $minClose, $maxClose)”, MYSQLI_STORE_RESULT);
// process one row at a time from first SP
while($picksRow = $resultPicks->fetch_array(MYSQLI_ASSOC)) {
// Get Parameter for next SP
$symbol = $picksRow[‘Symbol’];
// Free stored results
clearStoredResults($mysqli);
// Execute second SP using value from first as a parameter (MYSQLI_USE_RESULT and free result right away)
$resultData = $mysqli->query(“CALL prcGetLastMACDDatesBelowZero(‘$symbol’, $searchDate)”, MYSQLI_USE_RESULT);
$dataRow = $resultData->fetch_array(MYSQLI_ASSOC);
// Dump result from both related queries
echo “$symbol … Num Dates: ” . $dataRow[‘NumDates’];
// Free results from second SP
$resultData->free();
}
// Free results from first SP
$resultPicks->free();
// close connections
$mysqli->close();
#——————————————
function clearStoredResults($mysqli_link){
#——————————————
while($mysqli_link->next_result()){
if($l_result = $mysqli_link->store_result()){
$l_result->free();
}
}
}
?>
DonM,
I believe the issus is the $qPicks->close(); inside your while. Basically you are closing the connection when returning your first result therefore you can’t return any other results. I would first try commenting that line out (right after where you create your $symbol) and see if that fixes this. If not, let me know and I’ll be glab to keep looking. Also, what error are you getting?
I guess I was a bit too slow answering that first one. Thanks for providing the solution for others with this problem. I have had a few people with the same issue trying to do multiple queries.
Thanks for the reply, Joey. Your blog at least got me started looking in the right direction.
I think there was something wrong with my initial code like you said – but the main thing I lacked was an understanding of the meaning of these constants and functions. The error messages didn’t make sense.
The best explanation I came across was in the big bible of “MySQL Developer’s Library” 4th edition by Paul DuBois. He has a whole section pp 398-400, “7.4.5 mysql_store_result() vs. mysql_use_result()” dedicated to explaining the differences between the two – and how to use them in different examples.
Basically “store” retrieves the whole result set, allocating memory for all rows and buffers them on the client side, whereas “use” doesn’t retrieve any rows at all – you need to use fetch_row to do row by row retrieval.
Anyways – glad I found your blog – keep it up!
after 2 days thru ur post i got rid of this error, thanks
Thanks for your nice blog post. this really help me a lot.
Gracias por el artículo, me ha sido de gran ayuda!! Saludos desde Argentina
Hi,
Its very nice for beginners.
I am facing a problem .
if i am calling sp returning a recordset
get_users()
and then
calling sp with out variables
get_user(1, @first, @last)
as below entire code
$pdo = new PDO(‘mysql:dbname=test;host=127.0.0.1′, ‘root’, ”);
print ‘PDO: calling sp returning a recordset’;
foreach($pdo->query( ‘CALL get_emps()’ ) as $row)
{
debug($row);
}
print ‘PDO: calling sp with out variables’;
$pdo->query( ‘CALL get_emp(6, @first, @last)’ );
foreach($pdo->query( ‘SELECT @first, @last’ ) as $row)
{
debug($row);
}
function debug($o)
{
print ”;
print_r($o);
print ”;
}
THEN ABOVE CODE PRODUCE ERROR LIKE BELOW
PDO: calling sp with out variables
Warning: Invalid argument supplied for foreach() in D:\cci_projects\test\sp.php on line 13
IF calling sp with out variables FIRST
WORKING FINE
WHAT SHOULD BE RESIGON ?
AND WHAT CAN I DO FOR THIS
PLEASE SUGGEST AAPS
Hey Mahendra,
I’ll try to look at this tonight to see if I can find something that stands out as a problem. Just for testing purposes, if you call the first sp and then close and open the db connection before calling the second sp, does it work?
Joey
MAn, it really helped.
thanks
thank you very much for your blog, this helped me a lot.
OOOOH very nice.
Thank you very muchhhhhhhhhhhhhhhhhh
it helps to me a lot.
hi Joey i have a problem with your code in the moment that to call a store procedure “get_user” appear a error
mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource
maybe is el mysql_fetch_assoc()
sorry if my english is bad
Ricardo, are you using the correct library version? If I remember correctly, not having the right version of the dll or library was causing this problem. Some of the comments on this post might explain the problem. You can also post your code so I can check to make sure nothing stands out.
Hi Joey,
Nice and through article on SProcs on MySql, I am also a developer, mainly work with MS Products and tools, also enjoy working on LAMP.
I Wanted to do use checkboxes on the html form and process in MySql etc, came to your blog.
Good work keep it up.
Nice ride enjoy.
Vijay
hi Joey,
Thanks a lot…
James, did you figure out your problem?
thx for the info. Think someone else is taking stuff from here without a trackback…
http://preetul.wordpress.com/2009/06/26/using-mysql-stored-procedures-with-php-mysqlmysqlipdo/
Thanks for pointing that out.
Thanks you so much! Very informative tutorial, worked perfectly.
When I execute the code the error “Result consisted of more than one row” comes
Which line is saying that? Can you do a var_dump on that object to see what’s inside? Was your code expecting one row but got many instead?
Hi Joey
Thanks for the sample and explanation it’s really help me a lot
Hi, excellent article. I attach a post (the post is in Spanish, and the MySQL script’s in Catalan) that explains how to implement a virtual store with MySQL stored procedures. There’s a MySQL API there for typical CRUD operations. Greetings to all!
http://programarivm.com/2012/01/comercio-electronico-de-la-uoc-base-de-datos-de-una-tienda-virtual-con-stored-procedures-y-stored-functions-de-mysql/
Gracias!
thanks for providing stored procedure…in php and my sql…………
i m new jr. developer this is helpfull for me…………….
thank u
hey great tut 🙂
maybe you can add this i found on http://de.php.net/manual/de/mysql.constants.php#mysql.client-flags
“frak at gingerhq dot net 30-Jun-2011 11:06
If you’re using stored procedures and mysql_error() returns “PROCEDURE storedProcedureName can’t return a result set in the given context”, you need to pass an additional flag (CLIENT_MULTI_RESULTS) to mysql_connect() as such:
mysql_connect($hostname, $username, $password, true, 131072);
Some other sources say that you should use 65536. 65536 is actually the flag to allow multiple statements in a single mysql_query(), and is a security issue. The reason it allows you to receive results from stored procedures is because it implies 131072. To be safe, use 131072 over 65536.
“
Very interesting and thanks for that information.
thank you
It’s awesome example to understand about the store procedure in the PHP using mysql.
I would request you please set of example for the trigger as well.
Thanks a lot 🙂
Thanks for the post, I’ve tried MYSQL and MYSQLi but I am getting an error on these lines:
$rs = mysql_query( ‘SELECT @first, @last’ );
and
$rs = $mysqli->query( ‘SELECT @first, @last’ );
“…Parse error: syntax error, unexpected ‘@’ …
Any idea how to fix this?
Thanks,
Alex
Hey Alex, not sure why you are seeing that error. What version of php and of the mysql adapter are you using?
Thanks for such useful tutorial that explains most used select statements, not unneeded variables or cursors.
thanks a lot. Keep post such valuable stuff.
hey! could you help me out with this? my code:
stored procedure
query(‘CALL allcom()’);
while($row=$rs->fetch_object())
{
echo $row;
}
//$mysqli_close($con);
?>
the error i get is:
Fatal error: Call to a member function fetch_object() on a non-object in C:\wamp\www\proj4\stor.php on line 12
how can i fix it?
Hey Amul, did you create and set the recordset variable? Your query line should look like:
$rs = $mysqli->query(‘CALL allcom();’);
Thanks a lot.
Type your comment here Necestio una ayudita señores en el siguiente codigo…. tengo un procedimeinto almacenado que al momento de ejecutarlo m funciona todo ok me muestra todo los mensajes de validacionn… el problema es que no me muestra los mensajes de validacion en php.
DELIMITER $$
DROP PROCEDURE IF EXISTS `Insert_Cargo` $$
CREATE PROCEDURE `Insert_Cargo`(
IN `VCod_Cargo` char(8),
IN `VNom_Cargo` varchar(25) )
DETERMINISTIC
BEGIN
DECLARE Vcodcargo char(8);
DECLARE Vnomcargo varchar(25);
if VCod_Cargo ” then
select Cod_Cargo into Vcodcargo from cargo where Cod_Cargo = upper(VCod_Cargo);
if isnull(Vcodcargo)then
if VNom_Cargo ” then
select Nom_Cargo into Vnomcargo from cargo where Nom_Cargo = upper(VNom_Cargo);
if isnull(Vnomcargo)then
insert into `db_factura`.`cargo` (
`Cod_Cargo`,
`Nom_Cargo`
)
values (VCod_Cargo,VNom_Cargo);
Select “Registro Se Añadido Sastifactoriamente”;
else
Select “El Nombre para el Cargo, Ya Existe”;
end if;
else
Select “Ingrese Un Nombre para el Cargo”;
end if;
else
Select “El Codigo del Cargo Ingresado, Ya Existe”;
end if;
else
Select “Ingrese El Codigo del Cargo”;
end if;
END $$
DELIMITER ;
This is a beautiful and complete tutorial for me.
Thanks a lot for such a nice sharing.
it’s nice for who start to teach stroe procedure
thanks
Hi!
I tried to run this examples in my local mysql database(im using MAMP for development and Sequel Pro for db management, in MacOS), but when i call the sp after running the query to create it, gives me a message ‘the sp test.test does not exist’.
I also ran the sql command SHOW CREATE PROCEDURE test.test and it also told me the sp doesn’t exist. What I’m doing wrong? This is my code:
delimiter $$
drop procedure if exists `redbull`.`loginUsuario` $$
create procedure `redbull`.`loginUsuario`
(
in usuarioId varchar(100),
in usuarioClave varchar(100),
in tabla_usuarios varchar(100),
out usuario_codigo varchar(100),
out usuario_nombre varchar(100),
out usuario_nombre_completo varchar(100)
)
begin
select codigo, nombre, CONCAT(nombre,’ ‘,apellido)
into usuario_codigo, usuario_nombre, usuario_nombre_completo
from tabla_usuarios
where usuario=usuarioId and contrasena=usuarioClave and activado = 1
end$$
delimiter ;
call loginUsuario(‘user’,’xxxx’,’table’,@codigo,@nombre,@nombrecompleto);
select @codigo, @nombre, @nombrecompleto;
hope for an answer soon, thank you
it’s working fine, I was totally confuse with store procedure, still one year I didn’t touch to store procedure due to unnecessary fear. Now I am working with store procedure with fine.
Thanks a lost.
really helpfull.thnx
Very well explained. However please explain about delimeter , code not executed in mysql yog then goes to http://www.techflirt.com/mysql-stored-procedure-tutorial/ and executed by delimeter
Thanks alot for this simple tutorial..
please help me. whats means @ in storprocedure?
Result sets returned from a stored procedure cannot be fetched correctly using mysqli_query(). The mysqli_query() function combines statement execution and fetching the first result set into a buffered result set, if any. However, there are additional stored procedure result sets hidden from the user which cause mysqli_query() to fail returning the user expected result sets.
Result sets returned from a stored procedure are fetched using mysqli_real_query() or mysqli_multi_query(). Both functions allow fetching any number of result sets returned by a statement, such as CALL