Stored Procedure Lesson

Are you writing Stored Procedures if not please take a look at this post. Stored procedures can help to improve web application performance and reduce database access traffic. In this post I want to explain how to create and call the stored procedures from database server.

Database
users table contains username and name.

CREATE TABLE users
(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE,
name VARCHAR(50),
);

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

<?php
include('db.php'); // Check code below of the post.
$sql=mysql_query("SELECT user,name FROM users");
while($row=mysql_fetch_array($sql))
{
echo $row['user'].'--'.$row['name'].'</br>';
}
?>

I had implemented Stored Procedure at clouds.9lessons.info

How to Create Stored Procedure

You can create stored procedures that run on your database server. Stored Procedure name users(). Just like SQL statements.

DELIMITER // 
CREATE PROCEDURE users()
SELECT username,name FROM users;

How to Call Stored Procedure

Results.php (With stored procedures)
Notice that here mysqli(MySQL Improved Extension)

<?php
include("newdb.php");
$sql=mysqli_query($connect,"CALL users()");
while($row=mysqli_fetch_array(sql))
{
echo $row['user'].'--'.$row['name'].'';
}
>

newdb.php (Stored Procedure)
You have to include this file every stored procedure call. Why because call automatically closing the database connection.

<?php
$connect=mysqli_connect('localhost','username','password','database');
if (!$connect)
{
printf("Can't connect to MySQL Server.", mysqli_connect_error());
exit;
}
?>

Stored Procedure Input

Normal Way
insert procedure IN - Input , name and datatype.

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

INSERT INTO users(username,name) VALUES (username,name);

Better Way
I recommend you to create stored procedures following statements.

DELIMITER // 
CREATE PROCEDURE insert(IN username VARCHAR(50),IN nameVARCHAR(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("newdb.php");
$username='9lessons';
$name='Srinivas Tamada';
$sql=mysqli_query($connect,
"CALL insert('$username','$name')");
?>

db.php
Database configuration code.

<?php
$mysql_hostname = "localhost";
$mysql_user = "username";
$mysql_password = "password";
$mysql_database = "database";
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password)
or die("Opps some thing went wrong");
mysql_select_db($mysql_database, $bd) or die("Opps some thing went wrong");
?>