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);
}
}
}
?>
Nice work, Keep it up.
ReplyDeleteHello guys..i have a problem.. i got a table with name 'courses' and the columns are: course_ID, course_name, course_category, course_ECTS, course_room.. I want to ask where i put the course_ID column in the php files.. I have done successfully the insert stored procedure but i failed to do the update and delete stored procedures. please help!!
ReplyDeletethnx and great job guys!!
Check your created table
DeleteNice job sir
ReplyDeleteThank you guys. I learn more by following the video and from the source code. Keep it up
ReplyDeletenice thanks for the tutorial
ReplyDeletenice one but need without Procedure system
ReplyDeleteThis function works well thank you so much 1st tutorial i found like this
ReplyDeleteGreat example, all works fine!
ReplyDeleteFantastic example, Everything works good
ReplyDeleteThank you sir, great tutorial as well as Excellent. Keep up on it.
ReplyDeletethe importance of the sql procedures is to minimize the repetitive queries
ReplyDeleteso always remember , to prepare it once like when you are creating a database, you just create it once , not everytime.
first of all, Thank you for this effort, I'm trying to insert and update extra columns in my database, but insert not working to new columns, can you clarify the best way to do that. 🙏🙏
ReplyDelete