This post is third part of a series called Getting Started with Datatable 1.10 .

  1. DataTable demo (Server side) in Php,Mysql and Ajax
  2. DataTable (Server side) Custom Column Search
  3. Datatable Scroller (Server Side)
  4. DataTable Responsive (Server side)
  5. Datatable Custom Loader
  6. Datatable Colvis (Column Visibility Customization)
  7. DataTable Table tools
  8. DataTable Custom Search By Tags Input (Server side)
  9. DataTable Search By Datepicker (Server side)
  10. Datatable Bulk Delete (Server Side)
  11. Datatable Page Resize (Server Side)

Suppose you have millions of records, you don’t need pagination ,Then you can go for datatable scroller. I think scroller is better user experience than pagination, because virtually you will able to view whole table at once. Today I will show how to implement scroller in datatable.js v1.10 and show server side data through ajax. To implement scroller in datatable you have to add a js library or extension dataTables.scroller.js. You can download directly from here.

1
<script type="text/javascript" language="javascript" src="js/dataTables.scroller.js"></script>

Datatable will fetch some extra records as per your viewport (ie, table), for smooth redering. It will request to server for new records as per scroller position.

let me initialize the datatable:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
$(document).ready(function() {
   var dataTable =  $('#employee-grid').DataTable( {
    serverSide: true,
    ajax:{
            url :"employee-grid-data.php", // json datasource
            type: "post",  // method  , by default get
            error: function(){  // error handling
                $(".employee-grid-error").html("");
                $("#employee-grid").append('<tbody class="employee-grid-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
                $("#employee-grid_processing").css("display","none");
            }
        },
    dom: "frtiS",
    scrollY: 200,
    deferRender: true,
    scroller: {
        loadingIndicator: true
    }
    } );
} );

Three option is important here:

  • dom: “frtiS” Dom element of the table , here “f ” for filtering, “r” for processing , “t” for table, “i” for info of the table and “S” for scroller
  • scrollY: 200 This is grid height.
  • deferRender: true It will render chunk data in large amount data (table rows) from server only when you scroll. It will increase performance significantly.

As per your table height mentioned in “scrollY”, scroller will trigger ajax with starting row number and limit. Suppose, you have 200 records in your database. In your table viewport showing only 5 rows, then by default scroller will fetch 10 records for smooth rendering. In next scroll trigger ajax will request for more 10 records. This length calculated as per “scrollY” by datatable scroller itself.

datatable scroller viewport

In my first post , I have discussed about the server side processing. Depending your search parameter, order and limit, here is the server side code :

01
02
03
04
05
06
07
08
09
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
// Database connections
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "test";
$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());
 
// storing  request (ie, get/post) global array to a variable
$requestData= $_REQUEST;
 
$columns = array(
// datatable column index  => database column name
    0 =>'employee_name',
    1 => 'employee_salary',
    2=> 'employee_age'
);
 
// getting total number records without any search
$sql = "SELECT employee_name, employee_salary, employee_age ";
$sql.=" FROM employee";
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.
 
$sql = "SELECT employee_name, employee_salary, employee_age ";
$sql.=" FROM employee WHERE 1=1";
if( !empty($requestData['search']['value']) ) {   // if there is a search parameter, $requestData['search']['value'] contains search parameter
    $sql.=" AND ( employee_name LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR employee_salary LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR employee_age LIKE '".$requestData['search']['value']."%' )";
}
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result.
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."   LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
/* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc , $requestData['start'] contains start row number ,$requestData['length'] contains limit length. */
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
 
$data = array();
while( $row=mysqli_fetch_array($query) ) {  // preparing an array
    $nestedData=array();
 
    $nestedData[] = $row["employee_name"];
    $nestedData[] = $row["employee_salary"];
    $nestedData[] = $row["employee_age"];
 
    $data[] = $nestedData;
}
 
$json_data = array(
            "draw"            => intval( $requestData['draw'] ),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
            "recordsTotal"    => intval( $totalData ),  // total number of records
            "recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
            "data"            => $data   // total data array
            );
 
echo json_encode($json_data);  // send data as json format