JavaScript
CSS
jQuery
MySQL
PHP
Laravel
Python
HTML Course Code
White Board
CSS Minifier
Gradient Generator
PDF to Text Converter
Text Converter
Age Calculator
Pincode Details Finder
PX ⇄ REM Converter
PX ⇄ EM Converter
WEBP Converter
Coding Ground
Resume Builder
Sumit Sarkar
06 Nov 2024
PHPIn this tutorial, we will learn how to implement CRUD operations using PHP, AJAX, and jQuery without page reload.
Here wee will see step by step process of database setup, PHP script creation, and integrating dynamic interactions with AJAX for a seamless user experience.
We will use an Employee Management app as an example. This app performs all CRUD operations in PHP without reloading the page, using AJAX, jQuery, and Bootstrap modals.
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 an 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
To create a connection.php file for connecting to your database (php_ajax), use the following PHP 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
- To create an
employee.phpfile for performing CRUD operations using AJAX, you will: - Set up the structure with HTML for the employee form and data display.
- Use AJAX to handle create, read, update, and delete actions without reloading the page.
- Connect to the database via connection.php to perform the operations.
<!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
To create the necessary API files for CRUD operations in your employee.php file, follow these steps:
Create following files in your root folder :-
- employee-add.php: This file will handle adding new employee records to the database using POST requests.
- employee-list.php: This file will fetch and display all employees from the database.
- employee-view.php: This file will retrieve and show detailed information for a specific employee.
- employee-edit.php: This file will allow for editing employee details.
- employee-delete.php: This file will handle deleting an employee record.
1) employee-add.php
This file will handle adding new employee records to the database using POST requests.
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
This file will fetch and display all employees from the database.
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
This file will retrieve and show detailed information for a specific employee.
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
This file will allow for editing employee details.
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
This file will handle deleting an employee record.
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
Now go to browser and run employee.php file.
Output
Add employee
View employee
Edit Employee
Delete Employee
Leave a comment