create and call procedure in mysql ( Updated 2016 )

By Prem Tiwari in Mysql, Oracle. Updated on November 7, 2016

In this post i will explain how to create store procedure in mysql and how to call store procedure in PHP for select, insert, update and delete data from mysql table. A stored procedure has a header and a body using to declarative SQL statements stored inside the database catalog.

There are three different ways to pass parameters in store procedures in mysql:
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters

Database table

First create user table with id, name and email fields in your test database with mysql. Open phpmyadmin and go to the SQL tabs. Now copy paste below code and execute this query. After successfully execution of below query users table will be created in your selected database.

CREATE TABLE users

(

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(50),

email VARCHAR(50) UNIQUE

);

create-table-in-mysql

how to create store procedure in mysql

Now again go to the SQL tab in phpmyadmin and copy paste below code, then execute query. Now you have created a stored procedure named “UserInfo” which will select all rows from users tables.

DELIMITER //

CREATE PROCEDURE or replace UserInfo()

BEGIN

SELECT name,email FROM users

END

Create config.php file

Create config.php file for configure database connection with your mysql database. Add below code in your config.php file and save.

< ?PHP

//connect to database

$conn=mysql_connect("hostname","database username","database password") or die(mysql_error());

//select your database name

$dbc=mysql_select_db("testdb",$conn);

if (!$dbc) {

die('Could not select: ' . mysql_error());

}

how call store procedure in php code

Create index.php file and copy paste below code and save it. In below code first include config.php to connect your PHP code with mysql database, then call your procedure by using call UserInfo().

< ?PHP

//include db config file

include('config.php');

//call the procedure

$sql=mysql_query('call UserInfo()');

if (!$sql) {

die('syntax error in procedure: ' . mysql_error());

}

while($row=mysql_fetch_object($sql)):

echo $row->name.'<br>';

echo $row->email.'<br>';

endwhile;

how to insert data using stored procedure

DELIMITER //

CREATE PROCEDURE insert(IN name VARCHAR(50),IN email 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 page

After above steps now create insert.php file and copy paste below code. Now go to your web browser and run the insert.php page. After insert.php page runed value inserted in users table.

<?php

//include db config file

include('config.php');

$name='freewebmentor';

$name='[email protected]';

$sql=mysql_query('CALL insert("$name","$email")');

if (!$sql) {

die('Oops something wrong : ' . mysql_error());

}

Thanks for giving you valuable time on Freewebmentor community. Do like & share with you friends using social media if you liked.

Prem Tiwari

An engineer by profession and a passionate blogger by heart. Founder of FreeWebMentor.com (A Programming blog for beginners), Tech Speaker at various forums. A part from this he is an open source enthusiast, WordPress Lover, Blogger, SEO, and Growth Strategic.

You might also like:

Tags: create and call procedure in mysql, create parameters in stored procedure, how to create store procedure, mysql call procedure in select, mysql create procedure example, mysql create procedure parameters, mysql delimiter, mysql how to pass parameters, mysql stored function example, php call mysql procedure, show procedures in mysql, store procedure in mysql