Server Side Jquery DataTable

|
| By Webner

Server Side Scripting is – when the user’s request is served by the script on the server to provide customized or dynamic data. Server-side processing on DataTables is that when all the DataTable functions like searching, ordering, pagination etc. are controlled by Server Side. This is used when we have a large database and need to serve thousands of records to the user faster so that it wouldn’t take a long time as in Client Side Scripting.

Below is the example of DataTable javascript for server side:
JSON Data:

{
 "draw": 1,
 "recordsTotal": 20,
 "recordsFiltered":20,
 "data": [
   [
    "1",
    "Parminder",
    "xyz.abc@xyz.com",
    "User",
   ],
   [
    "2",
    "Kiranpreet",
    "pqr@xyz.com",
    "Admin",  
   ],
}
$('#usersTable').DataTable({
    processing: true, 
    serverSide: true, //enables Server Side Scripting
    ajax:{
    url: ‘../users/users.php’,  
    type:'POST',
    },    		 
    Columns:[
    {“data”:”id”}, //displays id in the first column
    {"data":"name"},    //displays name in the second column
    { "data":"email",	 //displays email in the anchor tag in the td
    render: function ( data, type,row) {
    email = row.email
    email = ''+email+'';
    return email;
     }
     },
    { "data": "user_type" }, //displays user_type in the last column
     ],
    });

HTML for this is:

<table id="usersTable">              
 <thead>
  <tr>     
   <th>Id</th>   
   <th>Name</th>
   <th>Email</th>
   <th>Role</th>
  </tr>
 </thead>   
  <tbody>   
  </tbody>
</table>

In above example, on enabling Server Side processing DataTable method will post all the variables like start index, length (limit in SQL Query), Search String, Order of Columns etc in the Ajax call to PHP method. For example, few of the variables we get by Post in ajax call are:

$startIndex = $_POST[‘start’] //Start Index
$length = $_POST[‘length’] //Limit of Records to fetch
$searchString = $_POST['search']['value'] // String value to search in database
$orderByColumn = $_POST['order'][0]['column'] //Order of the Column
$orderByDirection = $_POST['order'][0]['dir'] //Direction of the Column Asc or Desc

SQL Query which uses variables and gets records of users:

$query = "SELECT id,name,email,user_type from users";
if($searchString != null){
$query .= " where (name like '%$searchString%' OR email like '%$searchString%')";
}
$query .= " ORDER BY $orderByColumn $orderByDirection LIMIT $startIndex , $length";

 

Leave a Reply

Your email address will not be published. Required fields are marked *