In this tutorial we will learn how to perform PHP CRUD operation using Ajax and jQuery with example.
Content Overview
Step 2: Create Table Configuration
Step 3: Make Database Connection
Step 4: Create Index Page (Included HTML and AJAX)
Step 5: Create CRUD API Using PHP
PHP CRUD Operation Using Ajax and jQuery
Here we are taking example of a Employee Management application, this application perform all PHP CRUD operation without refreshing the page using Ajax, Jquery and Bootstrap 4 modal.
Step 1 - Create database php_ajax
In first step, create a database with name php_ajax .
Step 2 - Create employees table
In this step, create employee table in your database.
CREATE TABLE `employees` (
`id` int(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(20) NOT NULL,
`email` varchar(50) NOT NULL,
`address` varchar(200) NOT NULL,
`phone` varchar(15) NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL );
Step 3 – Make database connection
Create connection.php file in your root folder and make connection with your database ( php_ajax ) using below code.
<?php
$server_name= "localhost";
$user_name= "root";
$password= "";
$database_name= "php_ajax";
$conn= mysqli_connect($server_name , $user_name , $password , $database_name);
if ($conn) {
// echo "connected" ;
}
?>
Step 4 – Create employee.php file
Now create a employee.php file, in this file we have perform our CRUD operation using ajax.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1,shrink-to-fit=no">
<title>PHP Ajax CRUD Data Table for Database with Modal Form</title>
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto|Varela+Round">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<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">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
<style>
body {
color: #566787;
background: #f5f5f5;
font-family: 'Varela Round', sans-serif;
font-size: 13px;
}
.table-responsive {
margin: 30px 0;
}
.table-wrapper {
background: #fff;
padding: 20px 25px;
border-radius: 3px;
min-width: 1000px;
box-shadow: 0 1px 1px rgba(0, 0, 0, .05);
}
.table-title {
padding-bottom: 15px;
background: #435d7d;
color: #fff;
padding: 16px 30px;
min-width: 100%;
margin: -20px -25px 10px;
border-radius: 3px 3px 0 0;
}
.table-title h2 {
margin: 5px 0 0;
font-size: 24px;
}
.table-title .btn-group {
float: right;
}
.table-title .btn {
color: #fff;
float: right;
font-size: 13px;
border: none;
min-width: 50px;
border-radius: 2px;
border: none;
outline: none !important;
margin-left: 10px;
}
.table-title .btn i {
float: left;
font-size: 21px;
margin-right: 5px;
}
.table-title .btn span {
float: left;
margin-top: 2px;
}
table.table tr th,
table.table tr td {
border-color: #e9e9e9;
padding: 12px 15px;
vertical-align: middle;
}
table.table tr th:first-child {
width: 60px;
}
table.table tr th:last-child {
width: 100px;
}
table.table-striped tbody tr:nth-of-type(odd) {
background-color: #fcfcfc;
}
table.table-striped.table-hover tbody tr:hover {
background: #f5f5f5;
}
table.table th i {
font-size: 13px;
margin: 0 5px;
cursor: pointer;
}
table.table td:last-child i {
opacity: 0.9;
font-size: 22px;
margin: 0 5px;
}
table.table td a {
font-weight: bold;
color: #566787;
display: inline-block;
text-decoration: none;
outline: none !important;
}
table.table td a:hover {
color: #2196F3;
}
table.table td a.edit {
color: #FFC107;
}
table.table td a.delete {
color: #F44336;
}
table.table td i {
font-size: 19px;
}
table.table .avatar {
border-radius: 50%;
vertical-align: middle;
margin-right: 10px;
}
/* Modal styles */
.modal .modal-dialog {
max-width: 400px;
}
.modal .modal-header,
.modal .modal-body,
.modal .modal-footer {
padding: 20px 30px;
}
.modal .modal-content {
border-radius: 3px;
font-size: 14px;
}
.modal .modal-footer {
background: #ecf0f1;
border-radius: 0 0 3px 3px;
}
.modal .modal-title {
display: inline-block;
}
.modal .form-control {
border-radius: 2px;
box-shadow: none;
border-color: #dddddd;
}
.modal textarea.form-control {
resize: vertical;
}
.modal .btn {
border-radius: 2px;
min-width: 100px;
}
.modal form label {
font-weight: normal;
}
.loading {
color: black;
font: 300 2em/100% Impact;
text-align: center;
}
/* loading dots */
.loading:after {
content: ' .';
animation: dots 1s steps(5, end) infinite;
}
@keyframes dots {
0%,
20% {
color: rgba(0, 0, 0, 0);
text-shadow:
.25em 0 0 rgba(0, 0, 0, 0),
.5em 0 0 rgba(0, 0, 0, 0);
}
40% {
color: black;
text-shadow:
.25em 0 0 rgba(0, 0, 0, 0),
.5em 0 0 rgba(0, 0, 0, 0);
}
60% {
text-shadow:
.25em 0 0 black,
.5em 0 0 rgba(0, 0, 0, 0);
}
80%,
100% {
text-shadow:
.25em 0 0 black,
.5em 0 0 black;
}
}
</style>
</head>
<body>
<div class="container-xl">
<div class="table-responsive">
<div class="table-wrapper">
<div class="table-title">
<div class="bg-light p-2 m-2">
<h5 class="text-dark text-center">PHP Ajax CRUD operation</h5>
</div>
<div class="row">
<div class="col-sm-6">
<h2>Manage <b>Employees</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 Employee</span></a>
</div>
</div>
</div>
<table class="table table-striped table-hover">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Address</th>
<th>Phone</th>
<th>Actions</th>
</tr>
</thead>
<tbody id="employee_data">
</tbody>
</table>
<p class="loading">Loading Data</p>
</div>
</div>
</div>
<!-- Edit Modal HTML -->
<div id="addEmployeeModal" class="modal fade">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h4 class="modal-title">Add Employee</h4>
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
</div>
<div class="modal-body add_epmployee">
<div class="form-group">
<label>Name</label>
<input type="text" id="name_input" class="form-control" required>
</div>
<div class="form-group">
<label>Email</label>
<input type="email" id="email_input" class="form-control" required>
</div>
<div class="form-group">
<label>Address</label>
<textarea class="form-control" id="address_input" required></textarea>
</div>
<div class="form-group">
<label>Phone</label>
<input type="text" id="phone_input" class="form-control" required>
</div>
</div>
<div class="modal-footer">
<input type="button" class="btn btn-default" data-dismiss="modal" value="Cancel">
<input type="submit" class="btn btn-success" value="Add" onclick="addEmployee()">
</div>
</div>
</div>
</div>
<!-- Edit Modal HTML -->
<div id="editEmployeeModal" class="modal fade">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h4 class="modal-title">Edit Employee</h4>
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
</div>
<div class="modal-body edit_employee">
<div class="form-group">
<label>Name</label>
<input type="text" id="name_input" class="form-control" required>
</div>
<div class="form-group">
<label>Email</label>
<input type="email" id="email_input" class="form-control" required>
</div>
<div class="form-group">
<label>Address</label>
<textarea class="form-control" id="address_input" required></textarea>
</div>
<div class="form-group">
<label>Phone</label>
<input type="text" id="phone_input" class="form-control" required>
<input type="hidden" id="employee_id" class="form-control" required>
</div>
</div>
<div class="modal-footer">
<input type="button" class="btn btn-default" data-dismiss="modal" value="Cancel">
<input type="submit" class="btn btn-info" onclick="editEmployee()" value="Save">
</div>
</div>
</div>
</div>
<!-- View Modal HTML -->
<div id="viewEmployeeModal" class="modal fade">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h4 class="modal-title">View Employee</h4>
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
</div>
<div class="modal-body view_employee">
<div class="form-group">
<label>Name</label>
<input type="text" id="name_input" class="form-control" readonly>
</div>
<div class="form-group">
<label>Email</label>
<input type="email" id="email_input" class="form-control" readonly>
</div>
<div class="form-group">
<label>Address</label>
<textarea class="form-control" id="address_input" readonly></textarea>
</div>
<div class="form-group">
<label>Phone</label>
<input type="text" id="phone_input" class="form-control" readonly>
</div>
</div>
<div class="modal-footer">
<input type="button" class="btn btn-default" data-dismiss="modal" value="Close">
</div>
</div>
</div>
</div>
<!-- Delete Modal HTML -->
<div id="deleteEmployeeModal" class="modal fade">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h4 class="modal-title">Delete Employee</h4>
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
</div>
<div class="modal-body">
<p>Are you sure you want to delete these Records?</p>
<p class="text-warning"><small>This action cannot be undone.</small></p>
</div>
<input type="hidden" id="delete_id">
<div class="modal-footer">
<input type="button" class="btn btn-default" data-dismiss="modal" value="Cancel">
<input type="submit" class="btn btn-danger" onclick="deleteEmployee()" value="Delete">
</div>
</div>
</div>
</div>
<script>
</script>
<script>
$(document).ready(function () {
employeeList();
});
function employeeList() {
$.ajax({
type: 'get',
url: "employee-list.php",
success: function (data) {
var response = JSON.parse(data);
console.log(response);
var tr = '';
for (var i = 0; i < response.length; i++) {
var id = response[i].id;
var name = response[i].name;
var email = response[i].email;
var phone = response[i].phone;
var address = response[i].address;
tr += '<tr>';
tr += '<td>' + id + '</td>';
tr += '<td>' + name + '</td>';
tr += '<td>' + email + '</td>';
tr += '<td>' + phone + '</td>';
tr += '<td>' + address + '</td>';
tr += '<td><div class="d-flex">';
tr +=
'<a href="#viewEmployeeModal" class="m-1 view" data-toggle="modal" onclick=viewEmployee("' +
id + '")><i class="fa" data-toggle="tooltip" title="view"></i></a>';
tr +=
'<a href="#editEmployeeModal" class="m-1 edit" data-toggle="modal" onclick=viewEmployee("' +
id +
'")><i class="material-icons" data-toggle="tooltip" title="Edit"></i></a>';
tr +=
'<a href="#deleteEmployeeModal" class="m-1 delete" data-toggle="modal" onclick=$("#delete_id").val("' +
id +
'")><i class="material-icons" data-toggle="tooltip" title="Delete"></i></a>';
tr += '</div></td>';
tr += '</tr>';
}
$('.loading').hide();
$('#employee_data').html(tr);
}
});
}
function addEmployee() {
var name = $('.add_epmployee #name_input').val();
var email = $('.add_epmployee #email_input').val();
var phone = $('.add_epmployee #phone_input').val();
var address = $('.add_epmployee #address_input').val();
$.ajax({
type: 'post',
data: {
name: name,
email: email,
phone: phone,
address: address,
},
url: "employee-add.php",
success: function (data) {
var response = JSON.parse(data);
$('#addEmployeeModal').modal('hide');
employeeList();
alert(response.message);
}
})
}
function editEmployee() {
var name = $('.edit_employee #name_input').val();
var email = $('.edit_employee #email_input').val();
var phone = $('.edit_employee #phone_input').val();
var address = $('.edit_employee #address_input').val();
var employee_id = $('.edit_employee #employee_id').val();
$.ajax({
type: 'post',
data: {
name: name,
email: email,
phone: phone,
address: address,
employee_id: employee_id,
},
url: "employee-edit.php",
success: function (data) {
var response = JSON.parse(data);
$('#editEmployeeModal').modal('hide');
employeeList();
alert(response.message);
}
})
}
function viewEmployee(id = 2) {
$.ajax({
type: 'get',
data: {
id: id,
},
url: "employee-view.php",
success: function (data) {
var response = JSON.parse(data);
$('.edit_employee #name_input').val(response.name);
$('.edit_employee #email_input').val(response.email);
$('.edit_employee #phone_input').val(response.phone);
$('.edit_employee #address_input').val(response.address);
$('.edit_employee #employee_id').val(response.id);
$('.view_employee #name_input').val(response.name);
$('.view_employee #email_input').val(response.email);
$('.view_employee #phone_input').val(response.phone);
$('.view_employee #address_input').val(response.address);
}
})
}
function deleteEmployee() {
var id = $('#delete_id').val();
$('#deleteEmployeeModal').modal('hide');
$.ajax({
type: 'get',
data: {
id: id,
},
url: "employee-delete.php",
success: function (data) {
var response = JSON.parse(data);
employeeList();
alert(response.message);
}
})
}
</script>
</body>
</html>
Step 5 – Crate CRUD API using PHP
In this step we will create api for CRUD operation.
Create following files in your root folder :-
- employee-add.php
- employee-list.php
- employee-view.php
- employee-edit.php
- employee-delete.php
1) employee-add.php
In this file, we will perform create operation.
This file are used for employee create API.
employee-add.php
<?php
include ("connection.php" );
$name = $_POST['name' ];
$email = $_POST['email' ];
$address = $_POST['address' ];
$phone = $_POST['phone' ];
$sql= "INSERT INTO `employees`(`name` , `email` , `address` , `phone`)
VALUE (' {$name} ' , ' {$email } ' , ' {$address } ' , ' {$phone } ')" ;
if(mysqli_query($conn , $sql)){
$response = [
'status'=>'ok',
'success'=>true,
'message'=>'Record created succesfully!'
];
print_r(json_encode($response));
}else{
$response = [
'status'=>'ok',
'success'=>false,
'message'=>'Record created failed!'
];
print_r(json_encode($response));
}
?>
2) employee-list.php
In this file, we will fetch all employee data from database.
This file are used for employee list API.
employee-list.php
<?php
include ("connection.php" );
$sql= "SELECT * FROM `employees`" ;
$result = mysqli_query($conn , $sql);
$data = [];
while ($fetch=mysqli_fetch_assoc($result)){
$data[] = $fetch;
}
print_r(json_encode($data));
?>
3) employee-view.php
In this file, we will fetch a single employee data.
This file are used for employee view API.
employee-view.php
<?php
include ("connection.php" );
$id= $_GET['id' ];
$sql= "SELECT * FROM `employees` WHERE `id` = $id";
$result= mysqli_query($conn , $sql);
$fetch= mysqli_fetch_assoc($result) ;
print_r(json_encode($fetch));
?>
4) employee-edit.php
In this file, we will edit a single employee data.
This file are used for employee edit API.
employee-edit.php
<?php
include ("connection.php" );
$name= $_POST['name' ];
$email= $_POST['email' ];
$address= $_POST['address' ];
$phone= $_POST['phone' ];
$id= $_POST['employee_id' ];
$sql= "UPDATE `employees` SET `name` = '". $name."' , `email` = '". $email."' ,
`address` = '".$address ."' , `phone` ='".$phone ."' WHERE `id` = $id " ;
if(mysqli_query($conn , $sql)){
$response = [
'status'=>'ok',
'success'=>true,
'message'=>'Record updated succesfully!'
];
print_r(json_encode($response));
}else{
$response = [
'status'=>'ok',
'success'=>false,
'message'=>'Record updated failed!'
];
print_r(json_encode($response));
}
?>
5) employee-delete.php
In this file, we will delete a single employee data.
This file are used for employee delete API.
employee-delete.php
<?php
include ("connection.php" );
$id =$_GET['id' ];
$sql= "DELETE FROM `employees` WHERE `id` = $id " ;
if(mysqli_query($conn , $sql)){
$response = [
'status'=>'ok',
'success'=>true,
'message'=>'Record deleted succesfully!'
];
print_r(json_encode($response));
}else{
$response = [
'status'=>'ok',
'success'=>false,
'message'=>'Record deleted failed!'
];
print_r(json_encode($response));
}
?>
Step 6 – Run your project
In last step, go to browser and run employee.php file.
Results –
Add employee
View employee
Edit Employee
Delete Employee