Stored Procedure Lesson in PHP


 

Stored Procedure Tutorial in PHP:
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>‘;

}

?>

 

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’].”;

}

 

Stored Procedure Input OR Insert
Normal Way
insert procedure IN – Input , name and datatype.

DELIMITER //

CREATE PROCEDURE insert(IN username VARCHAR(50),IN name VARCHAR(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 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(“newdb.php”);

$username=’Venugopal24′;

$name=’venugopal’;

$sql=mysqli_query($connect,

CALL insert(‘$username’,’$name’)“);

?>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s