PHP

Store Procedure In PHP

A stored procedure is a set of SQL commands that has 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
)
As
	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 : Data Values 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>";
}
 }
?>
View Demo

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

Recommended Tutorials for you


Related posts

How to convert number to String in PHP

Anuj Kumar

How to insert data into MySql using PHP

Anuj Kumar

How to get current page URL in php

Anuj Kumar

How to fetch data in excel or generate excel file in PHP

Anuj Kumar

PHP Echo / Print

Anuj Kumar

Types of Errors in PHP

Anuj Kumar

How to delete multiple records in PHP

Anuj Kumar

Hit counter in PHP

Anuj Kumar

Session In PHP

Anuj Kumar

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.