Datatables:Client and server-side processing(PHP,MySQL,Ajax)

Written by Charaf JRA

jQuery DataTables is an excellent jQuery plugin to transform your HTML table from a classical Table to a DataGridView component that includes many features (Pagination, instant search by single or multiple columns, Sorting and ordering Data). Because of the flexibility of its API, developers can build multiple extensions to suit their needs in addition to those that already exist (Responsive, Autofill, fixed Columns…).
This plugin is highly themeable and designers can fully customize its look and feel, it is already adapted to many CSS Frameworks such as Bootstrap3 and foundation.
jQuery DataTables can be populated using different kinds of data sources (HTML DOM, JavaScript Objects or from any database using server-side processing).
This tutorial will cover two datasources (HTML DOM and server side processing using MySQL as a database, PHP as a programming language, and Ajax to load data asynchronously).
You will transform your table from a simple table like this:
Before jquery datatables To a rich component:
after jquery datatablesKeep in mind that this theme is the default one and if you want to apply styling from a third party(Bootstrap, Zurb Foundation…), you have to include the corresponding version of Datatables wich adds custom classes to the table, in addition to assets of CSS frameworks.
Before we choose which data source we use, let’s integrate Datatables jQuery plugin to our page.
I will use latest version of jquery, latest CSS and JS assets of the plugin (until date of this article) from its official CDN, our head markup should include:

<link rel="stylesheet" href="//cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css"/>
<script src="https://code.jquery.com/jquery-3.1.0.min.js"></script>
<script src="//cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>

Data Source 1: HTML DOM(Client Side) – Datatables

This is the easiest way to create a GridView with just a few jQuery lines.
First we create a table in HTML like we usually do, containing sample data.The web browser will parse this HTML to DOM nodes (Document Objects Model) and will use these DOM objects to create the Grid using Javascript.
PS: I added a class  to apply the default styling and a width attribute to give the table all the space available.
For responsive design, we will talk later about the Responsive extension.
Our HTML table will look like this:

<table id="datatable" class="display" cellpadding="1" cellspacing="1"  width="100%">
<thead>
<tr>
    <th>Name</th>
    <th>Phone</th>
    <th>City</th>
    <th>Email</th>
</tr>
</thead>
<tbody>
    <tr>
        <td>Rooney Richard</td>
        <td>1-996-276-4803</td>
        <td>Ingolstadt</td>
        <td>erat@augue.net</td>
    </tr>
    <tr>
        <td>Darius Sheppard</td>
        <td>1-717-252-8279</td>
        <td>Davangere</td>
        <td>sem@dictumplacerataugue.edu</td>
    </tr>
    <tr>
        <td>Uriel Burris</td>
        <td>1-538-931-3803</td>
        <td>Ussassai</td>
        <td>a.malesuada.id@vestibulum.com</td>
    </tr>
</tbody>
<tfoot>
<tr>
    <th>Name</th>
    <th>Phone</th>
    <th>City</th>
    <th>Email</th>
</tr>
</tfoot>
</table>

Then once DOM is ready, we run jQuery Datatables plugin:

$(document).ready(function() {
    $(‘#datatable’).DataTable();
} );

View Demo Download Source

This solution is very easy and works well with few rows, but once we have large tables containing thousands of data, the web browser can lag and page loading will become very slow, that’s why we use Server-side processing.

Data Source 2: Server-side processing using PHP, MySQL database, and Ajax

jQuery datatables server side processing

As we said, our Datasource will be a table from MySQL Database.Let’s create a sample table “users”:

CREATE TABLE users (
  id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(255) default NULL,
  phone varchar(100) default NULL,
  email varchar(255) default NULL,
  country varchar(100) default NULL,
  zip varchar(10) default NULL,
  PRIMARY KEY (id)
) AUTO_INCREMENT=1;

I will fill this table with dummy data (full SQL Script in the GIT Repository of this demo) or you can generate your own customized sample data by using this excellent generator:
You can export your SQL data in addition to other formats:

  • CSV
  • Excel
  • HTML
  • JSON
  • LDIF
  • Arrays for different programming language
  • XML

Like in DOM DataSource, we will define the skeleton of our table:

<table cellpadding="1" cellspacing="1" id="users" class="display" width="100%">
    <thead>
    <tr>
        <th>ID</th>
        <th>NAME</th>
        <th>PHONE</th>
        <th>Email</th>
        <th>Country</th>
        <th>ZIP Code</th>
    </tr>
    </thead>
    <tfoot>
    <tr>
        <th>ID</th>
        <th>NAME</th>
        <th>PHONE</th>
        <th>Email</th>
        <th>Country</th>
        <th>ZIP Code</th>
    </tr>
    </tfoot>
</table>

Running the plugin:
We enabled server side processing and we specified URL of PHP script that we will interact with using Ajax.
Finally, we forced POST Method for Ajax requests to send variable via Headers, by default this plugin uses GET method.
I defined columns too because response of my script will be a JSON Encoded associative array and its keys are those defined below:

$(document).ready(function () {
    $('#users').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": {
            url: 'demo2.php',
            type: 'POST'
        },
        "columns": [
            {"data": "id"},
            {"data": "name"},
            {"data": "phone"},
            {"data": "email"},
            {"data": "country"},
            {"data": "zip"}
        ],
    });
});

In server side, we will create an SQL Query for each ajax request coming from our component:

  • Listing data taking into consideration pagination and number of entries
  • Ordering data by a specific column
  • Filtering Data using the search field on keyUp

To handle all these Ajax requests, we will need some very useful parameters sent in the headers of our POST requests:

headers of a POST request - Datatables Some of the parameters we will use:

$draw = $_POST["draw"];//counter used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables
   $orderByColumnIndex  = $_POST['order'][0]['column'];// index of the sorting column (0 index based - i.e. 0 is the first record)
   $orderBy = $_POST['columns'][$orderByColumnIndex]['data'];//Get name of the sorting column from its index
   $orderType = $_POST['order'][0]['dir']; // ASC or DESC
   $start  = $_POST["start"];//Paging first record indicator.
   $length = $_POST['length'];//Number of records that the table can display in the current draw

You can find a full explanation of each parameter in Datatables’s official documentation.

I defined a function for fetching and formatting data coming from users table, this function takes SQL String as parameter and returns our formatted array:

function getData($sql){
    global $connection ;//we use connection already opened
    $query = mysqli_query($connection, $sql) OR DIE ("Can't get Data from DB , check your SQL Query " );
    $data = array();
    foreach ($query as $row ) {
        $data[] = $row ;
    }
    return $data;
}

The returned result of this function will be like this:

print_r(getData("SELECT * FROM users"));//Result
Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Callum Wall
            [phone] => 1-476-763-3328
            [email] => enim@Craspellentesque.edu
            [country] => Palau
            [zip] => WD13 2PT
        )

    [1] => Array
        (
            [id] => 2
            [name] => Emmanuel Petersen
            [phone] => 1-301-573-9241
            [email] => elit.Etiam.laoreet@necmetusfacilisis.edu
            [country] => Lebanon
            [zip] => 29657
        )
)

For pagination and listing, we will run this code using POST variables described above:

$sql = sprintf("SELECT * FROM %s ORDER BY %s %s limit %d , %d ", MyTable ,$orderBy,$orderType ,$start , $length);
$data = getData($sql);

For filtered data using search input, we need to check if the searched value exists in table columns.To do so, I will add a WHERE clause created dynamically:

     for($i=0 ; $i<count($_POST['columns']);$i++){
         $column = $_POST['columns'][$i]['data'];//we get the name of each column using its index from POST request
         $where[]="$column like '%".$_POST['search']['value']."%'";
     }
     $where = "WHERE ".implode(" OR " , $where);// id like '%searchValue%' or name like '%searchValue%' ....

Finally, we need to create our Response: A JSON encoded associative array containing the same draw parameter that we get from client side(Sync) in addition to data from MySQL table and the number of returned items.

$response = array(
    "draw" => intval($draw),
    "recordsTotal" => $recordsTotal,
    "recordsFiltered" => $recordsFiltered,
    "data" => $data
);

echo json_encode($response);

View Demo Download Source

If you have any question or feedback about this tutorial, feel free to add a detailed comment.

About the author

Charaf JRA

I'm a Code enthusiast , Blogger, Geek and Software Engineer. I created this blog to share my inspirations and my new experiences.If you like to contact me , use the contact form above or follow me on twitter

%d bloggers like this: