Stored Procedure Lesson
Wall Script
Follow Me:
Thursday, July 01, 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");
?>
Was this article helpful?
Thanks! Your feedback helps us to improve 9lessons.info


48 comments:

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

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

    ReplyDelete
  3. is this will improve mysql speed?

    ReplyDelete
  4. 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.

    ReplyDelete
  5. 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.

    ReplyDelete
  6. @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. :-)

    ReplyDelete
  7. Traffic is very important if I can reduce.

    nice

    ReplyDelete
  8. @Abdullah Al Mamun

    Thank you.. Sure I will

    ReplyDelete
  9. good seenu.......

    ReplyDelete
  10. 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/

    ReplyDelete
  11. "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...

    ReplyDelete
  12. 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?

    ReplyDelete
  13. Nice article,

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

    John.

    ReplyDelete
  14. 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 ?

    ReplyDelete
  15. 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

    ReplyDelete
  16. Great help ......

    Thanks for your tutorials

    ReplyDelete
  17. yes this what i need thanks for ur tutorial its great

    ReplyDelete
  18. very nice article. keep it up

    ReplyDelete
  19. Thanks for sharing..

    ReplyDelete
  20. 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.

    ReplyDelete
  21. 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

    ReplyDelete
  22. 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

    ReplyDelete
  23. Thanks and more power ;)

    ReplyDelete
  24. Thanks in advance!

    ReplyDelete
  25. hi how to connect php with MSSQL procedure

    ReplyDelete
  26. Hi Srinivas...

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

    thanks

    ReplyDelete
  27. 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

    ReplyDelete
  28. Nice work. Very usefull.

    ReplyDelete
  29. This is really help us . thank you for nice tutorial

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

    ReplyDelete
  31. 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!

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

    ReplyDelete
  33. how to write update query in phpmyadmin?

    ReplyDelete
  34. How to pass parameters in select procedure.???

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

    ReplyDelete
  36. 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..

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

    ReplyDelete
  38. how to code mysql and php Stored Procedure Output ,

    you provided Stored Procedure Input

    ReplyDelete
  39. Thanx 4 post really helpful to me

    ReplyDelete
  40. Nice code and easy to understand.

    ReplyDelete

Make in India