Using MySQL Stored Procedures with PHP mysql/mysqli/pdo
Using MySQL Stored Procedures with PHP mysql/mysqli/pdo
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://us.php.net/manual/en/book.mysql.php
MySQLi – http://uk2.php.net/manual/en/class.mysqli.php
PDO – http://us.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, ‘Makey’, ‘McDon’), (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: ‘Makey McDon’ 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 ‘MYSQL: simple select’;
$rs = mysql_query( ‘SELECT * FROM users;’ );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}
print ‘MYSQL: calling sp with out variables’;
$rs = mysql_query( ‘CALL get_user(1, @first, @last)’ );
$rs = mysql_query( ‘SELECT @first, @last’ );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}
print ‘MYSQL: calling sp returning a recordset – doesn\’t work’;
$rs = mysql_query( ‘CALL get_users()’ );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}
// MYSQLI
$mysqli = new mysqli(’localhost’, ‘example’, ‘example’, ‘test’);
print ‘MYSQLI: simple select’;
$rs = $mysqli->query( ‘SELECT * FROM users;’ );
while($row = $rs->fetch_object())
{
debug($row);
}
print ‘MYSQLI: calling sp with out variables’;
$rs = $mysqli->query( ‘CALL get_user(1, @first, @last)’ );
$rs = $mysqli->query( ‘SELECT @first, @last’ );
while($row = $rs->fetch_object())
{
debug($row);
}
print ‘MYSQLI: calling sp returning a recordset’;
$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 ‘PDO: simple select’;
foreach($pdo->query( ‘SELECT * FROM users;’ ) as $row)
{
debug($row);
}
print ‘PDO: calling sp with out variables’;
$pdo->query( ‘CALL get_user(1, @first, @last)’ );
foreach($pdo->query( ‘SELECT @first, @last’ ) as $row)
{
debug($row);
}
print ‘PDO: calling sp returning a recordset’;
foreach($pdo->query( ‘CALL get_users()’ ) as $row)
{
debug($row);
}
function debug($o)
{
print ‘
’; print_r($o); print ‘
’;
}
?>
When you run this code you get the following results:
MYSQL: simple select
Array
(
[users_id] => 1
[first_name] => Makey
[last_name] => McDon
)
Array
(
[users_id] => 2
[first_name] => John
[last_name] => Doe
)
MYSQL: calling sp with out variables
Array
(
[@first] => Makey
[@last] => McDon
)
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] => Makey
[last_name] => McDon
)
stdClass Object
(
[users_id] => 2
[first_name] => John
[last_name] => Doe
)
MYSQLI: calling sp with out variables
stdClass Object
(
[@first] => Makey
[@last] => McDon
)
MYSQLI: calling sp returning a recordset
stdClass Object
(
[users_id] => 1
[first_name] => Makey
[last_name] => McDon
)
stdClass Object
(
[users_id] => 2
[first_name] => John
[last_name] => Doe
)
PDO: simple select
Array
(
[users_id] => 1
[0] => 1
[first_name] => Makey
[1] => Makey
[last_name] => McDon
[2] => McDon
)
Array
(
[users_id] => 2
[0] => 2
[first_name] => John
[1] => John
[last_name] => Doe
[2] => Doe
)
PDO: calling sp with out variables
Array
(
[@first] => Makey
[0] => Makey
[@last] => McDon
[1] => McDon
)
PDO: calling sp returning a recordset
Array
(
[users_id] => 1
[0] => 1
[first_name] => Makey
[1] => Makey
[last_name] => McDon
[2] => McDon
)
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.
Frankly speaking I don’t know these last two parameters but Important thing is It does work.
If somebody is struggling with mysqli connection, then following Code will help:
Class File:
class dbc{
private $dblink;
public $SQLlog;
function connecti($db,$usr,$pass){
$this->dblink = new mysqli($db,$usr,$pass);
if (mysqli_connect_errno()) {
printf(“Connect failed: %s\n”, mysqli_connect_error());
exit();
}
}
function connectdbi($db){
$db_selected = mysqli_select_db($this->dblink,$db);
if (!$db_selected){
die (“Can\’t use test_db : ” . mysql_error());
}
}
function sqli($sql,$dbs,$usr,$pass,$db){
$this->connecti($dbs,$usr,$pass);
$db_selected = $this->connectdbi($db);
$this->SQLlog = $sql;
return $res=mysqli_query($this->dblink,$sql);
}
}
Php File:
$scd = new dbc();
$callSQL = “CALL MySP();”;
$InsertBSEIntradayArch = $scd->sqli($callSQL,’Server’,’Username’,’Password’,’database’);
Reference Document
Thanx to Joey Rivera for making our life easy!! U Rock!!!
hi there how did you use MySQL queries in your post? Everytime I try and post something with MySQL in it, I get a access error …
thanks!
G
Hey… ideally it sld not give any error as its a text editor.
I guess.. u r entering some special character 🙂
Hey this is a pretty good lifesaver! everywhere else I searched said: “Update your PHP to 5.3”, yeah, like my client’s hosting provider would do it.
Thanks a lot!
I also use a debug() function. But I included it from the php.ini:
auto_prepend_file = “D:\http\debug.php”
Here it is, it may be useful to someone:
<?php
function debug( $var, $second = false, $third = false )
{
$label = false;
$method = 0;
if( $second !== false )
{
if( $third !== false )
{
if( (int) $third === $third )
$method = $third;
else
$label = $third;
}
if( (int) $second === $second )
$method = $second;
else
$label = $second;
}
echo '
';
}
?>
Hey Buddy…thanx for u r contribution
that program is perfomed all row will be deleted how to check where condition
stored procedure using php how to delete proceder call in php
I am not getting your questions. Can u elaborate plz?
Thanks for the great information.
If I may offer some advice.
In order to make “MYSQL: calling sp returning a recordset” WORK,
change the connect code
from $mysql = mysql_connect(‘localhost’, ‘example’, ‘example’);
to $mysql = mysql_connect(‘localhost’, ‘example’, ‘example’, false, 65536);
THANKS Justin!!! You are a lifesaver. That tip has saved me a ton of work. YOU ROCK!!!
preetul, good post and this is a really lifesaver! And not sure how i missed this since last night.
Hi Preetul, really a nice post.
Just what I was looking for.
Thanks.
#define CLIENT_MULTI_STATEMENTS 65536 /* Enable/disable multi-stmt support */
The above define I found in the php manual. Thus it seems that multi-statement support must be enabled to return values from a stored procedure. My guess is that mysqli has this already set by default.
Hi Preetul,
as a senior programmer but a newbie to PHP and MySQL I was reading about a hundred postings by a simple google search for PHP MySQL Stored Procedures. It seems that only very very very view people in this world are able to give simple examples … and you are one of them. I thank you so much for this great post.
Add my vote of thanks. Like Oliver, I am a senior programmer but a newbie to PHP and MySQL and found your complete example very helpful. Tx again. JohnJ
Excellent content..
Thanks! The MySQL trick was neato!
It seems that putting a 65536 in the mysql-connect is introducing a security issue.
On this web page of the PHP documentation (look at the two first notes), http://fr2.php.net/manual/en/mysql.constants.php#mysql.client-flags they tell we should put 131072 instead.
hey i did it ..
http://developer99.blogspot.com/2011/07/calling-ms-sql-sp-from-php.html
check out this link
can any one help to write multiple queries to get multiple results from mysql storedprocedure?
and then how to use multiple results in php?
Yes, You can need to use
mysqli_next_result($db);
to move to next result set.Check following Links:
stackoverflow.com
ibm.com
Hey Preetul,
I would appreciate some credit for this post.
http://www.joeyrivera.com/2009/using-mysql-stored-procedures-with-php-mysqlmysqlipdo/
Thanks.
Thanx for making our life easy!!!
Let me tell you one thing clearly, I had got the document in mail from my senior colleague. I found it very helpful, so I immediately posted it at my blog(but didn’t put any reference since i didn’t have any).
Thanx for sharing the original post.
This post was really informative & helpful.
Hello There,
I have to create a mysql procedure on urgent basis.
Please help me
I have three table d1,d2,d3 with same field, like id,name,dttm etc.
Now procedure is may like this, procedure have to select data from d1 table and insert into d2,d3 conditionally… condition is count of id as cdtid field group by name from d1 if cdtid > 1 then insert this row to d2 otherwise insert into d3.
Any one can please do
Thanks
Maahi Vala
Hi,
thank u very much………..it is usefull for starter….i m newly…developer..
thanks alots.. toprankwebservices
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
Remove single quotes(‘) & put double quotes(“) instead,
e.g. $rs = mysql_query(“Select @first …”)
& Then try,
thanx
thanks a lot.
Hi,
Thanks for the post!
Can we create a stored procedure returning multiple resultset and use prepare statement to pass the parameters of stored procedure?? how can we fetch the multiple resultset??
Please help!!
I’m new to developing web sites and I was wondering if having your website title relevant to your content really that important? I see your title, “Using MySQL Stored Procedures with PHP mysql/mysqli/pdo | Programming Geeks ” does seem to be spot on with what your blog is about however, I prefer to keep my title less content descriptive and based more around site branding. Would you think this is a good idea or bad idea? Any help would be greatly appreciated.