DataTable with Ajax Data Source

Post Views: 1,276

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

<!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>

<!DOCTYPE html>

<html>

<head>

<title>DataTable with Ajax Data Source</title>

</head>

<body>

<h1>DataTable with Ajax Data Source Example</h1>

<thead>

<tr>

<th>Position</th>

<th>Office</th>

<th>Start Date</th>

<th>Salary</th>

</tr>

</thead>

<tbody>

</tbody>

</table>

$('#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

--
-- 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;

-- 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

<?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;
    }
}

<?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;

}

<?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();

<?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