Jquery Datatables Server-Side Paginaton and Sorting in PHP and MYSQL

: 541

Last Updated : 03/06/2023

In this article, I have mentioned how we can create jQuery Datatable with server-side processing in PHP with a database, so for example, we have below database.

Database Name:db_2023

Table name: emp

CREATE TABLE `emp` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(50) NOT NULL,  `email` varchar(100) NOT NULL,  `mobile` varchar(20) NOT NULL,  PRIMARY KEY (`id`) )

Sample data in the table as below:

INSERT INTO `emp` (`id`, `name`, `email`, `mobile`) VALUES

(1, 'yagati venkata narayana', 'yvn@gmail.com', '9999999999'),

(2, 'yagati satya narayana', 'ysn@gmail.com', '8888888888'),

(3, 'Yagati Nagesrara Rao', 'ynr@gmail.com', '7777777777'),

(4, 'Yagati Machiyya', 'ym@gmail.com', '6666666666'),

(5, 'Yagati Rama Krishna', 'yrk@gmail.com', '5555555555');

To implement server-side pagination and sorting using jQuery DataTables in PHP, you'll need to perform the following steps:

Step 1: Set up your HTML page: Create an HTML table with the appropriate structure and include the necessary CSS and JavaScript files for jQuery and DataTables.

<!doctype html>
<html>
   <head>
      <title>DataTable AJAX pagination using PHP and Mysqli</title>
      <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
      <link href='https://cdn.datatables.net/1.13.4/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>
      <link href='https://cdn.datatables.net/responsive/2.4.1/css/responsive.dataTables.min.css' rel='stylesheet' type='text/css'>
      <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
      <script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.min.js"></script>
      <script src="https://cdn.datatables.net/responsive/2.4.1/js/dataTables.responsive.min.js"></script>
   </head>
   <body >
      <div class="container" >
         <div class="row" style="padding:50px;">
            <p>
            <h1>Users</h1>
            </p>
            <div >
               <table id='empTable' class="display responsive nowrap" width='100%'>
                  <thead>
                     <tr>
                        <th>User Name</th>
                        <th>Email</th>
                        <th>Mobile</th>
                     </tr>
                  </thead>
               </table>
            </div>
         </div>
      </div>
      <script>
         $(document).ready(function(){
         
         var empDataTable = $('#empTable').DataTable({
         
         'processing': true,
         
         'serverSide': true,
         
         'responsive': true,
         
         'serverMethod': 'post',
         
         'ajax': {
         
         'url':'fetch_records.php'
         
         },
         
         pageLength: 25,
         
         'columns': [
         
         { data: 'name' },
         
         { data: 'email' },
         
         { data: 'mobile' }                    
         
         ]
         
         });
         
         });
         
      </script>
   </body>
</html>

Step 2: Create the PHP file (e.g., fetch_records.php) that will handle server-side requests and provide the data to the DataTable.

<?php

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "db_2023";

// Create connection

$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection

if (!$conn) {

  die("Connection failed: " . mysqli_connect_error());

}

$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($conn,$_POST['search']['value']); // Search value

## Search

$searchQuery = " ";

if($searchValue != ''){

   $searchQuery .= " and (name like '%".$searchValue."%' or

            email like '%".$searchValue."%' or

            mobile like'%".$searchValue."%' ) ";

}

## Total number of records without filtering

$sel = mysqli_query($conn,"select count(*) as allcount from emp");

$records = mysqli_fetch_assoc($sel);

$totalRecords = $records['allcount'];

## Total number of records with filtering

$sel = mysqli_query($conn,"select count(*) as allcount from emp WHERE 1 ".$searchQuery);

$records = mysqli_fetch_assoc($sel);

$totalRecordwithFilter = $records['allcount'];

## Fetch records

$empQuery = "select * from emp WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;

$empRecords = mysqli_query($conn, $empQuery);

$data = array();

while($row = mysqli_fetch_assoc($empRecords)){

    $data[] = array(

            "name"=>$row['name'],

            "email"=>$row['email'],

            "mobile"=>$row['mobile']            

        );

}

## Response

$response = array(

    "draw" => intval($draw),

    "iTotalRecords" => $totalRecords,

    "iTotalDisplayRecords" => $totalRecordwithFilter,

    "aaData" => $data

);

echo json_encode($response);

?>

With these steps, you should have a server-side pagination and sorting implementation using jQuery DataTables in PHP.

The DataTable will make AJAX requests to the data.php file, passing necessary parameters such as pagination, sorting, and search values.

The PHP file will handle these requests, retrieve the required data, apply filtering and sorting, and return the response in the expected JSON format.

The DataTable will then render the data in the HTML table, enabling pagination and sorting.