With the use of the DataTables jQuery plugin, you can list your records in pagination format.
You can add HTML elements like – buttons, link, image, etc with a record.
For this need to pass element in HTML string format from the PHP script.
NOTE – I am using the Bootstrap modal to update a record.
Contents
1. Table structure
Create users table and I added some records.
CREATE TABLE `users` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` varchar(80) NOT NULL, `gender` varchar(10) NOT NULL, `city` varchar(80) NOT NULL, `email` varchar(80) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2. Configuration
Create a config.php for the database connection.
Completed Code
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}3. Download & Include
- Download Datatables from here and Bootstrap from here.
- Include
datatables.min.css,bootstrap.min.css, jQuery library,bootstrap.min.js, anddatatables.min.jsin<head>section. - You can also use CDN.
<!-- Datatable CSS --> <link href='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'> <!-- Bootstrap CSS --> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" > <!-- jQuery Library --> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script> <!-- Bootstrap CSS --> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" ></script> <!-- Datatable JS --> <script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
4. HTML
Create <div id="updateModal" > modal for update user details. Created a hidden field to store edit user id and <button id="btn_save"> to update user on click.
Display users list in <table id='userTable' >. On the last column, display the edit and delete button.
Completed Code
<!doctype html>
<html>
<head>
<title>Edit delete DataTables record with AJAX and PHP</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<!-- Datatable CSS -->
<link href='DataTables/datatables.min.css' rel='stylesheet' type='text/css'>
<!-- Bootstrap CSS -->
<link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css">
<!-- jQuery Library -->
<script src="jquery-3.5.1.min.js"></script>
<!-- Bootstrap JS -->
<script type="text/javascript" src="bootstrap/js/bootstrap.min.js"></script>
<!-- Datatable JS -->
<script src="DataTables/datatables.min.js"></script>
</head>
<body >
<div class='container'>
<!-- Modal -->
<div id="updateModal" class="modal fade" role="dialog">
<div class="modal-dialog">
<!-- Modal content-->
<div class="modal-content">
<div class="modal-header">
<h4 class="modal-title">Update</h4>
<button type="button" class="close" data-dismiss="modal">×</button>
</div>
<div class="modal-body">
<div class="form-group">
<label for="name" >Name</label>
<input type="text" class="form-control" id="name" placeholder="Enter name" required>
</div>
<div class="form-group">
<label for="email" >Email</label>
<input type="email" class="form-control" id="email" placeholder="Enter email">
</div>
<div class="form-group">
<label for="gender" >Gender</label>
<select id='gender' class="form-control">
<option value='male'>Male</option>
<option value='female'>Female</option>
</select>
</div>
<div class="form-group">
<label for="city" >City</label>
<input type="text" class="form-control" id="city" placeholder="Enter city">
</div>
</div>
<div class="modal-footer">
<input type="hidden" id="txt_userid" value="0">
<button type="button" class="btn btn-success btn-sm" id="btn_save">Save</button>
<button type="button" class="btn btn-default btn-sm" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
<!-- Table -->
<table id='userTable' class='display dataTable' width='100%'>
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Gender</th>
<th>City</th>
<th>Action</th>
</tr>
</thead>
</table>
</div>
</body>
</html>5. PHP
Create an ajaxfile.php file for handling AJAX requests.
Handle 4 requests –
If $request == 1 – Return DataTable response.
Read DataTable POST values and assign them in variables.
If $search is not empty then prepare search query and assign in $searchQuery variable.
Count total records with and without search filter and assign in variables. Fetch records from users table where pass $searchQuery in WHERE clause and specify ORDER BY and LIMIT.
Loop on the fetched records. Display update and delete buttons in DataTable using HTML string.
- Create an update button and assign in
$updateButton. In the button add'updateUser'class and usedata-idattribute to store user id. Adddata-toggleanddata-targetattribute to open#updateModalmodal. - Similarly, create delete button and assign in
$deleteButton. In the button add'deleteUser'class and usedata-idattribute to store user id.
- Create an update button and assign in
Concat $updateButton and $deleteButton and assign in $action variable.
Initialize $response Array with required values. Here, pass the $action in 'action' key.
Return $response Array in JSON format.
If $request == 2 – Return user details by id.
Assign $_POST['id'] to $id variable. Fetch a record from users table by id. If the number of fetched record is greater than 0 then read field values and initialize $response Array. Return an Array in JSON format that contains array("status" => 1,"data" => $response).
If the number of fetched record is 0 then Return an Array in JSON format that contains array("status" => 1).
If $request == 3 – Update field values by id.
Assign $_POST['id'] to $id variable. If $id is greater than 0 then read POST value and assign in a variable.
Check if $id is exists in the users table. If exists then check if variables are not empty then update a record by id and return Array in JSON format. Array contains array("status" => 1,"message" => "Record updated."). If variables are empty then return array("status" => 0,"message" => "Please fill all fields.") in JSON format.
If $id is not exists then return array("status" => 0,"message" => "Invalid ID.") in JSON format.
If $request == 4 – Delete a record by id.
Assign $_POST['id'] to $id variable. If $id is greater than 0 then read POST value and assign in a variable.
Check if $id is exists in the users table. If exist then delete a record from users table by id and return 1 otherwise return 0.
Completed Code
<?php
include 'config.php';
$request = 1;
if(isset($_POST['request'])){
$request = $_POST['request'];
}
// DataTable data
if($request == 1){
## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = mysqli_escape_string($con,$_POST['search']['value']); // Search value
## Search
$searchQuery = " ";
if($searchValue != ''){
$searchQuery = " and (name like '%".$searchValue."%' or
email like '%".$searchValue."%' or
city like'%".$searchValue."%' ) ";
}
## Total number of records without filtering
$sel = mysqli_query($con,"select count(*) as allcount from users");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];
## Total number of records with filtering
$sel = mysqli_query($con,"select count(*) as allcount from users WHERE 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];
## Fetch records
$empQuery = "select * from users WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$empRecords = mysqli_query($con, $empQuery);
$data = array();
while ($row = mysqli_fetch_assoc($empRecords)) {
// Update Button
$updateButton = "<button class='btn btn-sm btn-info updateUser' data-id='".$row['id']."' data-toggle='modal' data-target='#updateModal' >Update</button>";
// Delete Button
$deleteButton = "<button class='btn btn-sm btn-danger deleteUser' data-id='".$row['id']."'>Delete</button>";
$action = $updateButton." ".$deleteButton;
$data[] = array(
"name" => $row['name'],
"email" => $row['email'],
"gender" => $row['gender'],
"city" => $row['city'],
"action" => $action
);
}
## Response
$response = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);
echo json_encode($response);
exit;
}
// Fetch user details
if($request == 2){
$id = 0;
if(isset($_POST['id'])){
$id = mysqli_escape_string($con,$_POST['id']);
}
$record = mysqli_query($con,"SELECT * FROM users WHERE id=".$id);
$response = array();
if(mysqli_num_rows($record) > 0){
$row = mysqli_fetch_assoc($record);
$response = array(
"name" => $row['name'],
"email" => $row['email'],
"gender" => $row['gender'],
"city" => $row['city'],
);
echo json_encode( array("status" => 1,"data" => $response) );
exit;
}else{
echo json_encode( array("status" => 0) );
exit;
}
}
// Update user
if($request == 3){
$id = 0;
if(isset($_POST['id'])){
$id = mysqli_escape_string($con,$_POST['id']);
}
// Check id
$record = mysqli_query($con,"SELECT id FROM users WHERE id=".$id);
if(mysqli_num_rows($record) > 0){
$name = mysqli_escape_string($con,trim($_POST['name']));
$email = mysqli_escape_string($con,trim($_POST['email']));
$gender = mysqli_escape_string($con,trim($_POST['gender']));
$city = mysqli_escape_string($con,trim($_POST['city']));
if( $name != '' && $email != '' && $gender != '' && $city != '' ){
mysqli_query($con,"UPDATE users SET name='".$name."',email='".$email."',gender='".$gender."',city='".$city."' WHERE id=".$id);
echo json_encode( array("status" => 1,"message" => "Record updated.") );
exit;
}else{
echo json_encode( array("status" => 0,"message" => "Please fill all fields.") );
exit;
}
}else{
echo json_encode( array("status" => 0,"message" => "Invalid ID.") );
exit;
}
}
// Delete User
if($request == 4){
$id = 0;
if(isset($_POST['id'])){
$id = mysqli_escape_string($con,$_POST['id']);
}
// Check id
$record = mysqli_query($con,"SELECT id FROM users WHERE id=".$id);
if(mysqli_num_rows($record) > 0){
mysqli_query($con,"DELETE FROM users WHERE id=".$id);
echo 1;
exit;
}else{
echo 0;
exit;
}
}6. jQuery
Initialize DataTables
Initialize DataTables on #userTable and assign in userDataTable. Set processing: true, serverSide: true, serverMethod: 'post'. Specify AJAX url with 'ajax' option.
With 'columns' option specifies key names that need to read from AJAX response.
Update User
Define click event on .updateUser class. Read user id from data-id attribute and assign in id variable. Update value of #txt_userid.
Send AJAX POST request to 'ajaxfile.php' file. Pass {request: 2, id: id}, set dataType: 'json'.
On successful callback check if response.status == 1 or not. If equals then read values from response.data and update the value of Bootstrap modal input elements.
Define click event on #btn_save. Read edit id from #txt_userid and assign in id variable. Similarly, read name, email, gender, and city from input elements and assign in variables.
If values are not empty then send AJAX POST request to 'ajaxfile.php' file. Pass {request: 3, id: id,name: name, email: email, gender: gender, city: city} as data. Set dataType: 'json'.
On successful callback check if response.status == 1 or not. If equals then alert(response.message) and reset input elements value.
Reload Datatable by calling userDataTable.ajax.reload() and toggle the #updateModal modal.
Delete User
Define click event on .deleteUser class. Read delete user id from data-id attribute. Display confirm alert message. If deleteConfirm == true then send AJAX POST request to 'ajaxfile.php' file. Pass {request: 4, id: id} as data.
On successful callback alert("Record deleted.") if response == 1 and reload Datatable by calling userDataTable.ajax.reload().
Completed Code
$(document).ready(function(){
// DataTable
var userDataTable = $('#userTable').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
'ajax': {
'url':'ajaxfile.php'
},
'columns': [
{ data: 'name' },
{ data: 'email' },
{ data: 'gender' },
{ data: 'city' },
{ data: 'action' },
]
});
// Update record
$('#userTable').on('click','.updateUser',function(){
var id = $(this).data('id');
$('#txt_userid').val(id);
// AJAX request
$.ajax({
url: 'ajaxfile.php',
type: 'post',
data: {request: 2, id: id},
dataType: 'json',
success: function(response){
if(response.status == 1){
$('#name').val(response.data.name);
$('#email').val(response.data.email);
$('#gender').val(response.data.gender);
$('#city').val(response.data.city);
userDataTable.ajax.reload();
}else{
alert("Invalid ID.");
}
}
});
});
// Save user
$('#btn_save').click(function(){
var id = $('#txt_userid').val();
var name = $('#name').val().trim();
var email = $('#email').val().trim();
var gender = $('#gender').val().trim();
var city = $('#city').val().trim();
if(name !='' && email != '' && city != ''){
// AJAX request
$.ajax({
url: 'ajaxfile.php',
type: 'post',
data: {request: 3, id: id,name: name, email: email, gender: gender, city: city},
dataType: 'json',
success: function(response){
if(response.status == 1){
alert(response.message);
// Empty and reset the values
$('#name','#email','#city').val('');
$('#gender').val('male');
$('#txt_userid').val(0);
// Reload DataTable
userDataTable.ajax.reload();
// Close modal
$('#updateModal').modal('toggle');
}else{
alert(response.message);
}
}
});
}else{
alert('Please fill all fields.');
}
});
// Delete record
$('#userTable').on('click','.deleteUser',function(){
var id = $(this).data('id');
var deleteConfirm = confirm("Are you sure?");
if (deleteConfirm == true) {
// AJAX request
$.ajax({
url: 'ajaxfile.php',
type: 'post',
data: {request: 4, id: id},
success: function(response){
if(response == 1){
alert("Record deleted.");
// Reload DataTable
userDataTable.ajax.reload();
}else{
alert("Invalid ID.");
}
}
});
}
});
});
