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.