MYSQL Stored Procedure in PHP

MYSQL Stored Procedure in PHP

A stored procedure is a segment of declarative SQL statements stored inside the database catalog.
Stored procedures can help to improve web applications and reduce database requests traffic.This post explains you how to make procedures and decrease database requests.

store-procedure

Database:
users table contains username and name.
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
)

Result.php (Direct database server access):
Calling SQL statements directly. Here displaying the users content with PHP.

<?php
include(‘DataBase.php’); // Check code below of the post.
$sql=mysql_query(“SELECT username,name FROM users”);
while($row=mysql_fetch_array($sql))
{
echo $row[‘username’].’–‘.$row[‘name’].'</br>’;
}
?>

How to Create Stored Procedure:
You can create stored procedures that run on your database server. Stored Procedure name userIDName(). Just like SQL statements.
DELIMITER //
CREATE PROCEDURE users()
SELECT username,name FROM users;

How to Call Stored Procedure
Results.php (With stored procedures):
<?php
include(“DataBase.php”);
$sql=mysql_query(“CALL users()”);
while($row=mysql_fetch_array($sql))
{
echo $row[‘username’].’–‘.$row[‘name’].”;
}
>
Stored procedure for inserting data:

DELIMITER //
CREATE PROCEDURE insert(IN username VARCHAR(50),IN name VARCHAR(50))

BEGIN

SET @username=username;
SET @name=name;

PREPARE STMT FROM
“INSERT INTO users(username,name) VALUES (?,?)”;

EXECUTE STMT USING @username,@name;

END

Insert.php:Here inserting values into users table with calling insert() procedure.

<?php
include(“DataBase.php”);
$username=guest;
$name=’xyz user’;
$sql=mysql_query($connect,”CALL insert(‘$username’,’$name’)”);
?>
DataBase.php Database configuration code.
<?php
$mysql_hostname = “localhost”;
$mysql_user = “username”;
$mysql_password = “password”;
$mysql_database = “database”;
$rs = mysql_connect($mysql_hostname, $mysql_user, $mysql_password)
or die(“Opps some thing went wrong”);
mysql_select_db($mysql_database, $rs) or die(“Opps some thing went wrong”);
?>

If you want to list all stored procedures you can use this query
select * 
from information_schema.routines
where routine_type = 'procedure'

 .