DataTables is a jQuery plugin that makes it easier to add pagination on the webpage.
Just need to add records list then it will auto-adjust data and create pagination with search and sort feature.
There are options available to implement AJAX pagination.
In this tutorial, I show how you can implement AJAX pagination in DataTables with PHP.
Contents
1. Table structure
Create employee table.
CREATE TABLE `employee` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `emp_name` varchar(80) NOT NULL, `salary` varchar(20) 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.
- Include
datatables.min.cssanddatatables.min.jsin<head>section and also include the jQuery 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.5.1/jquery.min.js"></script> <!-- Datatable JS --> <script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
4. HTML
Create a <table id='empTable' class='display dataTable'> and add column name in <thead>.
Completed Code
<!-- Datatable CSS -->
<link href='https://cdn.datatables.net/1.11.5/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="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
<!-- Table -->
<table id='empTable' class='display dataTable'>
<thead>
<tr>
<th>Employee name</th>
<th>Email</th>
<th>Gender</th>
<th>Salary</th>
<th>City</th>
</tr>
</thead>
</table>
5. Script
Initialize DataTable on <table id='empTable'>.
Within the method pass options to enable server-side processing and send AJAX post request to ajaxfile.php.
In the columns option pass field names that get read when AJAX successfully callback.
Completed Code
$(document).ready(function(){
$('#empTable').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
'ajax': {
'url':'ajaxfile.php'
},
'columns': [
{ data: 'emp_name' },
{ data: 'email' },
{ data: 'gender' },
{ data: 'salary' },
{ data: 'city' },
]
});
});
6. PHP
Create a new ajaxfile.php.
Read the $_POST values and store in variables that are passed by DataTable during AJAX request – draw, start, length, order,columnIndex, column name, order, and search.
Prepare search query if $searchValue is not empty.
Count the total number of records in the employee table.
Count the total number of records with the Search filter from the employee table.
Both the count returns same value on the first time. The difference is when the search value from DataTable then the record count with filter will use to show – number of filtered record from total records in DataTable.
Fetch records from employee table.
Loop on the records and initialize $data Array with an associative array. In the Array, the key will the same as defined in columns option during DataTable initialization.
Prepare $response An array that has a draw, iTotalRecords, iTotalDisplayRecords, and aaData keys.
Return $response JSON format.
Completed Code
<?php
## Database configuration
include 'config.php';
## 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
$sel = mysqli_query($con,"select count(*) as allcount from employee");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];
## Total number of record with filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee WHERE 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];
## Fetch records
$empQuery = "select * from employee WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$empRecords = mysqli_query($con, $empQuery);
$data = array();
while ($row = mysqli_fetch_assoc($empRecords)) {
$data[] = array(
"emp_name"=>$row['emp_name'],
"email"=>$row['email'],
"gender"=>$row['gender'],
"salary"=>$row['salary'],
"city"=>$row['city']
);
}
## Response
$response = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);
echo json_encode($response);
7. Demo
8. Conclusion
Make sure that field names should be the same in AJAX response data as defined in columns data during DataTable initialization otherwise field value not be read.
Remove serverMethod option if you want to send GET type AJAX request then you also need to update the AJAX file.
You can also view the PDO version here.
If you found this tutorial helpful then don't forget to share.NOTE – If data is not loading and Invalid JSON response message showing then use the browser network tab to debug. Check SQL queries again and return response. If still issue is not resolved then you either comment or mail me your code with SQL file at makitweb@gmail.com so I can check it.
