?> Datatables server side processing

Datatables server side processing

With server-side processing enabled, all paging, searching, ordering actions that DataTables performs are handed off to a server where an SQL engine (or similar) can perform these actions on the large data set. As such, each draw of the table will result in a new Ajax request being made to get the required data.

If you are required to select data from multiple tables, the usual method would be to use joins. However, this can be very complicated when using server side processing. After thorough research, no well explained examples or methods to affectively get joins to work were found. In order to overcome this issue, I selected all the data from multiple tables using joins as usual, then inserted it into a new table. Then as the example below demonstrates, I selected the data from the new table using SSP.

Side note - If your data needs to be updated regularly, CRON can be set up in order to run the script to select all data and insert into the new table.

SQL Details:

  $sql_details = array(
'user' => '',
'pass' => '',
'db' => '',
'host' => ''
);

PHP Code for sever side processing:

  require( 'ssp.class.php' ); // Required. Contains SSP:simple & SSP:complex 

$primaryKey = "Id";
$table="table_name";
$columns = array(
array( 'db' => 'name', 'dt' => 'Name' ), // first column is db column name, second is datatables data name
array( 'db' => 'age, 'dt' => 'Age' ),
array( 'db' => 'dob', 'dt' => 'DOB' ),
array( 'db' => 'address', 'dt' => 'Address' ),
array( 'db' => 'email', 'dt' => 'Email' ),
array( 'db' => 'order_no', 'dt' => 'OrderNo' )
);

$minage = $_POST['minage']; // passed from ajax on datatables JS
$maxage = $_POST['maxage'];
$name = $_POST['name'];

if (!empty($minage) && !empty($maxage)) { // search between 2 dates entered
$whereAll[] = "age >='".$minage."' AND age <='".$maxage."' ";
} else {
$whereAll[] = "age > '18'";
}

if (!empty($clientsearch)) { // searches for name
$whereAll[] = "name = '".$name"'";
}

if (is_array($whereAll)) { // $whereAll array -> users can do multiple searches at once e.g. search between ages and for a name
$whereAll = implode(" AND ", $whereAll);
}

echo json_encode(SSP::complex ($_POST, $sql_details, $table, $primaryKey, $columns, $whereResult=null, $whereAll));


These are the parameters required SSP::simple & SSP::complex. SSP::complex has additional parameters; $whereResult & $whereAll. The parameters are explained below:

  Parameters for SSP::simple & SSP:complex 

@param array $request Data sent to server by DataTables
@param array connection resource or connection parameters array
@param string $table SQL table to query
@param string $primaryKey Primary key of the table
@param array $columns Column information array
@param string $whereResult WHERE condition to apply to the result set
@param string $whereAll WHERE condition to apply to all queries
@return array Server-side processing response array

Javascript:

This contains all of the javascript required to initialise Datatables. It also contains several additional options. The serverSide option is required for server side processing to work. I am also using ajax to pass additional data to the PHP in order for searching.

  var table = $('#maintable').DataTable( {
"processing": true,
"serverSide": true,
"responsive": true,
"rowId": "Id",
"ajax": {
"url": "URL",
"type": "POST", //get or post
"data": function ( d ) {
d.minage = $('#min').val(); // get value of input from user - id of the text box user enters data into
d.maxage = $('#max').val();
d.name = $('#name').val();
}
},
"columns": [
{title: "Name", data:"Name" },
{title: "Age", data:"Age" },
{title: "Date of Birth", data:"DOB" },
{title: "Address", data:"Address" },
{title: "Email", data: "Email"},
{title: "Order Number", data:"OrderNo" }
]
}

$(document).on('click', '#searchage', function(dn) { // reload tables based on min age & max age entered by user
$('#maintable').DataTable().ajax.reload();
});

$(document).on('click', '#clearages', function(dn) { // reload the table to show full results when user clicks clear age button
$('#min').val("");
$('#max').val("");
$('#maintable').DataTable().ajax.reload();
});

$(document).on('click', '#filternames', function(dn) { // reload table based on name entered to search for
$('#maintable').DataTable().ajax.reload();
});

Any additional information required look at the data tables server side processing information here

Top