In this tutorial, I am going to explain the crud operation in PHP using ajax jquery in MySQL as given below—-
1. How to insert, edit, and delete the form data in PHP using ajax jquery i.e. without reloading the page.
2. How to validate the HTML form data in PHP after submission using jquery.
3. How to keep HTML form data after the validation error is shown.
Also read, How to insert multiple checkbox values in database in PHP
First of all, We have to create a table to insert the data using ajax jquery. Here I am using a table named ‘users’ in my database.
DDL information of the table
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`phone_no` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`email` varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`password` varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
A brief summary of the Code for CRUD Operation in PHP Using Ajax Jquery
First, we will send the form data to ajax request with the help of jquery and receive the response using ‘JSON’. Jquery will send the data to the PHP script and we will communicate with the MySQL server with the help of the PHP script when we receive the JSON response from the MySQL server then we will encode the data with the help of a function called json_encode() and return the data to the browser without reloading the page.
COMPLETE CODE:-
<?php
include('connect.php');
if(isset($_POST['register'])){
if(empty($_POST['username'])){
$error['username'] = "Please enter the username";
}
else{
$username = $_POST['username'];
}
if(empty($_POST['phone_no'])){
$error['phone'] = "Please enter the phone No";
}
else{
$phone = $_POST['phone_no'];
}
if(empty($_POST['email'])){
$error['email'] = "Please enter the email";
}
else{
$email = $_POST['email'];
}
if(empty($_POST['password'])){
$error['password'] = "Please enter the password";
}
else{
$password = $_POST['password'];
}
if(isset($error)){
$data['error'] = $error;
echo json_encode($data);
}
else{
$sql="INSERT INTO users(username,phone_no,email,password)VALUES('$username','$phone','$email','$password')";
$stmt=$con->prepare($sql);
$stmt->execute();
//get the last insert ID
$last_id = $con->lastInsertId();
$stmt1=$con->prepare("select * from users where id='$last_id'");
$stmt1->execute();
$user_details = $stmt1->fetch(PDO::FETCH_ASSOC);
//$data['success'] = $con->lastInsertId();
$data['id'] = $user_details['id'];
$data['username'] = $user_details['username'];
$data['phone_no'] = $user_details['phone_no'];
$data['email'] = $user_details['email'];
$data['password'] = $user_details['password'];
echo json_encode($data);
}
exit();
}
//edit the user details
if(isset($_POST['edit_id'])){
$editid = $_POST['edit_id'];
if($editid>0){
$stmt2 = $con->prepare("select * from users where id='$editid'");
$stmt2->execute();
$edit_details = $stmt2->fetch(PDO::FETCH_ASSOC);
$output['username'] = $edit_details['username'];
$output['phone_no'] = $edit_details['phone_no'];
$output['email'] = $edit_details['email'];
$output['password'] = $edit_details['password'];
echo json_encode($output);
}
exit;
}
//update the data
if(isset($_POST['update'])){
$updateid = $_POST['update'];
$username = $_POST['username'];
$phoneno = $_POST['phone_no'];
$email = $_POST['email'];
$password = $_POST['password'];
$stmt = $con->prepare("update users set username='$username',phone_no='$phoneno',email='$email',password='$password' where id='$updateid'");
$stmt->execute();
//get the data as the json response
$stmt3 = $con->prepare("select * from users where id='$updateid'");
$stmt3->execute();
$userdetails = $stmt3->fetch(PDO::FETCH_ASSOC);
$output['id'] = $userdetails['id'];
$output['username'] = $userdetails['username'];
$output['phone_no'] = $userdetails['phone_no'];
$output['email'] = $userdetails['email'];
$output['password'] = $userdetails['password'];
echo json_encode($output);
exit;
}
if(isset($_POST['delete'])){
$deleteid = $_POST['delete'];
$stmt = $con->prepare("delete from users where id='$deleteid'");
$stmt->execute();
echo json_encode($stmt);
exit;
}
//fetch data from the table
$stmt3=$con->query("select * from users order by id asc");
$stmt3->execute();
$rows=$stmt3->fetchAll(PDO::FETCH_ASSOC);
?>
<html>
<head>
<title>ajax example</title>
<link rel="stylesheet" href="bootstrap.css" crossorigin="anonymous">
<!-- Optional theme -->
<link rel="stylesheet" href="bootstrap-theme.css" crossorigin="anonymous">
<style>
.container{
width:50%;
height:30%;
padding:20px;
}
.container1{
width:50%;
height:30%;
padding:20px;
}
</style>
</head>
<body>
<div class="container">
<h3 align="center"><u>CRUD FORM USING AJAX</u></h3>
<br/><br/><br/>
<form class="form-horizontal" action="#">
<div id="newuser"></div>
<div class="form-group">
<label class="control-label col-sm-2" for="email">Username:</label>
<div class="col-sm-10">
<input type="text" class="form-control" name="username" id="user" placeholder="Enter Username">
</div>
</div>
<div id="userdiv" style="color: #ff0000"></div><br/>
<div class="form-group">
<label class="control-label col-sm-2" for="phone">Phone No*:</label>
<div class="col-sm-10">
<input type="text" class="form-control" name="phone_no" id="pn" placeholder="Enter Phone No">
</div>
</div>
<div id="phonediv" style="color: #ff0000"></div><br/>
<div class="form-group">
<label class="control-label col-sm-2" for="email">Email:</label>
<div class="col-sm-10">
<input type="email" class="form-control" name="email" id="email" placeholder="Enter email">
</div>
</div>
<div id="emaildiv" style="color: #ff0000"></div><br/>
<div class="form-group">
<label class="control-label col-sm-2" for="pwd">Password:</label>
<div class="col-sm-10">
<input type="password" name="password" class="form-control" id="pwd" placeholder="Enter password">
</div>
</div>
<div id="passdiv" style="color: #ff0000"></div><br/>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<button type="button" class="btn btn-primary regBtn" id="reg" name="register" value="register">Submit</button>
</div>
</div>
</form>
<br/><br/>
<h3><u>Added User List</u></h3><br/>
<table class="table table-striped">
<thead>
<tr>
<th>id</th>
<th>username</th>
<th>phone no</th>
<th>email</th>
<th>password</th>
<th colspan="2">Operations</th>
</tr>
</thead>
<tbody id="listuser">
<?php
foreach($rows as $row)
{
if(!$row){
?>
<tr>
<td colspan="7" style="text-align: center">No user found</td>
</tr>
<?php
}
else{
?>
<tr id="listrow<?php echo $row['id'];?>">
<td><?php echo $row['id'];?></td>
<td><?php echo $row['username'];?></td>
<td><?php echo $row['phone_no'];?></td>
<td><?php echo $row['email'];?></td>
<td><?php echo $row['password'];?></td>
<td><button type="button" class="btn btn-danger btn-sm deleteBtn" id="<?php echo $row['id']?>" data-target="#deletemodal" data-toggle="modal" data-backdrop="false">Delete</button>
<button type="button" class="btn btn-info btn-sm editBtn" id="<?php echo $row['id']?>" data-target="#editmodal" data-toggle="modal" data-backdrop="false">Edit</button>
<td>
</tr>
<?php
}
}
?>
</tbody>
</table>
</div>
<!--Editmodel-->
<div id="editmodal" class="modal fade" role="dialog">
<div class="modal-dialog">
<!-- Modal content-->
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal"></button>
<h4 class="modal-title">Edit User Details</h4>
</div>
<div class="modal-body">
<div id="showupdate" style="color: #ff0000"></div>
<br/>
<form method="post">
<div id="edituser"></div>
<div class="form-group">
<div class="row">
<div class="col-sm-6">
<label for="username">Username:</label>
<input type="text" name="username" class="form-control" id="useredit">
</div>
<div class="col-sm-6">
<label for="username">Phone No*:</label>
<input type="text" name="phoneedit" class="form-control" id="phoneedit">
</div>
</div>
</div>
<div class="form-group">
<div class="row">
<div class="col-sm-6">
<label for="username">Email:</label>
<input type="text" name="email" class="form-control" id="emailedit">
</div>
<div class="col-sm-6">
<label for="username">Password:</label>
<input type="password" name="password" class="form-control" id="passedit">
</div>
</div>
<input type="hidden" id="edtid">
</div><br/>
<button type="button" class="btn btn-primary btn-sm pull-left upBtn" name="update">Update</button>
</form>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-primary close" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
<!--End edit Model-->
<script src="jquery-3.2.1.min.js"></script>
<script src="bootstrap.min.js"></script>
<script>
//insert Data
$('#reg').click(function(){
var username = $('#user').val();
var phone = $('#pn').val();
var email = $('#email').val();
var password = $('#pwd').val();
$.ajax({
url: 'crudform.php',
type: 'POST',
data: {"username":username,"phone_no":phone,"email":email,"password":password,'register':1},
dataType: 'json'
})
.done(function(data){
if(typeof(data.error)!=='undefined')
{
if (typeof(data.error.username)!=='undefined' || typeof(data.error.phone)!=='undefined' || typeof(data.erroremail)!=='undefined' || typeof(data.error.password)!=='undefined'){
$('#userdiv').html(data.error.username);
$('#phonediv').html(data.error.phone);
$('#emaildiv').html(data.error.email);
$('#passdiv').html(data.error.password);
}
}
else{
$('#newuser').html('<div class="alert alert-success" role="alert">New User added successfully</div>');
$('#user').val('');
$('#pn').val('');
$('#email').val('');
$('#pwd').val('');
$('.error').html('');
var new_row = '<tr><td>'+data.id+'</td><td>'+data.username+'</td><td>'+data.phone_no+'</td><td>'+data.email+'</td><td>'+data.password+'</td><td><button class="btn btn-danger btn-sm delBtn" data="'+ data.id+'" data-target="#deletemodal" data-toggle="modal" data-backdrop="false">Delete</button><button class="btn btn-info btn-sm edtBtn" data="'+ data.id+'" data-target="#editmodal" data-toggle="modal" data-backdrop="false">Edit</button></td></tr>';
$('#listuser').append(new_row);
}
})
.fail(function(xhr,textStatus,errorThrown,data)
{
//alert(textStatus);
alert(textStatus);
});
});
$('.editBtn').click(function(){
var edit_id = $(this).attr('id');
//alert(edit_id);
$.ajax({
url: 'crudform.php',
type: 'post',
data: {
'edit_id': edit_id,
},
dataType: 'json',
})
.done(function(data){
$('#useredit').val(data.username);
$('#phoneedit').val(data.phone_no);
$('#emailedit').val(data.email);
$('#passedit').val(data.password);
$('#edtid').val(edit_id);
})
.fail(function(data){
alert(data);
});
});
//update the data
$('.upBtn').click(function(){
var username = $('#useredit').val();
var phoneno = $('#phoneedit').val();
var email = $('#emailedit').val();
var password = $('#passedit').val();
var update_id = $('#edtid').val();
//alert(username+phoneno+email+password+update_id);
$.ajax({
url: 'crudform.php',
type: 'post',
data: {
'username': username,
'phone_no': phoneno,
'email': email,
'password': password,
'update': update_id,
},
dataType: 'json',
})
.done(function(data){
var newrow = '<tr><td>'+data.id+'</td><td>'+data.username+'</td><td>'+data.phone_no+'</td><td>'+data.email+'</td><td>'+data.password+'</td><td><button class="btn btn-danger btn-sm delBtn" data="'+ data.id+'" data-target="#deletemodal" data-toggle="modal" data-backdrop="false">Delete</button> <button class="btn btn-info btn-sm edtBtn" data="'+ data.id+'" data-target="#editmodal" data-toggle="modal" data-backdrop="false">Edit</button></td></tr>';
$('#showupdate').html('Data Updated successfully');
$('#useredit').val('');
$('#phoneedit').val('');
$('#emailedit').val('');
$('#passedit').val('');
$('#listrow'+data.id).replaceWith(newrow);
})
.fail(function(data){
alert(data);
});
});
//remove all data after clicking close button in model
$('.close').click(function(){
$('#showupdate').html('');
});
//delete the Data
$('.deleteBtn').click(function(){
var delete_id = $(this).attr('id');
//var parent=$(this).parent().parent();
alert('Do u want to delete this row?');
$.ajax({
url: 'crudform.php',
type: 'post',
data: {
'delete': delete_id,
},
dataType: 'json',
})
.done(function(data){
$(this).closest('tr').remove();
})
.fail(function(data){
alert(data);
});
});
</script>
</body>
</html>
Connect.php:-
<?php
$servername='localhost';
$username="root";
$password="";
try
{
$con=new PDO("mysql:host=$servername;dbname=blog",$username,$password);
$con->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
//echo 'connected';
}
catch(PDOException $e)
{
echo '<br>'.$e->getMessage();
}
?>
NOTE*
——–
Download the bootstrap CSS and js files from google and include the path of the files in the href attribute of the link tag and src attribute of the script tag respectively.
Also read, how to insert HTML form data into MySQL database using PHP
CONCLUSION:- I hope this article will help you to understand the concept and perform the CRUD operation in PHP using ajax jquery easily. If you have any doubt then please leave your comment below.