Adding pagination on the page becomes easier with the DataTables plugin. It is equipped with essential features such as searching, sorting, and customizable rows per page.
Elevate user interaction by seamlessly integrating action buttons into 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.
Table of Content
- Setting Up the Employees Table for DataTable Integration
- Create a Database Connection file
- Effortless Datatables Integration with jQuery and CDN
- HTML – Styling Toggle Buttons and Setup DataTables layout
- jQuery – Initializing DataTable and Managing User Status
- PHP – Return DataTables data and User Status Handling
- Demo
- Conclusion
1. Setting Up the Employees Table for DataTable Integration
I am using employees table in the example, it has the following structure –
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. Create a Database Connection file
Create a config.php for the database connection.
<?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. Effortless Datatables Integration with jQuery and CDN
- Download Datatables library from the official website.
- jQuery and Datatables can be included either by downloading the files or utilizing the CDN (Content Delivery Network) for convenient online access.
<!-- Datatable CSS --> <link href='https://cdn.datatables.net/1.13.7/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'> <!-- jQuery Library --> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script> <!-- Datatable JS --> <script src="https://cdn.datatables.net/1.13.7/js/jquery.dataTables.min.js"></script>
4. HTML – Styling Toggle Buttons and Setup DataTables layout
Toggle Button Styling:
To create a visually appealing toggle button, a set of CSS styles is applied to the elements within the #empTable. This includes defining the dimensions, transitions, and colors for the toggle button and its components.
Table Initialization:
A table with the id empTable is created, and DataTables is initialized on this table using jQuery. The table structure includes columns for employee name, email, gender, city, and status.
<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 – Initializing DataTable and Managing User Status
Initializing DataTable:
Initialize DataTable on the #empTable element. Configure the DataTable with server-side processing by setting 'serverSide' and 'serverMethod' options to true and 'post', respectively. Enable or disable the search control with the 'searching' option. Set the AJAX URL to 'ajaxfile.php' and pass additional data – 'paginationData' using the 'data' option. Define the columns to be displayed and read from the server response.
Changing User Status:
Implement a change event on elements with the class 'changeuserstatus'. Extract the update id from the 'data-id' attribute. Check if the checkbox is checked or not; if checked, set the 'status' variable to 1. Initiate an AJAX request to 'ajaxfile.php' with the parameters {request: 'changeStatus', status: status, id: empid}. Confirm the action with the user before sending the request.
$(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 – Return DataTables data and User Status Handling
Creating ajaxfile.php:
Create a file named ajaxfile.php dedicated to handling AJAX requests. The file manages two types of requests:
- Returning DataTables data and
- Updating user status.
Returning DataTables Data ($request == ‘paginationData’):
Process DataTables data request by reading and assigning variables. Implement search functionality by storing search queries in the $searchQuery variable. Calculate the total number of records with and without search filters. Fetch records from the employees table, apply search filters, and format data, including a toggle button for user status. Return the formatted data in JSON format.
Updating User Status ($request == ‘changeStatus’):
Handle the request to change user status by reading the POST parameters for update id and status. Execute an update query on the employees table to modify the status field based on the provided parameters.
<?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
Experience the convenience of updating user statuses directly from the list, eliminating the need to navigate to the edit form.
By replicating these straightforward steps, you can effortlessly incorporate additional buttons for various operations, tailoring the DataTables AJAX pagination to your specific needs.
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.