Adding pagination on the page becomes easier with the DataTables plugin. It comes with all the basic features that require in pagination like – searching, sorting, rows per page, etc.
You can add action buttons to the list. For these need to update the AJAX script.
In this tutorial, I show how you can add a toggle button to change user status in DataTable AJAX pagination with PHP.
Contents
1. Table structure
Create employees table and I added some records.
CREATE TABLE `employees` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `emp_name` varchar(80) NOT NULL, `gender` varchar(10) NOT NULL, `city` varchar(80) NOT NULL, `email` varchar(80) NOT NULL, `status` smallint(2) NOT NULL );
Change the value of the status field from the toggle button in DataTable.
2. Database 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.
- Include jQuery and DataTables library.
- 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'> <!-- jQuery Library --> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script> <!-- Datatable JS --> <script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
4. HTML
For toggle button add some CSS.
Create a <table id="empTable" >. Initialize DataTables on this <table > using jQuery.
Completed Code
<style type="text/css">
/* Toggle button */
#empTable .switch {
position: relative;
display: inline-block;
width: 45px;
height: 22px;
}
#empTable .switch input {
opacity: 0;
width: 0;
height: 0;
}
#empTable .slider {
position: absolute;
cursor: pointer;
top: 0;
left: 0;
right: 0;
bottom: 0;
background-color: #ccc;
-webkit-transition: .4s;
transition: .4s;
}
#empTable .slider:before {
position: absolute;
content: "";
height: 17px;
width: 17px;
left: 1px;
bottom: 3px;
background-color: white;
-webkit-transition: .4s;
transition: .4s;
}
#empTable input:checked + .slider {
background-color: #007bff;
}
#empTable input:focus + .slider {
box-shadow: 0 0 1px #007bff;
}
#empTable input:checked + .slider:before {
-webkit-transform: translateX(26px);
-ms-transform: translateX(26px);
transform: translateX(26px);
}
#empTable .slider.round {
border-radius: 34px;
}
#empTable .slider.round:before {
border-radius: 50%;
}
/*********/
</style>
<div >
<!-- Table -->
<table id='empTable' class='display dataTable'>
<thead>
<tr>
<th>Employee name</th>
<th>Email</th>
<th>Gender</th>
<th>City</th>
<th>Status</th>
</tr>
</thead>
</table>
</div>
5. jQuery
Initialize DataTable
Initialize DataTable on #empTable. Set AJAX URL to ajaxfile.php. Pass paginationData with data.request. Specify key names that need to be read in columns option.
Change status
Define change event on changeuserstatus class. Read update id from data-id attribute. Check if the checkbox is checked or not. If checked then store 1 in status variable. Send AJAX request to ajaxfile.php where pass {request: 'changeStatus',status: status,id: empid} as data.
Completed Code
$(document).ready(function(){
// DataTable
var dataTable = $('#empTable').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
'searching': true, // Set false to Remove default Search Control
'ajax': {
'url':'ajaxfile.php',
'data': function(data){
// Append to data
data.request = "paginationData";
}
},
'columns': [
{ data: 'emp_name' },
{ data: 'email' },
{ data: 'gender' },
{ data: 'city' },
{ data: 'status' },
]
});
// Change user status
$('#empTable').on('click','.changeuserstatus',function(e){
if (confirm("Are you sure?") == true) {
var empid = $(this).attr('data-id');
var status = 0;
if($(this).is(":checked")){
status = 1;
}
$.ajax({
url: 'ajaxfile.php',
type: 'post',
data: {request: 'changeStatus',status: status,id: empid},
success: function(response){
console.log(response);
}
});
}else{
e.preventDefault();
}
});
});
6. PHP
Create ajaxfile.php file for AJAX request handling.
From this file handle 2 requests –
- Return DataTables data.
- Update user status
Return DataTables data ($request == ‘paginationData’) –
Read DataTables POST data and assign them to the variables. In the $searchQuery variable store search query on fields if $searchValue is not empty.
Count the total number of records with and without search filter from the employees table and assign count value to the variables.
Fetch records from the employees table with search filter. Loop on the fetched records.
In the $status_html store HTML layout for the toggle.
Initialize $data Array with fetched data. Here, in the status pass $status_html.
In the $response Array store all the required data and returns it in JSON format.
Update user status ($request == ‘changeStatus’) –
Read POST update id and status. Execute update query on employees table to update status field value with $status where id = $empid.
Completed Code
<?php
include 'config.php';
$request = "";
if(isset($_POST['request'])){
$request = $_POST['request'];
}
// Datatable Data
if($request == 'paginationData'){
## 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_real_escape_string($con,$_POST['search']['value']); // Search value
## Search
$searchQuery = " ";
if($searchValue != ''){
$searchQuery = " AND (emp_name LIKE '%".$searchValue."%' OR email LIKE '%".$searchValue."%' OR city LIKE'%".$searchValue."%' ) ";
}
## Total number of records without filtering
$records = mysqli_query($con,"SELECT COUNT(*) as allcount FROM employees");
$row = mysqli_fetch_assoc($records);
$totalRecords = $row['allcount'];
## Total number of records with filtering
$records = mysqli_query($con,"SELECT COUNT(*) as allcount FROM employees WHERE 1 ".$searchQuery);
$row = mysqli_fetch_assoc($records);
$totalRecordwithFilter = $row['allcount'];
## Fetch records
$sql = "SELECT * FROM employees WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT ".$row.",".$rowperpage;
$empRecords = mysqli_query($con, $sql);
$data = array();
while ($row = mysqli_fetch_assoc($empRecords)) {
$empid = $row['id'];
// Status
$checked = "";
if($row['status'] == 1){
$checked = "checked";
}
$status_html = '<label class="switch">
<input type="checkbox" '.$checked.' class="changeuserstatus" data-id="'.$empid.'" >
<span class="slider round"></span>
</label>';
$data[] = array(
"emp_name" => $row['emp_name'],
"email" => $row['email'],
"gender" => $row['gender'],
"city" => $row['city'],
"status "=> $status_html,
);
}
## Response
$response = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);
echo json_encode($response);
die;
}
// Change user status
if($request == 'changeStatus'){
$empid = $_POST['id'];
$status = $_POST['status'];
mysqli_query($con,"UPDATE employees SET status=".$status." WHERE id=".$empid);
echo 1;
die;
}
7. Demo
8. Conclusion
With this, you can change the status directly from the list instead of going to the edit form.
Following the same steps you can add more buttons for different operations.
You can also view this tutorial if you want to know how to add edit delete action button in the DataTables AJAX pagination.
If you found this tutorial helpful then don't forget to share.