9lessons programming blog
Loading Search
9lessons Bring Friends
Wall Script
Follow Me:
Thursday, July 1, 2010

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.

Stored Procedure

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 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='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");
?>
Comments
{ 46 comments }
Mirza Ilhami said...

nice bro..

Sovath said...

For for this great lesson.
I am looking for a tutorial of how to use class in php and mysql.
Thanks

Shashi said...

thanks for this great post.... its very elegant.
Good work.

Muhammad Andik Cahyono said...

Thank,..
nice post

Buzzknow said...

is this will improve mysql speed?

Srinivas Tamada said...

Hi Friends,

I'm just giving ideas about basics
"How to Implement the Concepts"

SQL injections and all security issues you have to fix it.

Note : Professionals always busy with work.

Anonymous said...

Procedures do not improve performance, but parameterized queries do, because the database can create an executing plan from the queries, and reuse that for similar queries with different parameters. A stored procedure is just and example of a parameterized query.

I don't get the scheme with the arrows.

Abdullah Al Mamun said...

@Srinivas Tamada:

You are really great.
I am learning a lot from your tutorials.
Just one thing,
There are tons of tuts about php and jquery but database is neglected everywhere.
If you write more about core and fundamentals that will be very helpful.
.........
This one is really nice as the post about foreign key.
.......
Once again,
more posts focusing on database will be cordially appreciated.

Note: Thanks a lot for your time. :-)

Sitthyikun said...

Traffic is very important if I can reduce.

nice

kuldeep singh sadioura said...

as usual top the ranks....

iRvanFauziE said...

This is like trigger in postgreSQL?
i think this is trigger in mySQL ..

like so much for this lesson ..
thank you ..

Srinivas Tamada said...

@Abdullah Al Mamun

Thank you.. Sure I will

Vionodh Bandu said...

good seenu.......

Anonymous said...

Please look at this before using MySQL stored procedures from a web application
http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/

Anonymous said...

"Stored procedures can help to improve web application performance and reduce database access traffic."

Good article in the How, not good in the Why... everybody talks about performance when defending SP, but no one shows the tests...

Jaik said...

What kind of a performance hit does re-establishing the DB connection after each call create, I'd imagine it could be quite heavy in a large, complex site?

design is funny said...

nice information.. i like it

Anonymous said...

Nice article,

See also http://kwartik.wordpress.com/mysql-historization/ to see how the concept can be pushed to manage historization.

John.

Anonymous said...

thanks, great articel but once I've created some stored procs how do i manage them ? Im used to MS-SQL enterprise mangler, is there an equivalent for mysql that lets you manager your sprocs in a gui/interface ?

Anonymous said...

Thanks for the article.

For a reply to July 16,2010 question: A very good solution that can be run on desktop is provided by MySQL as MySQL Workbench (currently in Beta). You may visit the download page: http://dev.mysql.com/downloads/workbench/5.2.html

asimshahiddIT said...

Great help ......

Thanks for your tutorials

yudi said...

yes this what i need thanks for ur tutorial its great

Anonymous said...

very nice article. keep it up

Anonymous said...

Thanks for sharing..

dragonfly said...

Thanks for sharing.. There is one thing I want to ask. I have already got a mysql connection. Everything is built upon that connection. and mysqli does not support all the fuctions mysql supports. For example mysqli_num_fields exists for mysql_num_fields. however, mysqli_field_name is not exist to correspond mysql_field_name. Is that possible that using stored procedures without using mysqli extension. Thanks Ozlem.

Anonymous said...

nice article

Anonymous said...

Fortunately most applications have stored procedures that follow the same basic operations and design. Hence we can use some good sp generator like T4S stored procedure generator or Pro SP

Meet said...

good one

Anonymous said...

can u help me a simple algorithm?
e.g.: score rank should be: score rank
100 1 100 2
100 2 100 2
100 3 100 2
89 4 89 4.5
89 5 89 4.5
86 6 86 6
84 7 87 7

Anonymous said...

Thanks and more power ;)

Anonymous said...

Thanks in advance!

Stalin said...

hi how to connect php with MSSQL procedure

Anonymous said...

Hi Srinivas...

Is it possible to join multiple stored procedures (select stmnts) which would include passing of parameters ... for eg a userid... ???

thanks

99 Developer said...

hey i have call MS-SQL SP from the php which have in - out parameter in it ..
check this link out.
http://developer99.blogspot.com/2011/07/calling-ms-sql-sp-from-php.html

Anonymous said...

Nice work. Very usefull.

Anonymous said...

nice tutorials

PuriSpace said...

This is really help us . thank you for nice tutorial

Anonymous said...

good work

Anonymous said...

i want to insert data into one table as well as select data from another table. how to implement with stored procedure? help me!

Anonymous said...

Finally a tutorial that is actually useful! :-) For such a relatively simple thing to do, it's amazing how hard it is to find a good example.
Thanks!

herojewel said...

Osom !! bro many thanks for this tutorial.This is really helpful.

Anonymous said...

how to write update query in phpmyadmin?

vijaysinhparmar said...

How to pass parameters in select procedure.???

Anonymous said...

Nice post. Its definitely help to new users those interested to implement stored procedure.

Raj Singh said...

NICE ARTICLE

thnks for help to learn stored procedure in php.
this tutorial is so good and simple to learn stored procedural in php ....thanks a lot..

yogesh Randive said...

plz show how to call two stored procedure at same page using mysql function.

Post a Comment