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. 1st 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. 2nd 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);
?>
Posts
