DataTable AJAX Pagination with search, export buttons and sort in PHP
Mar 8, 2022
index.html
<!doctype html>
<html>
<head>
<title>DataTable AJAX Pagination with search and sort - PHP</title>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.2.3/css/bootstrap.min.css"
integrity="sha512-SbiR/eusphKoMVVXysTKG/7VseWii+Y3FdHrt0EpKgpToZeemhqHeZeLWLhJutz/2ut2Vw1uQEj2MbRF+TVBUA=="
crossorigin="anonymous" referrerpolicy="no-referrer" />
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.2/css/dataTables.bootstrap5.min.css" />
<link rel="stylesheet"
href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.2.0/css/bootstrap.min.css">
<link rel="stylesheet" href="style.css">
</head>
<body class="bg-light">
<div class="my-5 container-fluid">
<!-- Table -->
<h4 class="text-center">Live <span class="text-success">Auto-refresh</span> DataTable <span
class="text-success">AJAX</span> Pagination with search and sort - <span
class="text-warning">PHP</span></h4>
<table id='empTable' class="table table-striped display responsive nowrap" width="100%">
<thead>
<tr>
<th>S.No</th>
<th>Employee name</th>
<th>Designation</th>
<th>Department</th>
<th>Office</th>
<th>Age</th>
<th>Joining Date</th>
<th>Salary</th>
</tr>
</thead>
</table>
</div>
<script src="https://cdn.datatables.net/1.13.2/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.13.2/js/dataTables.bootstrap5.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
<script type="text/javascript"
src="https://cdn.datatables.net/v/bs5/dt-1.13.2/af-2.5.2/b-2.3.4/b-colvis-2.3.4/b-html5-2.3.4/b-print-2.3.4/cr-1.6.1/date-1.3.0/datatables.min.js"></script>
<script>
$(document).ready(function () {
$('#empTable').DataTable({
'processing': false,
'serverSide': true,
dom: 'lBfrtip',
buttons: [
'csv', 'excel', 'pdfHtml5', 'print'
],
'responsive': true,
'serverMethod': 'post',
'ajax': {
'url': 'ajaxfile.php'
},
'columns': [
{ data: 'EmpID' },
{ data: 'EmpName' },
{ data: 'Designation' },
{ data: 'Department' },
{ data: 'Office' },
{ data: 'Age' },
{ data: 'JoiningDate' },
{ data: 'Salary' },
]
});
setInterval(function () {
$('#empTable').DataTable().ajax.reload(null, false);
}, 1000);
});
</script>
</body>
</html>config.php
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "test"; /* Database name */
$con = mysqli_connect($host, $user, $password, $dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}ajaxfile.php
<?php
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 (EmpName like '%" . $searchValue . "%' or
Designation like '%" . $searchValue . "%' or
Department 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 records 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(
"EmpID" => $row['EmpID'],
"EmpName" => $row['EmpName'],
"Designation" => $row['Designation'],
"Department" => $row['Department'],
"Office" => $row['Office'],
"Age" => $row['Age'],
"JoiningDate" => date('d M Y h:i:s A'),
"Salary" => $row['Salary'],
);
}
## Response
$response = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);
echo json_encode($response);
test.sql
-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Feb 27, 2023 at 12:58 PM
-- Server version: 8.0.27
-- PHP Version: 8.1.0
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `test`
--
-- --------------------------------------------------------
--
-- Table structure for table `employee`
--
DROP TABLE IF EXISTS `employee`;
CREATE TABLE IF NOT EXISTS `employee` (
`EmpID` int NOT NULL AUTO_INCREMENT,
`EmpName` varchar(50) NOT NULL,
`Designation` varchar(50) DEFAULT NULL,
`Department` varchar(50) DEFAULT NULL,
`Office` varchar(50) NOT NULL,
`Age` int NOT NULL,
`JoiningDate` datetime DEFAULT NULL,
`Salary` float NOT NULL,
PRIMARY KEY (`EmpID`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `employee`
--
INSERT INTO `employee` (`EmpID`, `EmpName`, `Designation`, `Department`, `Office`, `Age`, `JoiningDate`, `Salary`) VALUES
(1, 'Tiger Nixonasdasda', 'System Architect', 'IT', 'Edinburgh', 61, '2011-04-25 00:00:00', 320800),
(2, 'Shobha Saini', 'Senior Manager', 'HR', 'India', 38, '2011-07-25 00:00:00', 190750),
(3, 'Ashton Cox', 'Junior Technical Author', 'Engineering', 'San Francisco', 53, '2009-01-12 00:00:00', 86000),
(4, 'Aakash Kumar', 'Senior Javascript Developer', 'IT', 'Edinburgh', 28, '2012-03-29 00:00:00', 433060),
(5, 'Sagar Kumar', 'Accountant', 'Accounts', 'Tokyo', 32, '2008-11-28 00:00:00', 175700),
(6, 'Brielle Williamson', 'Integration Specialist', 'IT', 'New York', 55, '2012-12-02 00:00:00', 354780),
(7, 'Herrod Chandler', 'Sales Assistan', 'Sales', 'San Francisco', 39, '2012-08-06 00:00:00', 234500),
(8, 'Rhona Davidson', 'Integration Specialist', 'IT', 'Tokyo', 55, '2010-10-14 00:00:00', 327900),
(9, 'Colleen Hurst', 'Talent Aquision', 'HR', 'San Francisco', 40, '2009-09-15 00:00:00', 205500),
(10, 'Sonya Frost', 'Software Engineer', 'IT', 'Edinburgh', 22, '2008-12-13 00:00:00', 110600),
(11, 'Jena Gaines', 'Office Manager', 'Admin', 'London', 30, '2008-12-19 00:00:00', 99800);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;