How To Create Stored Procedure In Php Mysql

Stored Procedure In PHP

A stored procedure is a set of SQL commands that have been compiled and stored on the database server.
Once the stored procedure has been "stored", client applications can execute the stored procedure over and over again without sending it to the database server again and without compiling it again.
Stored procedures improve performance by reducing network traffic and CPU load.
Comparison with dynamic SQL

Remove overhead
Avoidance of network traffic
Encapsulation of business logic
Delegation of access-rights
Some protection from SQL injection attacks

How To Create And Execute SP
There are various options that can be used to create stored procedures. In these next few topics we will discuss creating a stored procedure and How to execute.
Syntax –

Create Proc SP_Name

(

Param1 DataType ,

param2 DataType ,

Param3 DataType ,

Paramn DataType

)

Begin

Body Of Store Procedure

End

How to insert Data into Database Using Stored Procedure
Create a Sql Table with name user. Structure of the user table given below….

CREATE TABLE ` user ` (

` id ` int ( 11 ) NOT NULL ,

` name ` varchar ( 255 ) NOT NULL ,

` email ` varchar ( 255 ) NOT NULL ,

` contactno ` bigint ( 11 ) NOT NULL ,

` addrss ` longtext NOT NULL

) ENGINE = InnoDB DEFAULT CHARSET = latin1 ;

Now create a database connection

<?php

define ( 'DB_SERVER' , 'localhost' ) ;

define ( 'DB_USER' , 'root' ) ;

define ( 'DB_PASS' , '' ) ;

define ( 'DB_NAME' , 'test' ) ;

$con = mysqli_connect ( DB_SERVER , DB_USER , DB_PASS , DB_NAME ) ;

// Check connection

if ( mysqli_connect_errno ( ) )

{

echo "Failed to connect to MySQL: " . mysqli_connect_error ( ) ;

}

Create a stored Procedure with name insertuser.

DELIMITER $

CREATE PROCEDURE insertuser

( IN name VARCHAR ( 255 ) , IN email VARCHAR ( 255 ) , IN contactno BIGINT , IN addrss LONGTEXT )

insert into user ( name , email , contactno , addrss ) VALUES ( name , email , contactno , addrss ) $

Argument Modes
IN : Data Values comes in form the calling process and is not changed.
OUT : No Data Value comes in form the calling process; on normal exit, value of argument is passed back to caller.
IN OUT : DataValues comes in form the calling process, and another value is returned on normal exit.
Now create a HTML form (Index.php)

< form name = "stmt" method = "post" >

< table >

< tr >

< td > Name : < / td >

< td > < input type = "text" name = "name" required = "required" / > < / td >

< / tr >

< tr >

< td > Email : < / td >

< td > < input type = "email" name = "email" required = "required" / > < / td >

< / tr >

< tr >

< td > Contact no . : < / td >

< td > < input type = "text" name = "contact" required = "required" / > < / td >

< / tr >

< tr >

< td > Address : < / td >

< td > < textarea name = "addrss" cols = "30" rows = "4" required = "required" > < / textarea > < / td >

< / tr >

< tr >

< td > < / td >

< td > < input type = "submit" name = "submit" value = "Submit" / > < / td >

< / tr >

< / table >

< / form >

Now execute the store Procedure. Put this on the top of the index Page

<?php

include ( 'config.php' ) ;

if ( isset ( $_POST [ 'submit' ] ) )

{

$name = $_POST [ 'name' ] ;

$email = $_POST [ 'email' ] ;

$contact = $_POST [ 'contact' ] ;

$addrss = $_POST [ 'addrss' ] ;

// Code for execute the Store Procedure

$sql = mysqli_query ( $con , "CALL insertuser('$name','$email','$contact','$addrss')" ) ;

if ( $sql )

{

echo "<script>alert('Data Inserted');</script>" ;

}

else

{

echo "<script>alert('not inserted');</script>" ;

}

How to run demo on localhost
First, download the zip package.
Extract the zip file and paste the file in root directory.
Now create a database name with test and import the SQL file available inside the package.
Create a store procedure available inside the package copy .

Download Source Code(Store Procedure In PHP)

Size: 1.84 KB

Version: V 1.0

Anuj Kumar

Hi! I am Anuj Kumar, a professional web developer with 5+ years of experience in this sector. I found PHPGurukul in September 2015. My keen interest in technology and sharing knowledge with others became the main reason for starting PHPGurukul. My basic aim is to offer all web development tutorials like PHP, PDO, jQuery, PHP oops, and MySQL, etc. Apart from the tutorials, we also offer you PHP Projects, and we have around 80+ PHP Projects for you.