In this article, I will guide you about crud operation in PHP using AJAX. We will do create, read, update and delete, we will step by step guide to do crud operation in PHP using AJAX.
To do the crud operation in PHP using ajax we will follow the below steps -:
- Create Database
- Connection establish with database
- Create a listing file
- Perform CRUD Operation
- To build a dynamic, form-based Web application using Ajax
- Test the code
Step 1: Create Database
To create the database you will have to go to your PHPMyAdmin create a database named as a user after that create a table named crud, crud table will contain the following columns Id, name, email, phone, city and action where Id will be our primary key. Below you can see how it looks.
Step 2: Create a database connection.
To create a database connection we will write the following code.
Go to your htdocs, create a folder named learn-php and inside we can create one more folder named ajaxCURD and under that folder create a file named database.php and add the below code into it.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "user";
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
?>Step 3: Create a Listing File
Now we will create a listing file, where we will show all data which is stored in our database, so in your ajaxCRUD folder create a file named index.php and add the below code into it.
<?php
include 'database.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>User Data</title>
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto|Varela+Round">
<link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<link rel="stylesheet" href="css/style.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script src="ajax.js"></script>
</head>
<body>
<div class="container">
<p id="success"></p>
<div class="table-wrapper">
<div class="table-title">
<div class="row">
<div class="col-sm-6">
<h2>Manage <b>Users</b></h2>
</div>
<div class="col-sm-6">
<a href="#addEmployeeModal" class="btn btn-success" data-toggle="modal"><i class="material-icons"></i> <span>Add New User</span></a>
<a href="JavaScript:void(0);" class="btn btn-danger" id="delete_multiple"><i class="material-icons"></i> <span>Delete</span></a>
</div>
</div>
</div>
<table class="table table-striped table-hover">
<thead>
<tr>
<th>
<span class="custom-checkbox">
<input type="checkbox" id="selectAll">
<label for="selectAll"></label>
</span>
</th>
<th>SL NO</th>
<th>NAME</th>
<th>EMAIL</th>
<th>PHONE</th>
<th>CITY</th>
<th>ACTION</th>
</tr>
</thead>
<tbody>
<?php
$result = mysqli_query($conn,"SELECT * FROM crud");
$i=1;
while($row = mysqli_fetch_array($result)) {
?>
<tr id="<?php echo $row['id']; ?>">
<td>
<span class="custom-checkbox">
<input type="checkbox" class="user_checkbox" data-user-id="<?php echo $row["id"]; ?>">
<label for="checkbox2"></label>
</span>
</td>
<td><?php echo $i; ?></td>
<td><?php echo $row["name"]; ?></td>
<td><?php echo $row["email"]; ?></td>
<td><?php echo $row["phone"]; ?></td>
<td><?php echo $row["city"]; ?></td>
<td>
<a href="#editEmployeeModal" class="edit" data-toggle="modal">
<i class="material-icons update" data-toggle="tooltip"
data-id="<?php echo $row['id']; ?>"
data-name="<?php echo $row['name']; ?>"
data-email="<?php echo $row['email']; ?>"
data-phone="<?php echo $row['phone']; ?>"
data-city="<?php echo $row['city']; ?>"
title="Edit"></i>
</a>
<a href="#deleteEmployeeModal" class="delete" data-id="<?php echo $row["id"]; ?>" data-toggle="modal"><i class="material-icons" data-toggle="tooltip"
title="Delete"></i></a>
</td>
</tr>
<?php
$i++;
}
?>
</tbody>
</table>
</div>
</div>
<!-- Add Modal HTML -->
<div id="addEmployeeModal" class="modal fade">
<div class="modal-dialog">
<div class="modal-content">
<form id="user_form">
<div class="modal-header">
<h4 class="modal-title">Add User</h4>
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
</div>
<div class="modal-body">
<div class="form-group">
<label>NAME</label>
<input type="text" id="name" name="name" class="form-control" required>
</div>
<div class="form-group">
<label>EMAIL</label>
<input type="email" id="email" name="email" class="form-control" required>
</div>
<div class="form-group">
<label>PHONE</label>
<input type="phone" id="phone" name="phone" class="form-control" required>
</div>
<div class="form-group">
<label>CITY</label>
<input type="city" id="city" name="city" class="form-control" required>
</div>
</div>
<div class="modal-footer">
<input type="hidden" value="1" name="type">
<input type="button" class="btn btn-default" data-dismiss="modal" value="Cancel">
<button type="button" class="btn btn-success" id="btn-add">Add</button>
</div>
</form>
</div>
</div>
</div>
<!-- Edit Modal HTML -->
<div id="editEmployeeModal" class="modal fade">
<div class="modal-dialog">
<div class="modal-content">
<form id="update_form">
<div class="modal-header">
<h4 class="modal-title">Edit User</h4>
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
</div>
<div class="modal-body">
<input type="hidden" id="id_u" name="id" class="form-control" required>
<div class="form-group">
<label>Name</label>
<input type="text" id="name_u" name="name" class="form-control" required>
</div>
<div class="form-group">
<label>Email</label>
<input type="email" id="email_u" name="email" class="form-control" required>
</div>
<div class="form-group">
<label>PHONE</label>
<input type="phone" id="phone_u" name="phone" class="form-control" required>
</div>
<div class="form-group">
<label>City</label>
<input type="city" id="city_u" name="city" class="form-control" required>
</div>
</div>
<div class="modal-footer">
<input type="hidden" value="2" name="type">
<input type="button" class="btn btn-default" data-dismiss="modal" value="Cancel">
<button type="button" class="btn btn-info" id="update">Update</button>
</div>
</form>
</div>
</div>
</div>
<!-- Delete Modal HTML -->
<div id="deleteEmployeeModal" class="modal fade">
<div class="modal-dialog">
<div class="modal-content">
<form>
<div class="modal-header">
<h4 class="modal-title">Delete User</h4>
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
</div>
<div class="modal-body">
<input type="hidden" id="id_d" name="id" class="form-control">
<p>Are you sure you want to delete these Records?</p>
<p class="text-warning"><small>This action cannot be undone.</small></p>
</div>
<div class="modal-footer">
<input type="button" class="btn btn-default" data-dismiss="modal" value="Cancel">
<button type="button" class="btn btn-danger" id="delete">Delete</button>
</div>
</form>
</div>
</div>
</div>
</body>
</html> Note:- Here, we have using modal class. This modal class uses Bootstrap’s JavaScript modal plugin to add dialogs to your site for lightboxes, user notifications, or completely custom content. If you want more about modal then visit
https://getbootstrap.com/docs/4.0/components/modal/
Step 4: Perform CRUD Operation
- Insert the Data
- Edit the data
- Update the data
- Delete the data
Now we will insert the data, To edit the data, To update the data and To delete the data into our database. we shall create save.php on a single page.
To perform all CRUD operation like Insert, Modify, Update and Delete of the data in our database in save.php. write the below code.
<?php
include 'database.php';
if(count($_POST)>0){
if($_POST['type']==1){
$name=$_POST['name'];
$email=$_POST['email'];
$phone=$_POST['phone'];
$city=$_POST['city'];
$sql = "INSERT INTO `crud`( `name`, `email`,`phone`,`city`)
VALUES ('$name','$email','$phone','$city')";
if (mysqli_query($conn, $sql)) {
echo json_encode(array("statusCode"=>200));
}
else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
}
}
if(count($_POST)>0){
if($_POST['type']==2){
$id=$_POST['id'];
$name=$_POST['name'];
$email=$_POST['email'];
$phone=$_POST['phone'];
$city=$_POST['city'];
$sql = "UPDATE `crud` SET `name`='$name',`email`='$email',`phone`='$phone',`city`='$city' WHERE id=$id";
if (mysqli_query($conn, $sql)) {
echo json_encode(array("statusCode"=>200));
}
else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
}
}
if(count($_POST)>0){
if($_POST['type']==3){
$id=$_POST['id'];
$sql = "DELETE FROM `crud` WHERE id=$id ";
if (mysqli_query($conn, $sql)) {
echo $id;
}
else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
}
}
if(count($_POST)>0){
if($_POST['type']==4){
$id=$_POST['id'];
$sql = "DELETE FROM crud WHERE id in ($id)";
if (mysqli_query($conn, $sql)) {
echo $id;
}
else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
}
}
?>Step 4: To build a dynamic, form-based Web application using Ajax.
suppose a user fills out a form to add data to a database table. Without Ajax, the validity of data in the form is not checked until the form is submitted. With Ajax, the data added to the form can be dynamically validated as the data is added to form fields using business logic in a server application. Thus, a complete form does not have to be posted to the server to check if the data in the form is valid.
For this, we have to create ajax.js and attached this file to index.html. write the below code.
$(document).on('click','#btn-add',function(e) {
var data = $("#user_form").serialize();
$.ajax({
data: data,
type: "post",
url: "backend/save.php",
success: function(dataResult){
var dataResult = JSON.parse(dataResult);
if(dataResult.statusCode==200){
$('#addEmployeeModal').modal('hide');
alert('Data added successfully !');
location.reload();
}
else if(dataResult.statusCode==201){
alert(dataResult);
}
}
});
});
$(document).on('click','.update',function(e) {
var id=$(this).attr("data-id");
var name=$(this).attr("data-name");
var email=$(this).attr("data-email");
var phone=$(this).attr("data-phone");
var city=$(this).attr("data-city");
$('#id_u').val(id);
$('#name_u').val(name);
$('#email_u').val(email);
$('#phone_u').val(phone);
$('#city_u').val(city);
});
$(document).on('click','#update',function(e) {
var data = $("#update_form").serialize();
$.ajax({
data: data,
type: "post",
url: "backend/save.php",
success: function(dataResult){
var dataResult = JSON.parse(dataResult);
if(dataResult.statusCode==200){
$('#editEmployeeModal').modal('hide');
alert('Data updated successfully !');
location.reload();
}
else if(dataResult.statusCode==201){
alert(dataResult);
}
}
});
});
$(document).on("click", ".delete", function() {
var id=$(this).attr("data-id");
$('#id_d').val(id);
});
$(document).on("click", "#delete", function() {
$.ajax({
url: "backend/save.php",
type: "POST",
cache: false,
data:{
type:3,
id: $("#id_d").val()
},
success: function(dataResult){
$('#deleteEmployeeModal').modal('hide');
$("#"+dataResult).remove();
}
});
});
$(document).on("click", "#delete_multiple", function() {
var user = [];
$(".user_checkbox:checked").each(function() {
user.push($(this).data('user-id'));
});
if(user.length < = 0) {
alert('Please select records');
}
else {
WRN_PROFILE_DELETE = "Are you sure you want to delete "+(user.length>1?"these":"this")+" row?";
var checked = confirm(WRN_PROFILE_DELETE);
if(checked == true) {
var selected_values = user.join(",");
console.log(selected_values);
$.ajax({
type: "POST",
url: "backend/save.php",
cache:false,
data:{
type: 4,
id : selected_values
},
success: function(response) {
var ids = response.split(",");
for (var i=0; i < ids.length; i++ ) {
$("#"+ids[i]).remove();
}
}
});
}
}
});
$(document).ready(function(){
$('[data-toggle="tooltip"]').tooltip();
var checkbox = $('table tbody input[type="checkbox"]');
$("#selectAll").click(function(){
if(this.checked){
checkbox.each(function(){
this.checked = true;
});
} else{
checkbox.each(function(){
this.checked = false;
});
}
});
checkbox.click(function(){
if(!this.checked){
$("#selectAll").prop('checked', false);
}
});
})Step 5: Test the code
Now we will test the whole code, is this working fine or not. To test the code visit the URL http://localhost/learn-php/ajaxCRUD in your browser.
Now click on add new user button to insert the data into it.
Now we will edit the page, click on edit icon.
Now we will edit the page, click on update button.
when we click on the update button, then we got an alert message “Data Uploaded Successfully!!!“
Now we will edit the page, click on delete.
Here we shall have 3 option, To delete the user details :-
- Delete the data from a single column.
- Select one or more data to remove from the table by using the checkbox.
- Select all to remove entire data from the table.
1. Delete the data from a single column.
2. Select one or more data to remove from the table by using the CHECKBOX.
3. Select all to remove entire data from the table.
when we delete all the data from the table then we got an empty table as below
Read Also: Simple Crud operation in PHP
