PHP Mysqli Datatables Server Side CRUD Ajax (Create, Read, Update and Delete) Bootstrap 5.1 Version
https://getbootstrap.com/docs/5.1/getting-started/introduction/
DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, built upon the foundations of progressive enhancement, that adds all of these advanced features to any HTML table.
https://datatables.net/
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`username` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL,
`mobile` varchar(20) NOT NULL,
`city` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `users` (`id`, `username`, `email`, `mobile`, `city`) VALUES
(7, 'Airi Satou', 'AiriSatou@gmail.com', '8887919632', 'Tokyo'),
(8, 'Angelica Ramos', 'AngelicaRamos@gmail.com', '8887919632', 'London'),
(9, 'Ashton Cox', 'AshtonCox@gmail.com', '8887919632', 'San Francisco'),
(10, 'Bradley Greer', 'BradleyGreer@gmail.com', '434334', 'London'),
(11, 'Brenden Wagner', 'BrendenWagner@gmail.com', '434334', 'San Francisco'),
(12, 'Brielle Williamson', 'BrielleWilliamson@gmail.com', '434334', 'New York'),
(13, 'Bruno Nash', 'BrunoNash@gmail.com', '9988999999', 'London'),
(15, 'Caesar Vance', 'CaesarVance@gmail.com', '8127956219', 'New York'),
(16, 'Cara Stevens', 'CaraStevens@gmail.com', '8127956219', 'New York'),
(18, 'Cedric Kelly', 'CedricKelly@gmail.com', '8127956219', 'Edinburgh'),
(19, 'Charde Marshall', 'ChardeMarshall@Gmail.com', '5464654654', 'San Francisco'),
(20, 'Colleen Hurst', 'ColleenHurst@Gmail.com', '5465465465', 'San Francisco'),
(21, 'Garrett Winters', 'GarrettWinters@gmail.com', '346546465', 'Olongapo City');
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;
index.php
//index.php
<?php include('connection.php');?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-+0n0xVW2eSR5OomGNYDnhzAbDsOXxcvSN1TPprVMTNDbiYZCxYbOOl7+AMvyTG2x" crossorigin="anonymous">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.4/css/jquery.dataTables.min.css"/>
<title>PHP Mysqli Datatables Server Side CRUD Ajax (Create, Read, Update and Delete)</title>
<style type="text/css">
.btnAdd {
text-align: right;
width: 83%;
margin-bottom: 20px;
}
</style>
</head>
<body>
<div class="container-fluid">
<h2 class="text-center">PHP Mysqli Datatables Server Side CRUD Ajax (Create, Read, Update and Delete)</h2>
<div class="row">
<div class="container">
<div class="btnAdd">
<a href="#!" data-id="" data-bs-toggle="modal" data-bs-target="#addUserModal" class="btn btn-success btn-sm" >Add User</a>
</div>
<div class="row">
<div class="col-md-2"></div>
<div class="col-md-8">
<table id="example" class="table">
<thead>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Mobile</th>
<th>City</th>
<th>Options</th>
</thead>
<tbody>
</tbody>
</table>
</div>
<div class="col-md-2"></div>
</div>
</div>
</div>
<?php include('modal.php'); ?>
</div>
<script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-gtEjrD/SeCtmISkJkNUaaKMoLD0//ElJ19smozuHV6z3Iehds+3Ulb9Bn9Plx0x4" crossorigin="anonymous"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.11.4/js/jquery.dataTables.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$('#example').DataTable({
"fnCreatedRow": function( nRow, aData, iDataIndex ) {
$(nRow).attr('id', aData[0]);
},
'serverSide':'true',
'processing':'true',
'paging':'true',
'order':[],
'ajax': {
'url':'fetch_data.php',
'type':'post',
},
"columnDefs": [{
'target':[5],
'orderable' :false,
}]
});
} );
$(document).on('submit','#addUser',function(e){
e.preventDefault();
var city= $('#addCityField').val();
var username= $('#addUserField').val();
var mobile= $('#addMobileField').val();
var email= $('#addEmailField').val();
if(city != '' && username != '' && mobile != '' && email != '' )
{
$.ajax({
url:"add_user.php",
type:"post",
data:{city:city,username:username,mobile:mobile,email:email},
success:function(data)
{
var json = JSON.parse(data);
var status = json.status;
if(status=='true')
{
mytable =$('#example').DataTable();
mytable.draw();
$('#addUserModal').modal('hide');
}
else
{
alert('failed');
}
}
});
}
else {
alert('Fill all the required fields');
}
});
$(document).on('submit','#updateUser',function(e){
e.preventDefault();
var city= $('#cityField').val();
var username= $('#nameField').val();
var mobile= $('#mobileField').val();
var email= $('#emailField').val();
var trid= $('#trid').val();
var id= $('#id').val();
if(city != '' && username != '' && mobile != '' && email != '' )
{
$.ajax({
url:"update_user.php",
type:"post",
data:{city:city,username:username,mobile:mobile,email:email,id:id},
success:function(data)
{
var json = JSON.parse(data);
var status = json.status;
if(status=='true')
{
table =$('#example').DataTable();
var button = '<td><a href="javascript:void();" data-id="' +id + '" class="btn btn-info btn-sm editbtn">Edit</a> <a href="#!" data-id="' +id + '" class="btn btn-danger btn-sm deleteBtn">Delete</a></td>';
var row = table.row("[id='"+trid+"']");
row.row("[id='" + trid + "']").data([id,username,email,mobile,city,button]);
$('#exampleModal').modal('hide');
}
else
{
alert('failed');
}
}
});
}
else {
alert('Fill all the required fields');
}
});
$('#example').on('click','.editbtn ',function(event){
var table = $('#example').DataTable();
var trid = $(this).closest('tr').attr('id');
var id = $(this).data('id');
$('#exampleModal').modal('show');
$.ajax({
url:"get_single_data.php",
data:{id:id},
type:'post',
success:function(data)
{
var json = JSON.parse(data);
$('#nameField').val(json.username);
$('#emailField').val(json.email);
$('#mobileField').val(json.mobile);
$('#cityField').val(json.city);
$('#id').val(id);
$('#trid').val(trid);
}
})
});
$(document).on('click','.deleteBtn',function(event){
var table = $('#example').DataTable();
event.preventDefault();
var id = $(this).data('id');
if(confirm("Are you sure want to delete this User ? "))
{
$.ajax({
url:"delete_user.php",
data:{id:id},
type:"post",
success:function(data)
{
var json = JSON.parse(data);
status = json.status;
if(status=='success')
{
$("#"+id).closest('tr').remove();
}
else
{
alert('Failed');
return;
}
}
});
}
else
{
return null;
}
})
</script>
</body>
</html>
modal.php
//modal.php
<!-- Add user Modal -->
<div class="modal fade" id="addUserModal" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="exampleModalLabel">Add User</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<form id="addUser" action="">
<div class="mb-3 row">
<label for="addUserField" class="col-md-3 form-label">Name</label>
<div class="col-md-9">
<input type="text" class="form-control" id="addUserField" name="name" >
</div>
</div>
<div class="mb-3 row">
<label for="addEmailField" class="col-md-3 form-label">Email</label>
<div class="col-md-9">
<input type="email" class="form-control" id="addEmailField" name="email">
</div>
</div>
<div class="mb-3 row">
<label for="addMobileField" class="col-md-3 form-label">Mobile</label>
<div class="col-md-9">
<input type="text" class="form-control" id="addMobileField" name="mobile">
</div>
</div>
<div class="mb-3 row">
<label for="addCityField" class="col-md-3 form-label">City</label>
<div class="col-md-9">
<input type="text" class="form-control" id="addCityField" name="City">
</div>
</div>
<div class="text-center">
<button type="submit" class="btn btn-primary">Submit</button>
</div>
</form>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
<!-- Modal -->
<div class="modal fade" id="exampleModal" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="exampleModalLabel">Update User</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<form id="updateUser" >
<input type="hidden" name="id" id="id" value="">
<input type="hidden" name="trid" id="trid" value="">
<div class="mb-3 row">
<label for="nameField" class="col-md-3 form-label">Name</label>
<div class="col-md-9">
<input type="text" class="form-control" id="nameField" name="name" >
</div>
</div>
<div class="mb-3 row">
<label for="emailField" class="col-md-3 form-label">Email</label>
<div class="col-md-9">
<input type="email" class="form-control" id="emailField" name="email">
</div>
</div>
<div class="mb-3 row">
<label for="mobileField" class="col-md-3 form-label">Mobile</label>
<div class="col-md-9">
<input type="text" class="form-control" id="mobileField" name="mobile">
</div>
</div>
<div class="mb-3 row">
<label for="cityField" class="col-md-3 form-label">City</label>
<div class="col-md-9">
<input type="text" class="form-control" id="cityField" name="City">
</div>
</div>
<div class="text-center">
<button type="submit" class="btn btn-primary">Submit</button>
</div>
</form>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
connection.php
//connection.php
<?php
$con = mysqli_connect('localhost','root','','testingdb');
if(mysqli_connect_errno())
{
echo 'Database Connection Error';
}
fetch_data.php
//fetch_data.php
<?php include('connection.php');
$output= array();
$sql = "SELECT * FROM users ";
$totalQuery = mysqli_query($con,$sql);
$total_all_rows = mysqli_num_rows($totalQuery);
if(isset($_POST['search']['value']))
{
$search_value = $_POST['search']['value'];
$sql .= " WHERE username like '%".$search_value."%'";
$sql .= " OR email like '%".$search_value."%'";
$sql .= " OR mobile like '%".$search_value."%'";
$sql .= " OR city like '%".$search_value."%'";
}
if(isset($_POST['order']))
{
$column_name = $_POST['order'][0]['column'];
$order = $_POST['order'][0]['dir'];
$sql .= " ORDER BY ".$column_name." ".$order."";
}
else
{
$sql .= " ORDER BY id desc";
}
if($_POST['length'] != -1)
{
$start = $_POST['start'];
$length = $_POST['length'];
$sql .= " LIMIT ".$start.", ".$length;
}
$query = mysqli_query($con,$sql);
$count_rows = mysqli_num_rows($query);
$data = array();
while($row = mysqli_fetch_assoc($query))
{
$sub_array = array();
$sub_array[] = $row['id'];
$sub_array[] = $row['username'];
$sub_array[] = $row['email'];
$sub_array[] = $row['mobile'];
$sub_array[] = $row['city'];
$sub_array[] = '<a href="javascript:void();" data-id="'.$row['id'].'" class="btn btn-info btn-sm editbtn" >Edit</a> <a href="javascript:void();" data-id="'.$row['id'].'" class="btn btn-danger btn-sm deleteBtn" >Delete</a>';
$data[] = $sub_array;
}
$output = array(
'draw'=> intval($_POST['draw']),
'recordsTotal' =>$count_rows ,
'recordsFiltered'=> $total_all_rows,
'data'=>$data,
);
echo json_encode($output);
add_user.php
//add_user.php
<?php
include('connection.php');
$username = $_POST['username'];
$email = $_POST['email'];
$mobile = $_POST['mobile'];
$city = $_POST['city'];
$sql = "INSERT INTO `users` (`username`,`email`,`mobile`,`city`) values ('$username', '$email', '$mobile', '$city' )";
$query= mysqli_query($con,$sql);
$lastId = mysqli_insert_id($con);
if($query ==true)
{
$data = array(
'status'=>'true',
);
echo json_encode($data);
}
else
{
$data = array(
'status'=>'false',
);
echo json_encode($data);
}
?>
get_single_data.php
//get_single_data.php
<?php include('connection.php');
$id = $_POST['id'];
$sql = "SELECT * FROM users WHERE id='$id' LIMIT 1";
$query = mysqli_query($con,$sql);
$row = mysqli_fetch_assoc($query);
echo json_encode($row);
?>
update_user.php
//update_user.php
<?php
include('connection.php');
$username = $_POST['username'];
$email = $_POST['email'];
$mobile = $_POST['mobile'];
$city = $_POST['city'];
$id = $_POST['id'];
$sql = "UPDATE `users` SET `username`='$username' , `email`= '$email', `mobile`='$mobile', `city`='$city' WHERE id='$id' ";
$query= mysqli_query($con,$sql);
$lastId = mysqli_insert_id($con);
if($query ==true)
{
$data = array(
'status'=>'true',
);
echo json_encode($data);
}
else
{
$data = array(
'status'=>'false',
);
echo json_encode($data);
}
?>
delete_user.php
//delete_user.php
<?php
include('connection.php');
$user_id = $_POST['id'];
$sql = "DELETE FROM users WHERE id='$user_id'";
$delQuery =mysqli_query($con,$sql);
if($delQuery==true)
{
$data = array(
'status'=>'success',
);
echo json_encode($data);
}
else
{
$data = array(
'status'=>'failed',
);
echo json_encode($data);
}
?>
VIDEO
