Today i will explain how to avoid high Memory Usage using DataTables in Server-Side Processing Mode. Also, we will add an Advanced Filter.
Server-side processing
There are times when reading data from the DOM is simply too slow or unwieldy, particularly when dealing with many 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 use case!), and then have that information drawn in the user’s web-browser. Consequently, you can display tables consisting of millions of rows with ease.
When using server-side processing, DataTables will make an Ajax request to the server for each draw of the information on the page (i.e. when paging, ordering, searching, 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. [Extracted from DataTables.net]
Fully Working DEMO for Test Purposes: Click here to TEST
Download Source Files: Clickto go to Github Repository
FILE: index.html (CDN Sources)
<html>
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css">
<style> #table1_filter{display:none;}</style>
</head>
FILE: index.html (Html Filter Form Template)
The filter options for each input are: 
“S”= Starts with …
“C”= Contains …
“F”= Finish wih …
“W”=Whole word …
“Aa”= Match case …
<body>
<div class="container" id="">
<br>
<div class="panel panel-default" id="advance_filter" >
<div class="panel-heading">
<h3 class="panel-title" >Advanced Filter : </h3>
</div>
<div class="panel-body">
<form id="form-filter" class="form-horizontal">
<div class="form-group">
<div class="col-md-8 col-sm-8 col-xs-12">
<label for="email" class="col-sm-3 col-xs-3 control-label">Email</label>
<div class="col-sm-9 col-xs-9">
<input type="text" class="form-control" id="user_E800_email">
</div>
</div>
<div class="col-md-4 col-sm-4 col-xs-12">
<div class="input-group radioBtns">
<div id="radioBtn" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E801_radioEmail" title="Start with.." data-title="S">S</a>
<a class="btn btn-primary btn-sm active" data-toggle="user_E801_radioEmail" title="Contains.." data-title="C">C</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E801_radioEmail" title="Finish with.." data-title="F">F</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E801_radioEmail" title="Whole word" data-title="W">W</a>
</div>
<input type="hidden" name="user_E801_radioEmail" id="user_E801_radioEmail" value="C">
<div id="radioBtn2" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E802_radioEmail2" title="Match case" data-title="A" value="a">Aa</a>
</div>
<input type="hidden" name="user_E802_radioEmail2" id="user_E802_radioEmail2" value="a">
</div>
</div>
</div>
<div class="form-group">
<div class="col-md-8 col-sm-8 col-xs-12">
<label for="first_name" class="col-sm-3 col-xs-3 control-label">First name</label>
<div class="col-sm-9 col-xs-9">
<input type="text" class="form-control" id="user_E803_first_name">
</div>
</div>
<div class="col-md-4 col-sm-4 col-xs-12">
<div class="input-group radioBtns">
<div id="radioBtn" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E804_radioFirstname" title="Start with.." data-title="S">S</a>
<a class="btn btn-primary btn-sm active" data-toggle="user_E804_radioFirstname" title="Contains.." data-title="C">C</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E804_radioFirstname" title="Finish with.." data-title="F">F</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E804_radioFirstname" title="Whole word" data-title="W">W</a>
</div>
<input type="hidden" name="user_E804_radioFirstname" id="user_E804_radioFirstname" value="C">
<div id="radioBtn2" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E805_radioFirstname2" title="Match case" data-title="A" value="A">Aa</a>
</div>
<input type="hidden" name="user_E805_radioFirstname2" id="user_E805_radioFirstname2" value="a">
</div>
</div>
</div>
<div class="form-group">
<div class="col-md-8 col-sm-8 col-xs-12">
<label for="last_name" class="col-sm-3 col-xs-3 control-label">Last name</label>
<div class="col-sm-9 col-xs-9">
<input type="text" class="form-control" id="user_E806_last_name">
</div>
</div>
<div class="col-md-4 col-sm-4 col-xs-12">
<div class="input-group radioBtns">
<div id="radioBtn" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E807_radioLastname" title="Start with.." data-title="S">S</a>
<a class="btn btn-primary btn-sm active" data-toggle="user_E807_radioLastname" title="Contains.." data-title="C">C</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E807_radioLastname" title="Finish with.." data-title="F">F</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E807_radioLastname" title="Whole word" data-title="W">W</a>
</div>
<input type="hidden" name="user_E807_radioLastname" id="user_E807_radioLastname" value="C">
<div id="radioBtn2" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E808_radioLastname2" title="Match case" data-title="A" value="A">Aa</a>
</div>
<input type="hidden" name="user_E808_radioLastname2" id="user_E808_radioLastname2" value="a">
</div>
</div>
</div>
<div class="form-group">
<div class="col-md-8 col-sm-8 col-xs-12">
<label for="employee_id" class="col-sm-3 col-xs-3 control-label">Employee ID</label>
<div class="col-sm-9 col-xs-9">
<input type="text" class="form-control" id="user_E809_employee_id">
</div>
</div>
<div class="col-md-4 col-sm-4 col-xs-12">
<div class="input-group radioBtns">
<div id="radioBtn" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E810_radioEmployee_id" title="Start with.." data-title="S">S</a>
<a class="btn btn-primary btn-sm active" data-toggle="user_E810_radioEmployee_id" title="Contains.." data-title="C">C</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E810_radioEmployee_id" title="Finish with.." data-title="F">F</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E810_radioEmployee_id" title="Whole word" data-title="W">W</a>
</div>
<input type="hidden" name="user_E810_radioEmployee_id" id="user_E810_radioEmployee_id" value="C">
<div id="radioBtn2" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E811_radioEmployee_id2" title="Match case" data-title="A" value="A">Aa</a>
</div>
<input type="hidden" name="user_E811_radioEmployee_id2" id="user_E811_radioEmployee_id2" value="a">
</div>
</div>
</div>
<div class="form-group">
<div class="col-md-8 col-sm-8 col-xs-12">
<label for="status" class="col-sm-3 col-xs-3 control-label">Status</label>
<div class="col-sm-9 col-xs-9">
<input type="text" class="form-control" id="user_E812_status">
</div>
</div>
<div class="col-md-4 col-sm-4 col-xs-12">
<div class="input-group radioBtns">
<div id="radioBtn" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E813_radioStatus" title="Start with.." data-title="S">S</a>
<a class="btn btn-primary btn-sm active" data-toggle="user_E813_radioStatus" title="Contains.." data-title="C">C</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E813_radioStatus" title="Finish with.." data-title="F">F</a>
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E813_radioStatus" title="Whole word" data-title="W">W</a>
</div>
<input type="hidden" name="user_E813_radioStatus" id="user_E813_radioStatus" value="C">
<div id="radioBtn2" class="btn-group">
<a class="btn btn-primary btn-sm notActive" data-toggle="user_E814_radioStatus2" title="Match case" data-title="A" value="A">Aa</a>
</div>
<input type="hidden" name="user_E814_radioStatus2" id="user_E814_radioStatus2" value="a">
</div>
</div>
</div>
<div class="form-group">
<label for="" class="col-sm-2 control-label"></label>
<div class="col-sm-12" style="text-align:center;">
<button type="button" id="B114_btn_filter" class="btn btn-primary btn-sm">Filter</button>
<button type="button" id="B115_btn_reset" class="btn btn-default btn-sm">Reset</button>
</div>
</div>
</form>
</div>
</div>
FILE: index.html (Html Table Template)
<form id="F110_gen_form" name="myForm" method="post" action="" onSubmit="" enctype = text/plain >
<div class="table-responsive">
<table
id="table1"
data-order="[]"
data-columnDefs='[{"targets":[0],"orderable":false}]'
data-columnFilter='{"aoColumns": [ null, {"type": "text"}, {"type": "text"} ]}'
data-columnFilter-select2="true"
class="table table-striped table-sorted table-hover table-bordered table-columnfilter datatable">
<thead class="table-head-alt">
<tr>
<th id="filterCheck">
<label class="control1 control-checkbox1">
<input id="C111_gen_master_checkbox" type="checkbox" name="C111_gen_master_checkbox" class="checkbox"/>
<div class="control_indicator1"></div>
</label>
</th>
<th class="col-lg-2 col-md-2 col-sm-1 col-xs-1">Email</th>
<th>First Name</th>
<th>Last Name</th>
<th>Employee ID</th>
<th>Status</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</form>
</div>
</body>
FILE: index.html (SCRIPT)
<script>
$(document).ready(function() {
var oTable;
/* DATATABLE-------------------------------------------*/
oTable = $('#table1').DataTable({
//"sDom": '<"top"i>rt<"bottom"flp><"clear">',
//"bAutoWidth": false,
//"dom": '<Bpl><rt><ip>',
/* To change the appearance of the NAV BUTTONs */
/*"pagingType": "full_numbers",*/
/*"pagingType": "full",*/
//"pagingType": "input",
"bFilter": false,
"processing": true, //Feature control the processing indicator.
"serverSide": true, //Feature control DataTables' server-side processing mode.
/* To order the list taking into account the first column*/
/*
"order": [[ 0, "asc" ]],
*/
/* To disable the initial sortering (the table is not sorted initially) */
"order": [],
/*Another way: "aaSorting": [ ],*/
// Load data for the table's content from an Ajax source
"ajax": {
"url": "ajax.php",
"type": "POST",
"data": function ( data ) {
data.email = $('#user_E800_email').val();
data.first_name = $('#user_E803_first_name').val();
data.last_name = $('#user_E806_last_name').val();
data.employee_id = $('#user_E809_employee_id').val();
data.status = $('#user_E812_status').val();
data.radioEmail = $('#user_E801_radioEmail').val();
data.radioFirstname = $('#user_E804_radioFirstname').val();
data.radioLastname = $('#user_E807_radioLastname').val();
data.radioEmployee_id = $('#user_E810_radioEmployee_id').val();
data.radioStatus = $('#user_E813_radioStatus').val();
data.radioEmail2 = $('#user_E802_radioEmail2').val();
data.radioFirstname2 = $('#user_E805_radioFirstname2').val();
data.radioLastname2 = $('#user_E808_radioLastname2').val();
data.radioEmployee_id2 = $('#user_E811_radioEmployee_id2').val();
data.radioStatus2 = $('#user_E814_radioStatus2').val();
}
},
/* Disable column SORT (No sort icon on the affected column)*/
"aoColumnDefs": [
{
'bSortable': false,
'aTargets': [ 0 ],
"render": function ( data, type, row, meta ) {
return '<label class="control control-checkbox"> <input id="'+data+'" type="checkbox" name="C112_gen_checkbox" value="'+data+'" class="checkbox"/><div class="control_indicator"></div></label>';
}
}
],
/* To set the initial value to 50 */
"iDisplayLength": 10,
/* To change the values displayed in the DIAPLAYLENGHT*/
"oLanguage": {
"sLengthMenu": 'Display <select>'+
'<option value="10">10</option>'+
'<option value="25">25</option>'+
'<option value="50">50</option>'+
'<option value="100">100</option>'+
'<option value="500">500</option>'+
'<option value="1000">1000</option>'+
'<option value="-1">All</option>'+
'</select> records'
},
//buttons: []
});
//------------------------------------------------
$('#B114_btn_filter').click(function(){ //button filter event click
$('#B114_btn_filter').blur();
oTable.ajax.reload(); //just reload table
});
$('#B115_btn_reset').click(function(){ //button reset event click
$('#B115_btn_reset').blur();
$('#form-filter')[0].reset();
oTable.ajax.reload(); //just reload table
});
});
$('#radioBtn a').tooltip();
$('#radioBtn a').on('click', function(){
var sel = $(this).data('title');
console.log(sel);
var tog = $(this).data('toggle');
console.log(tog);
$('#'+tog).prop('value', sel);
$('a[data-toggle="'+tog+'"]').not('[data-title="'+sel+'"]').removeClass('active').addClass('notActive');
$('a[data-toggle="'+tog+'"][data-title="'+sel+'"]').removeClass('notActive').addClass('active');
});
$('#radioBtn2 a').tooltip();
$('#radioBtn2 a').on('click', function(){
var sel = $(this).data('title');
var tog = $(this).data('toggle');
if($('#'+tog).attr("value")==="a"){
$('#'+tog).attr("value", "A");
$('a[data-toggle="'+tog+'"][data-title="'+sel+'"]').removeClass('notActive').addClass('active');
}else{
$('#'+tog).attr("value", "a");
$('a[data-toggle="'+tog+'"][data-title="'+sel+'"]').removeClass('active').addClass('notActive');
}
})
</script>
</html>
As you can see, in Line 206 we specify the URL for the ajax request
"ajax": {
"url": "ajax.php",
"type": "POST",
"data": function ( data ) {
FILE: ajax.php
With this file we receive the parameters sent in the index.html file with ajax.
<?php
$email = $_POST['email'];
$radioEmail = $_POST['radioEmail'];
$radioEmail2 = $_POST['radioEmail2'];
$first_name = $_POST['first_name'];
$radioFirstname = $_POST['radioFirstname'];
$radioFirstname2 = $_POST['radioFirstname2'];
$last_name = $_POST['last_name'];
$radioLastname = $_POST['radioLastname'];
$radioLastname2 = $_POST['radioLastname2'];
$employee_id = $_POST['employee_id'];
$radioEmployee_id = $_POST['radioEmployee_id'];
$radioEmployee_id2 = $_POST['radioEmployee_id2'];
$status = $_POST['status'];
$radioStatus = $_POST['radioStatus'];
$radioStatus2 = $_POST['radioStatus2'];
$start = $_POST['start'];
$draw = $_POST['draw'];
$length = $_POST['length'];
$order = $_POST['order'];
$rows = "id, email, first_name, last_name, employee_id, status";
$column_order = array('id', 'email', 'first_name', 'last_name', 'employee_id', 'status');
$order_by = array('email' => 'asc');
$keys = [];
$keys[0][0]='email';
$keys[0][1]=$email;
$keys[0][2]='radioEmail';
$keys[0][3]= $radioEmail;
$keys[0][4]='radioEmail2';
$keys[0][5]=$radioEmail2;
$keys[1][0]='first_name';
$keys[1][1]=$first_name;
$keys[1][2]='radioFirstname';
$keys[1][3]=$radioFirstname;
$keys[1][4]='radioFirstname2';
$keys[1][5]=$radioFirstname2;
$keys[2][0]='last_name';
$keys[2][1]=$last_name;
$keys[2][2]='radioLastname';
$keys[2][3]=$radioLastname;
$keys[2][4]='radioLastname2';
$keys[2][5]=$radioLastname2;
$keys[3][0]='employee_id';
$keys[3][1]=$employee_id;
$keys[3][2]='radioEmployee_id';
$keys[3][3]=$radioEmployee_id;
$keys[3][4]='radioEmployee_id2';
$keys[3][5]=$radioEmployee_id2;
$keys[4][0]='status';
$keys[4][1]=$status;
$keys[4][2]='radioStatus';
$keys[4][3]=$radioStatus;
$keys[4][4]='radioStatus2';
$keys[4][5]=$radioStatus2;
require_once "model.php";
$test = new Test();
$selector = 1;
$list_1 = $test->server_side($selector, $rows, $keys, $length, $start, $order, $column_order, $order_by);
$selector = 2;
$list_2 = $test->server_side($selector, $rows, $keys, $length, $start, $order, $column_order, $order_by);
$count_1 = $test-> count_total();
$output = array(
"draw" => $draw,
"recordsTotal" => $count_1,
"recordsFiltered" => count($list_1),
"data" => $list_2,
);
//output to json format
echo json_encode($output);
?>
Within Lines 22-25, we can see the main vars that are being received from the Plugin. These vars are used for Pagination.
You can review those vars being sent by doing:
- Right Click in the page (“inspect”)
- Click in “Network”
- Go below “Response Headers” and “Request Headers” and look for “Form Data”
In LINE 67, we are calling a Class that creates the SQL statements
require_once "model.php"; $test = new Test();
FILE: model.php
<?php
require_once "conn.php";
class Test{
protected $db_con;
public function __construct() {
$database= new Connection();
$this->db_con = $database -> connect();
}
public function count_total(){
$sql= 'SELECT COUNT(*) Total FROM com_auth_users';
//die(var_dump($sql));
$stm = $this->db_con->prepare($sql);
$stm->execute();
$data=$stm->fetch();
$result= $data['Total'];
//die(var_dump($result));
return !empty($result) ? $result : false;
}
public function server_side($selector, $rows, $keys, $length, $start, $order, $column_order, $order_by){
$sql_a = "SELECT ".$rows." FROM `com_auth_users`";
$sql_b = "";
$flag = 0;
for ($i=0; $i<count($keys); $i++) { //0-4
if(!empty($keys[$i][1])){ //verifica si el input esta vacio
$flag++;
if($flag>1){
$sql_b = $sql_b. " AND ";
}
if($keys[$i][5] === "a"){
$sql_b = $sql_b;
}else if($keys[$i][5] === "A"){
$sql_b = " BINARY " .$sql_b;
}
if($keys[$i][3] === "C"){
$sql_b = $sql_b.$keys[$i][0]." LIKE '%".$keys[$i][1]."%' ";
}else if($keys[$i][3] === "S"){
$sql_b = $sql_b.$keys[$i][0]." LIKE '".$keys[$i][1]."%' ";
}else if($keys[$i][3] === "F"){
$sql_b = $sql_b.$keys[$i][0]." LIKE '%".$keys[$i][1]."' ";
}else if($keys[$i][3] === "W"){
$sql_b = $sql_b.$keys[$i][0]." = '".$keys[$i][1]."' ";
}
}
}
if ($sql_b == ""){
$sql = $sql_a;
}else{
$sql = $sql_a . " WHERE " . $sql_b;
}
// ordenamiento por columnas ( una a la vez)
if(isset($order)){
$sql = $sql . " ORDER BY " . $column_order[$order['0']['column']] . " " . $order['0']['dir'];
}else if(isset($order_by)){
$sql = $sql . " ORDER BY " . key($order_by) . " " . $order_by[key($order_by)];
}
switch ($selector) {
case 1:
$sql = $sql;
break;
case 2:
if($length>0){
$sql = $sql . " LIMIT ".$start.",". $length;
}else{
$sql = $sql;
}
break;
default:
if($length>0){
$sql = $sql . " LIMIT ".$start.",". $length;
}else{
$sql = $sql;
}
break;
}
// die(var_dump($sql));
$stm = $this->db_con ->prepare($sql);
$stm->execute();
$data=$stm->fetchAll(PDO::FETCH_NUM);
return $data;
}
}
?>
As you can see, in Line 3, we require a connection file:
<?php
require_once "conn.php";
class Test{
protected $db_con;
public function __construct() {
$database= new Connection();
$this->db_con = $database -> connect();
}
FILE: conn.php
<?php
class Connection{
protected $conn;
public function connect(){
$dsn = 'mysql:host=' . "localhost" .
';dbname=' . "your_db_name_here" .
';port=' . 3306 .
';';
$user = "your_db_user_here";
$password = "your_db_password_here";
try {
$this ->conn = new PDO($dsn, $user, $password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));
//set the PDO error mode to exception
// return $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// echo "Connected successfully"."<br>";
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage()."<br>";
}
return $this->conn;
}
}
?>
Within the source files, also you can find an SQL file. Import that file to your MySQL database, in order to get the “test data”.
If you click over the header of a column, this column will be re-ordered, but, again, you will only receive the data that is displayed in that page. So, now we have a very low rate of memory usage. Also, no more slow pages.
by: Juan Pablo Donayre
