How to Create Stored Procedure in MySql phpmyadmin with Example

How to Create MySql Stored Procedure in PHP

Stored Procedure is a group of Transact SQL statements compiled into a single execution.

For create stored procedure please see below.  Stored procedure reduces database request traffic.

Database Table

employees  table  contains  empname  and  empno

CREATE TABLE employees(id INT PRIMARY KEY AUTO_INCREMENT,

empno VARCHAR(50) UNIQUE, empname VARCHAR(50));          

connection.php

</?php

$con = mysql_connect(“localhost”, “root”, “root”);

mysql_select_db(“storedprocedure”, $con);

?>

showresult.php (Simple fetch data from database)

</?php

include(“connection.php”);

$query = “select empno, empname from employees”;

$result = mysql_query($query);

while($data = mysql_fetch_array($result))

{

                echo $data[‘empno’].’ - ‘.$data[‘empname’].’’;

}

?>

Create Stored Procedure

We are going to create stored procedures that run on our database server.

Stored Procedure name empNoName(). Just like SQL statements.

DELIMITER //

CREATE PROCEDURE empNoName()

SELECT empno, empname from employees;

Call Stored Procedure

showresult.php ( With stored procedures )

</?php

include(“connection.php”);

$query = “CALL empNoName()”;

$result = mysql_query($query);

While($data = mysql_fetch_array($result))

{

                echo $data[‘empno’].’ – ‘.$data[‘empname’];

}

?>

Create Stored Procedure for Input

We can create stored Procedure from 2 types

      1.       1^st type

Insert procedure IN – Input, name and datatype.

DELIMITER //

CREATE PROCEDURE insert(IN empno VARCHAR(50), IN empname varchar(50))

INSERT INTO employees(empno, empname) values (empno, empname);

      2.       2^nd type

DELIMITER //

CREATE PROCEDURE insert(IN empno varchar(50), IN empname varchar(50))

BEGIN

SET @empno=empno;

SET @empname=empname;

PREPARE STMT FROM

“INSERT INTO user(empno, empname) VALUES (?,?)”;

EXECUTE STMT USING @empno, @empname

END

Insert.php

Include(“connection.php”);

$empno = 11;

$empname = “Kamran”;

$query = “CALL insert(‘$empno’, ‘$empname’)”;

$result = mysql_query($query);

?>