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

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

Grid view is a very important web component in modern web. Sorting, searching, pagination is not a easy job in HTML tables. So many grid view framework out there, DataTable.js is the most popular among them. It is open source, light weighted, highly flexible and customizable, features like AutoFill, inline editor, sticky header, responsive, Supports bootstrap, foundation. In version 1.10 DataTable has changed and improved over version 1.9. An entirely new API is available in DataTables 1.10.
In my blog I will try to explore datatable 1.10. So lets get started with most popular gridview framework in the planet.

In basic initialization datatable provides pagination, sorting, instant searching by loading whole data records at once. It can be a performance issue fetching large amount of data from server side. It will be better if you integrate server side pagination, searching and sorting, so we can break large amount data in chunk, So performance will increase significantly. Before proceed please take a look of the demo.

First i have created a index.php, where I have written html markup and basic initialization. created a table wth id “employee-grid” containing three column “Employee name”, “Salary”, “Age”.

1
2
3
4
5
6
7
8
9
<table id="employee-grid"  cellpadding="0" cellspacing="0" border="0" class="display" width="100%">
        <thead>
            <tr>
                <th>Employee name</th>
                <th>Salary</th>
                <th>Age</th>
            </tr>
        </thead>
</table>

Then add required assets in head which are jquery library, datatable js library and css . You can download them here. You can add directly cdn link of these files from here

1
2
3
<link rel="stylesheet" type="text/css" href="css/jquery.dataTables.css">
<script type="text/javascript" language="javascript" src="js/jquery.js"></script>
<script type="text/javascript" language="javascript" src="js/jquery.dataTables.js"></script>

Now its time to initialize the datatable with your table “employee-grid” . By ajax api we can fetch json the data from employee-grid-data.php and datatble will automatically handle fetched data and append after “thead”.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
<script type="text/javascript" language="javascript" >
    $(document).ready(function() {
        var dataTable = $('#employee-grid').DataTable( {
            "processing": true,
            "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");
 
                }
            }
        } );
    } );
</script>

Datatable send a request to “employee-grid-data.php” with so many parameters like sorting column index, order, search value, start and length/limit and draw number.
So what is draw ? sending request for any operation by datatable is called draw. it send a random number and recieve same same number as draw to ensure that the Ajax returns from server-side requests are drawn in sequence by DataTables.

In firebug or any developers tools you can check sending parameters.
datatable_param

In employee-grid-data.php you can see the parameter by print the Array $_REQUEST, like.

1
2
3
4
5
<?php
    echo "<pre>";
    print_r($_REQUEST);
    echo "</pre>";
 ?>

Output:

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
57
58
59
60
61
62
63
64
65
66
67
68
Array
(
    [draw] => 2
    [columns] => Array
        (
            [0] => Array
                (
                    [data] => 0
                    [name] =>
                    [searchable] => true
                    [orderable] => true
                    [search] => Array
                        (
                            [value] =>
                            [regex] => false
                        )
 
                )
 
            [1] => Array
                (
                    [data] => 1
                    [name] =>
                    [searchable] => true
                    [orderable] => true
                    [search] => Array
                        (
                            [value] =>
                            [regex] => false
                        )
 
                )
 
            [2] => Array
                (
                    [data] => 2
                    [name] =>
                    [searchable] => true
                    [orderable] => true
                    [search] => Array
                        (
                            [value] =>
                            [regex] => false
                        )
 
                )
 
        )
 
    [order] => Array
        (
            [0] => Array
                (
                    [column] => 0
                    [dir] => asc
                )
 
        )
 
    [start] => 0
    [length] => 10
    [search] => Array
        (
            [value] => b
            [regex] => false
        )
 
)

Here I have created a columns array which contains database columns according to datatable column index. Because datatable sends only column index.

1
2
3
4
5
6
7
8
<?php
$columns = array(
// datatable column index  => database column name
    0 =>'employee_name',
    1 => 'employee_salary',
    2=> 'employee_age'
);
?>

Now we have to create json object which will contain .

  • draw: we send same number which has been send by datatable at time of draw/request.
  • recordsTotal: Total numbers of records in your table.
  • recordsFiltered: Total numbers of filtered records after searching in your table. If you do not perform any search then recordsFiltered will be same as recordsTotal.
  • data: Your fetched records data. You have to fetched the data as per start, length, search value, colomn name and sorting orders parameters. you can download dummy database table from here
1
2
3
4
5
6
7
$json_data = array(
                "draw"            => intval( $_REQUEST['draw'] ),
                "recordsTotal"    => intval( $totaldata ),
                "recordsFiltered" => intval( $totalfiltered ),
                "data"            => $data
            );
echo json_encode($json_data);

You can download full code from below or improve my code by forking at Github.