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),
);
(
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>';
}
?>
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;
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'].'';
}
>
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;
}
?>
$connect= mysqli_connect('localhost','username','password','database');
if (!$connect)
{
printf("Can't connect to MySQL Server.", mysqli_connect_error());
exit;
}
?>
Stored Procedure Input
Normal Wayinsert 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);
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
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')");
?>
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");
?>
$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");
?>
nice bro..
ReplyDeleteFor for this great lesson.
ReplyDeleteI am looking for a tutorial of how to use class in php and mysql.
Thanks
thanks for this great post.... its very elegant.
ReplyDeleteGood work.
Thank,..
ReplyDeletenice post
is this will improve mysql speed?
ReplyDeleteHi Friends,
ReplyDeleteI'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.
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.
ReplyDeleteI don't get the scheme with the arrows.
@Srinivas Tamada:
ReplyDeleteYou 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. :-)
Traffic is very important if I can reduce.
ReplyDeletenice
as usual top the ranks....
ReplyDelete@Abdullah Al Mamun
ReplyDeleteThank you.. Sure I will
good seenu.......
ReplyDeletePlease look at this before using MySQL stored procedures from a web application
ReplyDeletehttp://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/
"Stored procedures can help to improve web application performance and reduce database access traffic."
ReplyDeleteGood article in the How, not good in the Why... everybody talks about performance when defending SP, but no one shows the tests...
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?
ReplyDeletenice information.. i like it
ReplyDeleteNice article,
ReplyDeleteSee also http://kwartik.wordpress.com/mysql-historization/ to see how the concept can be pushed to manage historization.
John.
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 ?
ReplyDeleteThanks for the article.
ReplyDeleteFor 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
Great help ......
ReplyDeleteThanks for your tutorials
yes this what i need thanks for ur tutorial its great
ReplyDeletevery nice article. keep it up
ReplyDeleteThanks for sharing..
ReplyDeleteThanks 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.
ReplyDeleteFortunately 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
ReplyDeletegood one
ReplyDeletecan u help me a simple algorithm?
ReplyDeletee.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
Thanks and more power ;)
ReplyDeleteThanks in advance!
ReplyDeletehi how to connect php with MSSQL procedure
ReplyDeleteHi Srinivas...
ReplyDeleteIs it possible to join multiple stored procedures (select stmnts) which would include passing of parameters ... for eg a userid... ???
thanks
hey i have call MS-SQL SP from the php which have in - out parameter in it ..
ReplyDeletecheck this link out.
http://developer99.blogspot.com/2011/07/calling-ms-sql-sp-from-php.html
Nice work. Very usefull.
ReplyDeletenice tutorials
ReplyDeleteThis is really help us . thank you for nice tutorial
ReplyDeletegood work
ReplyDeletei want to insert data into one table as well as select data from another table. how to implement with stored procedure? help me!
ReplyDeleteFinally 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.
ReplyDeleteThanks!
Osom !! bro many thanks for this tutorial.This is really helpful.
ReplyDeletehow to write update query in phpmyadmin?
ReplyDeleteHow to pass parameters in select procedure.???
ReplyDeleteNice post. Its definitely help to new users those interested to implement stored procedure.
ReplyDeleteNICE ARTICLE
ReplyDeletethnks for help to learn stored procedure in php.
this tutorial is so good and simple to learn stored procedural in php ....thanks a lot..
plz show how to call two stored procedure at same page using mysql function.
ReplyDeleteThanx 4 post really helpful to me
ReplyDeleteNice code and easy to understand.
ReplyDeleteNice programming and it's very useful for the beginners
ReplyDelete