DataTables logo DataTables

via Ad Packs

Server-side processing

There are times when reading data from the DOM is simply too slow or unwieldy, particularly when dealing with thousands or millions of data rows. To address this DataTables' server-side processing feature provides a method to let all the "heavy lifting" be done by a database engine on the server-side (they are after-all highly optimised for exactly this kind of thing), and then have that information drawn in the user's web-browser. As such you can display tables consisting of millions of rows with ease.

When using server-side processing, DataTables will make an XHR request to the server for each draw of the information on the page (i.e. when paging, sorting, filtering etc). DataTables will send a number of variables to the server to allow it to perform the required processing, and then return the data in the format required by DataTables.

Parameters sent to the server

The following information is sent to the server for each draw request. Your server-side script must use this information to obtain the data required for the draw.

Type Name Info
int iDisplayStart Display start point in the current data set.
int iDisplayLength Number of records that the table can display in the current draw. It is expected that the number of records returned will be equal to this number, unless the server has fewer records to return.
int iColumns Number of columns being displayed (useful for getting individual column search info)
string sSearch Global search field
bool bRegex True if the global filter should be treated as a regular expression for advanced filtering, false if not.
bool bSearchable_(int) Indicator for if a column is flagged as searchable or not on the client-side
string sSearch_(int) Individual column filter
bool bRegex_(int) True if the individual column filter should be treated as a regular expression for advanced filtering, false if not
bool bSortable_(int) Indicator for if a column is flagged as sortable or not on the client-side
int iSortingCols Number of columns to sort on
int iSortCol_(int) Column being sorted on (you will need to decode this number for your database)
string sSortDir_(int) Direction to be sorted - "desc" or "asc".
string mDataProp_(int) The value specified by mDataProp for each column. This can be useful for ensuring that the processing of data is independent from the order of the columns.
string sEcho Information for DataTables to use for rendering.

Reply from the server

In reply to each request for information that DataTables makes to the server, it expects to get a well formed JSON object with the following parameters.

Type Name Info
int iTotalRecords Total records, before filtering (i.e. the total number of records in the database)
int iTotalDisplayRecords Total records, after filtering (i.e. the total number of records after filtering has been applied - not just the number of records being returned in this result set)
string sEcho An unaltered copy of sEcho sent from the client side. This parameter will change with each draw (it is basically a draw count) - so it is important that this is implemented. Note that it strongly recommended for security reasons that you 'cast' this parameter to an integer in order to prevent Cross Site Scripting (XSS) attacks.
string sColumns Deprecated Optional - this is a string of column names, comma separated (used in combination with sName) which will allow DataTables to reorder data on the client-side if required for display. Note that the number of column names returned must exactly match the number of columns in the table. For a more flexible JSON format, please consider using mData.

Note that this parameter is deprecated and will be removed in v1.10. Please now use mData.
array aaData The data in a 2D array. Note that you can change the name of this parameter with sAjaxDataProp.

In addition to the above parameters as control for the overall table, DataTables can use the following optional parameters on each individual row's data source object to perform automatic actions for you:

Type Name Info
string DT_RowId Set the ID property of the TR node for this row
string DT_RowClass Add the this class to the TR node for this row

Example JSON return

DataTables is extremely flexible in the JSON data that it can consume from the server through the mDataProp option which allows you to get data from arrays (the default) or Javascript objects, including nested-objects and array.

By default DataTables will use the "aaData" property of the returned data which is an array of arrays with one entry for each column in the table. This is shown below:

{
    "sEcho": 3,
    "iTotalRecords": 57,
    "iTotalDisplayRecords": 57,
    "aaData": [
        [
            "Gecko",
            "Firefox 1.0",
            "Win 98+ / OSX.2+",
            "1.7",
            "A"
        ],
        [
            "Gecko",
            "Firefox 1.5",
            "Win 98+ / OSX.2+",
            "1.8",
            "A"
        ],
        ...
    ] 
}

This example shows basically the same example, but in this case an array of objects is used as the data source and DT_RowId and DT_RowClass are both given:

{
    "sEcho": 3,
    "iTotalRecords": 57,
    "iTotalDisplayRecords": 57,
    "aaData": [
        {
            "DT_RowId": "row_7",
            "DT_RowClass": "gradeA",
            "0": "Gecko",
            "1": "Firefox 1.0",
            "2": "Win 98+ / OSX.2+",
            "3": "1.7",
            "4": "A"
        },
        {
            "DT_RowId": "row_8",
            "DT_RowClass": "gradeA",
            "0": "Gecko",
            "1": "Firefox 1.5",
            "2": "Win 98+ / OSX.2+",
            "3": "1.8",
            "4": "A"
        },
        ...
    ]
}

Initialisation parameters

bServerSide
Show details

Configure DataTables to use server-side processing. Note that the sAjaxSource parameter must also be given in order to give DataTables a source to obtain the required data for each draw.

Default: false
Type: boolean
Code example:
   $(document).ready( function () {
     $('#example').dataTable( {
       "bServerSide": true,
       "sAjaxSource": "xhr.php"
     } );
   } );
fnServerData
Show details

This parameter allows you to override the default function which obtains the data from the server ($.getJSON) so something more suitable for your application. For example you could use POST data, or pull information from a Gears or AIR database.

Default:
Type: function
Code example:
   // POST data to server
   $(document).ready( function() {
     $('#example').dataTable( {
       "bProcessing": true,
       "bServerSide": true,
       "sAjaxSource": "xhr.php",
       "fnServerData": function ( sSource, aoData, fnCallback, oSettings ) {
         oSettings.jqXHR = $.ajax( {
           "dataType": 'json', 
           "type": "POST", 
           "url": sSource, 
           "data": aoData, 
           "success": fnCallback
         } );
       }
     } );
   } );
fnServerParams
Show details

It is often useful to send extra data to the server when making an Ajax request - for example custom filtering information, and this callback function makes it trivial to send extra information to the server. The passed in parameter is the data set that has been constructed by DataTables, and you can add to this or modify it as you require.

Default:
Type: function
Code example:
   $(document).ready( function() {
     $('#example').dataTable( {
       "bProcessing": true,
       "bServerSide": true,
       "sAjaxSource": "scripts/server_processing.php",
       "fnServerParams": function ( aoData ) {
         aoData.push( { "name": "more_data", "value": "my_value" } );
       }
     } );
   } );
sAjaxDataProp
Show details

By default DataTables will look for the property 'aaData' when obtaining data from an Ajax source or for server-side processing - this parameter allows that property to be changed. You can use Javascript dotted object notation to get a data source for multiple levels of nesting.

Default: aaData
Type: string
Code example:
   // Get data from { "data": [...] }
   $(document).ready( function() {
     var oTable = $('#example').dataTable( {
       "sAjaxSource": "sources/data.txt",
       "sAjaxDataProp": "data"
     } );
   } );
   
 
   // Get data from { "data": { "inner": [...] } }
   $(document).ready( function() {
     var oTable = $('#example').dataTable( {
       "sAjaxSource": "sources/data.txt",
       "sAjaxDataProp": "data.inner"
     } );
   } );
sAjaxSource
Show details

You can instruct DataTables to load data from an external source using this parameter (use aData if you want to pass data in you already have). Simply provide a url a JSON object can be obtained from. This object must include the parameter 'aaData' which is the data source for the table.

Default: null
Type: string
Code example:
   $(document).ready( function() {
     $('#example').dataTable( {
       "sAjaxSource": "http://www.sprymedia.co.uk/dataTables/json.php"
     } );
   } )
sServerMethod
Show details

Set the HTTP method that is used to make the Ajax call for server-side processing or Ajax sourced data.

Default: GET
Type: string
Code example:
   $(document).ready( function() {
     $('#example').dataTable( {
       "bServerSide": true,
       "sAjaxSource": "scripts/post.php",
       "sServerMethod": "POST"
     } );
   } );