How to Use Stored Procedure in PHP

Introduction

• A Stored Procedure is a precompiled SQL statement stored in the database for later use. Within a Stored Procedure you can write procedural code that controls the flow of execution. That includes if or else constructs, and error-handling code.
• A Stored Procedure helps improve performance when performing repetitive tasks because they are compiled the first time they are executed.
• A Stored Procedure can be used to share application logic with other front-end applications, thus making it easier to change business rules or policies.

Simple Syntax of a Stored Procedure

CREATE/ALTER PROCEDURE procedure_name(parameters...)
BEGIN
    DECLARE variable_name datatype;
    .......
    .......// Sql statements
    .......
END

The CREATE PROCEDURE statement creates the procedure. The code within the CREATE PROCEDURE statement is defined by a block of code that begins with the BEGIN keyword and ends with the END keyword. The DECLARE  statement is used to define a variable name.

Parameter in Stored Procedure

A Stored Procedure can have IN, INOUT, and OUT parameters, depending on the MySQL version.

1. IN
   Passes a value into a procedure.
2. OUT
   Passes a value from a procedure back to the caller.
3. INOUT
   The caller initializes an INOUT parameter, but the procedure can modify the value, and the final value is visible to the caller when the procedure returns.

You can create a Stored Procedures (sp) using a PHP application and you can also use it in a PHP application. Here I am describing step-by-step how to create a sp in PHP and how to use it in a PHP application.

Step 1. For creating a Stored Procedure you must use a CREATE PROCEDURE statement.

CREATE PROCEDURE test()
BEGIN
    SELECT * FROM EMP;
END;

If you want to make any changes to a previously created Stored Procedure, you can use the "ALTER statement" instead of the CREATE statement.

ALTER PROCEDURE test()
SELECT name FROM EMP WHERE id=102

If you want to drop any procedure permanently from a database. use "DROP statement" before the procedure statement.

DROP PROCEDURE IF EXISTS TEST;

Step 2. The "CALL SQL statement" is used to execute a Stored Procedure.

CALL procedure_name
CALL test()

Example of Stored Procedure in PHP

<?php

$con = mysql_connect("localhost", "sharad", "gupta");

if (!$con) {
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("Employees", $con);

print "<h2>MySQL: Simple Select statement</h2>";

$result = mysql_query("SELECT * FROM emp");

while ($row = mysql_fetch_array($result)) {
    echo $row['id'] . "" . $row['FirstName'] . "" . $row['LastName'];
    echo "<br/>";
}

print "<h2>MySQL: Creating Stored Procedure</h2>";

$qry = mysql_query("CREATE PROCEDURE user() SELECT * FROM emp");

echo "Stored Procedure created.";

mysql_query($qry, $con);

print "<h2>MySQL: Calling Stored procedure</h2>";

$res = mysql_query("CALL user()");

while ($row = mysql_fetch_array($res)) {
    echo $row['id'] . " " . $row['FirstName'] . " " . $row['LastName'];
    echo "<br/>";
}

mysql_close($con);
?>

Note. In the example given above, I have covered three (3) important statements. First I created a simple SQL statement, second created a Stored Procedure and third I called the Stored Procedure in the front end using PHP code.

Output