If you are looking for tutorial on Stored Procedure for Crud operation in PHP with Ajax Jquery then we are going to discuss one more advance topic in web development. In this tutorial we will discuss how can we use store procedure with Ajax and Jquery for Insert Update Delete and fetch data from Mysql table in php script. In this tutorial we will make crud operation in php with ajax. For making crud operation we will use stored procedure for making crud operation in php with Ajax. In this crud operation we have use Ajax with php so all operation will be done without page refresh. Here crud means create, read, update and delete of database table data. Insert, Update, and Delete features is almost use in every website in PHP with Ajax. In this script, we will fetch users data from database and display that user data on web page with update link and delete link. By this type of link we can update and delete data from database. This operation has been done without page refresh event. For this type of operation here we will use stored procedure. First of all what is stored procedure. Stored procedure is a collection of query. It is also called pre compiled sql query. Stored Procedure is mainly used for enterprise level application. If you are working on any large data project then you can use stored procedure for Insert Update Delete of data operation. So here we will stored procedure from crud operation in php with Ajax.
This is our complete system in which we can fetch, insert, edit and delete data from database by using stored procedure in php script with Ajax request. Insert, Fetch, Update and delete records from database by using php is a very simple feature for new programmer. But here is something complex task, In this task first of all we have use stored procedure for crud operation and second is we have use ajax method with stored procedure for crud operation. So it is something new feature, if you have working on very large data application then you can use stored procedure and with Ajax request. You application working speed will be increase than normal php application. This is because stored procedure execute query at database server not on php script. So your application will execute faster than normal php application. Insert Update delete and fetch data is the normal operation in any web application. So if this operation perform fast then your application will run fast. So for execute this basic operation fast, then you can use stored procedure in your application. Mainly stored procedure are stored in your data base and it will execute from database so it will reduce execution load on your php page. So this way we can increase execution speed of any web application by using stored procedure in php script with Ajax method.
Source Code
Users
--
-- Database: `crud`
--
CREATE DATABASE `crud` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `crud`;
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(150) NOT NULL,
`last_name` varchar(150) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`id`, `first_name`, `last_name`) VALUES
(17, 'Rosie', 'Peele'),
(18, 'Joseph', 'Harman'),
(19, 'John', 'Moss'),
(20, 'Lillie', 'Ferrari'),
(21, 'Yolanda', 'Green'),
(22, 'Cara', 'Gariepy');
index.php
<html>
<head>
<title>PHP Ajax Crud - Insert Update Delete with Stored Procedure</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<style>
body
{
margin:0;
padding:0;
background-color:#f1f1f1;
}
.box
{
width:750px;
padding:20px;
background-color:#fff;
border:1px solid #ccc;
border-radius:5px;
margin-top:100px;
}
</style>
</head>
<body>
<div class="container box">
<h3 align="center">PHP Ajax Crud - Insert Update Delete with Stored Procedure</h3>
<br /><br />
<br /><br />
<label>Enter First Name</label>
<input type="text" name="first_name" id="first_name" class="form-control" />
<br />
<label>Enter Last Name</label>
<input type="text" name="last_name" id="last_name" class="form-control" />
<br /><br />
<div align="center">
<input type="hidden" name="id" id="user_id" />
<button type="button" name="action" id="action" class="btn btn-warning">Add</button>
</div>
<br />
<br />
<div id="result" class="table-responsive">
</div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
fetchUser();
function fetchUser()
{
var action = "select";
$.ajax({
url : "select.php",
method:"POST",
data:{action:action},
success:function(data){
$('#first_name').val('');
$('#last_name').val('');
$('#action').text("Add");
$('#result').html(data);
}
});
}
$('#action').click(function(){
var firstName = $('#first_name').val();
var lastName = $('#last_name').val();
var id = $('#user_id').val();
var action = $('#action').text();
if(firstName != '' && lastName != '')
{
$.ajax({
url : "action.php",
method:"POST",
data:{firstName:firstName, lastName:lastName, id:id, action:action},
success:function(data){
alert(data);
fetchUser();
}
});
}
else
{
alert("Both Fields are Required");
}
});
$(document).on('click', '.update', function(){
var id = $(this).attr("id");
$.ajax({
url:"fetch.php",
method:"POST",
data:{id:id},
dataType:"json",
success:function(data){
$('#action').text("Edit");
$('#user_id').val(id);
$('#first_name').val(data.first_name);
$('#last_name').val(data.last_name);
}
})
});
$(document).on('click', '.delete', function(){
var id = $(this).attr("id");
if(confirm("Are you sure you want to remove this data?"))
{
var action = "Delete";
$.ajax({
url:"action.php",
method:"POST",
data:{id:id, action:action},
success:function(data)
{
fetchUser();
alert(data);
}
})
}
else
{
return false;
}
});
});
</script>
select.php
<?php
//select.php
$output = '';
$connect = mysqli_connect("localhost", "root", "", "crud");
if(isset($_POST["action"]))
{
$procedure = "
CREATE PROCEDURE selectUser()
BEGIN
SELECT * FROM users ORDER BY id DESC;
END;
";
if(mysqli_query($connect, "DROP PROCEDURE IF EXISTS selectUser"))
{
if(mysqli_query($connect, $procedure))
{
$query = "CALL selectUser()";
$result = mysqli_query($connect, $query);
$output .= '
<table class="table table-bordered">
<tr>
<th width="35%">First Name</th>
<th width="35%">Last Name</th>
<th width="15%">Update</th>
<th width="15%">Delete</th>
</tr>
';
if(mysqli_num_rows($result) > 0)
{
while($row = mysqli_fetch_array($result))
{
$output .= '
<tr>
<td>'.$row["first_name"].'</td>
<td>'.$row["last_name"].'</td>
<td><button type="button" name="update" id="'.$row["id"].'" class="update btn btn-success btn-xs">Update</button></td>
<td><button type="button" name="delete" id="'.$row["id"].'" class="delete btn btn-danger btn-xs">Delete</button></td>
</tr>
';
}
}
else
{
$output .= '
<tr>
<td colspan="4">Data not Found</td>
</tr>
';
}
$output .= '</table>';
echo $output;
}
}
}
?>
action.php
<?php
//action.php
if(isset($_POST["action"]))
{
$output = '';
$connect = mysqli_connect("localhost", "root", "", "crud");
if($_POST["action"] =="Add")
{
$first_name = mysqli_real_escape_string($connect, $_POST["firstName"]);
$last_name = mysqli_real_escape_string($connect, $_POST["lastName"]);
$procedure = "
CREATE PROCEDURE insertUser(IN firstName varchar(250), lastName varchar(250))
BEGIN
INSERT INTO users(first_name, last_name) VALUES (firstName, lastName);
END;
";
if(mysqli_query($connect, "DROP PROCEDURE IF EXISTS insertUser"))
{
if(mysqli_query($connect, $procedure))
{
$query = "CALL insertUser('".$first_name."', '".$last_name."')";
mysqli_query($connect, $query);
echo 'Data Inserted';
}
}
}
if($_POST["action"] == "Edit")
{
$first_name = mysqli_real_escape_string($connect, $_POST["firstName"]);
$last_name = mysqli_real_escape_string($connect, $_POST["lastName"]);
$procedure = "
CREATE PROCEDURE updateUser(IN user_id int(11), firstName varchar(250), lastName varchar(250))
BEGIN
UPDATE users SET first_name = firstName, last_name = lastName
WHERE id = user_id;
END;
";
if(mysqli_query($connect, "DROP PROCEDURE IF EXISTS updateUser"))
{
if(mysqli_query($connect, $procedure))
{
$query = "CALL updateUser('".$_POST["id"]."', '".$first_name."', '".$last_name."')";
mysqli_query($connect, $query);
echo 'Data Updated';
}
}
}
if($_POST["action"] == "Delete")
{
$procedure = "
CREATE PROCEDURE deleteUser(IN user_id int(11))
BEGIN
DELETE FROM users WHERE id = user_id;
END;
";
if(mysqli_query($connect, "DROP PROCEDURE IF EXISTS deleteUser"))
{
if(mysqli_query($connect, $procedure))
{
$query = "CALL deleteUser('".$_POST["id"]."')";
mysqli_query($connect, $query);
echo 'Data Deleted';
}
}
}
}
?>
fetch.php
<?php
//fetch.php
$connect = mysqli_connect("localhost","root", "", "crud");
if(isset($_POST["id"]))
{
$output = array();
$procedure = "
CREATE PROCEDURE whereUser(IN user_id int(11))
BEGIN
SELECT * FROM users WHERE id = user_id;
END;
";
if(mysqli_query($connect, "DROP PROCEDURE IF EXISTS whereUser"))
{
if(mysqli_query($connect, $procedure))
{
$query = "CALL whereUser(".$_POST["id"].")";
$result = mysqli_query($connect, $query);
while($row = mysqli_fetch_array($result))
{
$output['first_name'] = $row["first_name"];
$output['last_name'] = $row["last_name"];
}
echo json_encode($output);
}
}
}
?>
