Stored Procedure in php with example

Stored Procedure : These are set of SQL Commands / Statements that are stored in database in compiled form so that can be shared by a number of programs.

stored procedure is a subroutine stored in the database catalog.

Advantages of Stored Procedure

• Developed once, All applications can use same Command / Statements.
• Task becomes easier and less complicated.
• Network traffic reduced to greater extent as not sent repeatedly to server / reduces CPU load.
• Centralization of Command / Statements, which helps in repeated call
• Runs on any environment.

---

Basic Structure

CALL Procedure(....) - The CALL SQL statement is used to execute a stored procedure.

Parameters - IN/INOUT/OUT

Parameter	Description
IN	An IN parameter passes a value into a procedure with CALL. The procedure might modify the value, but the modification is not visible to the caller when the procedure returns.
INOUT	An INOUT parameter is initialized by the caller, can be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns.
OUT	An OUT parameter passes a value from the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns.

Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE. For a FUNCTION, parameters are always regarded as IN parameters.

---

Understand Procedure in PHP By Example

First we need to create database:

CREATE DATABASE `userdata`

Then create Table:

CREATE TABLE IF NOT EXISTS `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`contact` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5  

 Dumping Data into table:

INSERT INTO `users` (`id`, `name`, `contact`) VALUES
(1, 'Mukund Topiwala', '9422222222'),
(2, 'Ram ', '6666666635'),
(3, 'Veer', '5689782532'),
(4, 'BCW', '6584225141');

Afte Creating database and tables now look at the procedure.

Remember: 

"If you are using phpMyAdmin for MySQL or MySQLi You may have to create and compile Your Procedure into sql command box."

DELIMITER //

DROP PROCEDURE IF EXISTS `userdata`.`getUsers` //
CREATE PROCEDURE `userdata`.`getUsers`
(
IN uId INT,
OUT fullName VARCHAR(100),
OUT contactNo VARCHAR(10)
)
BEGIN
SELECT name, contact
INTO fullName, contactNo
FROM users
WHERE id = uId;
END //
DELIMITER ;

Explanations:

Here Client DELIMITER command used to change default delimiter ; to // while procedure is being defined. After setting DELIMITER DROP is used to drop the old procedure similar to drop table. First you check for the stored procedure if exist drop it before you recreate it. 

CREATE is used to create new stored procedure. Here in above example getUsers has three parameters uId, fullName, and contactNo. uId is passed to the Procedure that is why set to IN while fullName and contactNo is being returned from the procedure to caller, set to OUT. For more details see parameters table. 

Body of Procedure is written inside the BEGIN and END block. SELECT query that is used to select name and contact from the users table where the id = uId which is passed from the caller in CALL statement. OUT variable retrieves the value from the SELECT statement. fullName is set into  name and simillarly contactNo is set into contact.

Remember:

"Your Procedure is stored inside `ROUTINES` Table of `information_schema`

Once You compiled and successfully created your Procedure you can view it by following query

select routine_definition

from information_schema.routines
where routine_schema = 'userdata'
and routine_name = 'getUsers';   "

As you have created and compiled your procedure in MySQL now time to create a PHP file.

With MySQL AND PHP

<?php
       $mysql = mysql_connect('localhost', 'root', '');
       mysql_select_db('userdata', $mysql) ; 

       print '<h3>MySQL: simple select</h3>';
       $rs = mysql_query( 'SELECT * FROM users;' );
       while($row = mysql_fetch_assoc($rs))
      {
      echo $row["name"]."||".$row["contact"]."<br>";
      } 

      print '<h3>MySQL: calling Procedure with out variables</h3>';
      $rs = mysql_query( 'CALL getUsers(1, @name, @contact)' );
      $rs = mysql_query( 'SELECT @name, @contact' );
      while($row = mysql_fetch_assoc($rs))
      {
      echo $row["@name"]."||".$row["@contact"];
      }
?>

With MySQLi AND PHP

$mysqli = new mysqli('localhost', 'root', '', 'userdata');
print '<h3>MYSQLI: simple select</h3>';
$rs = $mysqli->query( 'SELECT * FROM users;' );
while($row = $rs->fetch_object())
{
print_r($row);
print("<br>");
}
print '<h3>MYSQLI: calling Procedures with out variables</h3>';
$rs = $mysqli->query( 'CALL getUsers(1, @name, @contact)' );
$rs = $mysqli->query( 'SELECT @name, @contact' ); 

while($row = $rs->fetch_object())
{
print_r($row);
print("<br>");
} 

Output :

To retrieve all field as array you can use print_r($row) function too.
replace echo line with print_r

MySQLi output :

---

Now to retrieve all the recordset with no repeated same value. (visit - retrieve recordset using stored procedure)

DELIMITER //
DROP PROCEDURE IF EXISTS `test`.`get_users`$$
CREATE PROCEDURE  `test`.`get_users`()
BEGIN
SELECT *
FROM users;
END //
DELIMITER ; 

print '<h3>MySQL: calling Procedure returning a recordset – Won't work here with MySQL</h3>';
$rs = mysql_query( 'CALL get_users()' );
while($row = mysql_fetch_assoc($rs))
{
print_r($row);
print("<br>");
} 

Output: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ***.php on line xx 

print '<h3>MySQLi: calling Procedure returning a recordset</h3>';
$rs = $mysqli->query( 'CALL get_users()' );
while($row = mysql_fetch_assoc($rs))
{
print_r($row);
print("<br>");
} 

Output: as above in simple mysqli select

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);