Adding jQuery DataTable to your page part 2

AJAX loaded dataTable

Now let’s think for a scenario where we need to show a list with 10000 row. If we try to retrieve all of these data at a time, what will happen? It will take too much time & will make your page unresponsive. Visitors will have no clue what to do & sure he will not like to visit your page for the next time. Here’s come DataTable with a very handy solution for you. You can divide your list in different segments & load it partially with pagination. Partial data will be available within a short time. dataTable will load each page’s data using a AJAX call.

Let’s check how can we implement it.

  1. At first, We need to include necessary files like part1. I am skipping discussion for this section, we have already discussed about it in previous section. After that, we need to rewrite our table like below code and save it as index.php
<table id="example" cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered"  width="100%">
    <thead>
        <tr>
            <!--  you can set column pririty using data-priority tag, lowest number will get highest priority-->
            <th>Product Code</th> 
            <th>Order Number</th>
            <th>Ordered quantity</th>
            <th>price Each</th>
            <th>Order LineNumber</th>
        </tr>
    </thead>
</table>

The interesting part is we have not added any table body with above code. Then where we will find it? dataTable will automatically prepare that portion for your and add it after thead.

2. We will use another two files for this example – a) ssp library for server side processing b) datatable.php for setting our database connection info, all other initial set up for retrieving data from database. we will discuss about it in more details from next point. Now we need to define our column info & will call dataTable for server side processing using jquery. Add below code inside script tag

var columns = [     
              {"db": "productCode", "dt": 0, "field": "productCode"},
              {"db": "orderNumber", "dt": 1, "field": "orderNumber"},
              {"db": "quantityOrdered", "dt": 2, "field": "quantityOrdered"},
              {"db": "priceEach", "dt": 3, "field": "priceEach"},
              {"db": "orderLineNumber", "dt": 4, "field": "orderLineNumber"}         
              ];

$(document).ready(function() {
    var tableElement = $('#example');
    var exampleTable = tableElement.DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": {
            "type": "POST",
            "url": "dataTable.php",
            "data": {"table": "orderdetails", "primary_key": "id", "columns": columns}
        }
    });
});
  • using columns variable we are defining columns we need to retrieve(db describes database tables field name, dt defines column’s serial, column name available in client side after retrieving data)
  • In first part, we were calling dataTable in a simpler way. But we can pass different value when calling it. Suppose, now we need to define that it’s a server side calling. For this reason, we have added “processing”: true,”serverSide”: true. Next we have described information about our AJAX call(it should be a POST type call, here we can use GET too. url will be dataTable.php & lastly we will send few basic info using data – table name, primary key of our table & which columns we want to retrieve)

3. At last, let’s check dataTable.php page & ssp library. Create a blank page, save it as datatable.php & insert below code inside it.

/*
 * DataTables example server-side processing script.
 *
 * Please note that this script is intentionally extremely simply to show how
 * server-side processing can be implemented, and probably shouldn't be used as
 * the basis for a large complex system. It is suitable for simple use cases as
 * for learning.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */

    // DB table to use
    $table = $_POST['table'];
     
    // Table's primary key
    $primaryKey = $_POST['primary_key'];

    // Array of database columns which should be read and sent back to DataTables.
    // The `db` parameter represents the column name in the database, while the `dt`
    // parameter represents the DataTables column identifier. In this case simple
    // indexes
    
    $columns = $_POST['columns'];

    //$columns['id'] = $columns[`artwork`.`artwork_id`];
   
    // SQL server connection information

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

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * If you just want to use the basic configuration for DataTables with PHP
     * server-side, there is no need to edit below this line.
     */
    require('ssp.class.php' );
    echo json_encode(SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns));

 

We have no need to change that much in these pages right now. Just update your database connection info inside dataTable.php & it will do the rest of the work for you. Click here to download ssp library. We have used this database for this example.

Finally you will get a output like this

DataTable

DataTable

Like to work with different type of web based application, have completed my graduation from RUET & I am a Zend Certified PHP Engineer and also a Certified Scrum Master, like agile methodologies for development. I am working as a Web Developer for 4+ years, specially experienced in Codeigniter and Laravel.
Print Friendly

Leave a Reply

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


*