
Hello Friends, Hope you are doing well today I am going to share how to use DataTable with Ajax Data Source or you can say how to perform server side processing in DataTable.
Actually I was working on one of my personal project where I was needed this feature and After implementing on my project I thought to share it with you guys. If you don’t know what DataTable is then let me tell you it is nothing but a plug-in for the jQuery Javascript library to work with Html Tables. It comes with various features like searching, sorting, pagination. Let’s start
We will do this coding in three steps:-
- Html & Javascript
- Table Structure
- PHP Script
Html & Javascript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
<!DOCTYPE html> <html> <head> <title>DataTable with Ajax Data Source</title> <!-- Datatable CSS --> <link href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css" /> </head> <body> <h1>DataTable with Ajax Data Source Example</h1> <!-- Table --> <table id="employeeTable"> <thead> <tr> <th>Name</th> <th>Position</th> <th>Office</th> <th>Age</th> <th>Start Date</th> <th>Salary</th> </tr> </thead> <tbody> </tbody> </table> <!-- jQuery Library --> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script> <!-- Datatable JS --> <script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script> <script> $('#employeeTable').dataTable({ 'processing': true, 'serverSide': true, 'serverMethod': 'post', "ajax": { "url": "get-data.php", }, 'columns': [{ data: 'name' }, { data: 'position' }, { data: 'office' }, { data: 'age' }, { data: 'start_date' }, { data: 'salary' }, ], destroy: true, }); </script> </body> </html> |
Table Structure
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
-- -- Table structure for table `employee` -- CREATE TABLE `employee` ( `id` int(11) NOT NULL, `name` varchar(256) NOT NULL, `position` varchar(256) NOT NULL, `office` varchar(256) NOT NULL, `age` int(11) NOT NULL, `start_date` date NOT NULL, `salary` varchar(56) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Indexes for table `employee` -- ALTER TABLE `employee` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for table `employee` -- ALTER TABLE `employee` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=58; COMMIT; |
PHP Scripts
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<?php date_default_timezone_set("Asia/Kolkata"); define("DATABASE_NAME", "datatables_db"); function getConnection() { $servername = "localhost"; $username = "root"; $password = ""; // Create connection $conn = new mysqli($servername, $username, $password, DATABASE_NAME); // Check connection if (mysqli_connect_error()) { die("Database connection failed: " . mysqli_connect_error()); } else { return $conn; } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
<?php // Database Connection include_once 'config.php'; $conn = getConnection(); $draw = $_REQUEST['draw']; $offset = $_REQUEST['start']; $limit = $_REQUEST['length']; // Rows display per page $columnIndex = $_REQUEST['order'][0]['column']; // Column index $order_by = $_REQUEST['columns'][$columnIndex]['data']; // Column name $order = $_REQUEST['order'][0]['dir']; // asc or desc $searchValue = mysqli_real_escape_string($conn, $_REQUEST['search']['value']); // Search value // Search Query $searchQuery = " "; if ($searchValue != '') { $searchQuery = " AND (name LIKE '%$searchValue%' OR position LIKE '%$searchValue%' OR office LIKE '%$searchValue%' OR age LIKE '%$searchValue%' OR start_date LIKE '%$searchValue%' OR salary LIKE '%$searchValue%') "; } // Total entries before filter $results = $conn->query("SELECT COUNT(id) as total FROM employee"); $record = $results->fetch_assoc(); $totalRecords = $record['total']; // Total entries after filter $results = $conn->query("SELECT COUNT(id) as total FROM employee WHERE 1 " . $searchQuery); $record = $results->fetch_assoc(); $totalRecordwithFilter = $record['total']; // Entries with filter, order & limit $results = $conn->query("SELECT * FROM employee WHERE 1 " . $searchQuery . " ORDER BY " . $order_by . " " . $order . " LIMIT " . $offset . "," . $limit); $data = array(); while ($row = $results->fetch_assoc()) { $data[] = $row; } // Response $response = array( "draw" => intval($draw), "iTotalRecords" => $totalRecords, "iTotalDisplayRecords" => $totalRecordwithFilter, "aaData" => $data ); header("Content-Type:application/json"); echo json_encode($response); exit(); |
Output
Script Download
Note:
Please make sure that AJAX response data field names should match with columns
data defined during DataTable initialization otherwise field value not be read.
Thanks friends
Your queries & suggestions are welcome in comments section
Please don’t forget to share if you find this helpful
Comments