From three days earlier I had a Good experience with my last project and I thought to share.

As usual, I make admin LTE as a backend for every project ,which contains a Datatables  jQuery plugin, although I could've changed it but I wouldn't because it is the most powerful plugin handling the admin lists it has many features some of these :

 

Datatables features

-On-the-fly filtering

-Multi-column sorting with data type detection

-Smart handling of column widths

-Display data from almost any data source

-DOM, Javascript array, Ajax file and server-side processing (PHP, C#, Perl, Ruby, AIR, Gears etc)

-Scrolling options for table viewport

-Fully internationality

-jQuery UI ThemeRoller support

-Rock solid - backed by a suite of 2900 unit tests

-A wide variety of plug-ins inc. Editor, TableTools, FixedColumns and more

-It's free!

-State saving

-Hidden columns

-Dynamic creation of tables

-Ajax auto loading of data

-Custom DOM positioning

-Single column filtering

-Alternative pagination types

-Non-destructive DOM interaction

-Sorting column(s) highlighting

-Advanced data source options

-Commercial support available

-Fully accessible for screen readers / keyboard access

-Small file size: 70K minified, 20K gzip

-Extensive plug-in support

-Sorting, type detection, API functions, pagination and filtering

-Fully themeable by CSS

-Solid documentation

-130+ pre-built examples

 

The most features I Liked, the client-side functionality, you can make a simple select statement, just add about 3 lines of JavaScript,  and let the Datatables do the magic for you.

With these only three lines you have a paging, sorting, searching, all your repetitive tasks initialized without any effort, which will make you sometimes feel lazy :).

Until now everything seems good, but what about large data?

 

Datatables large data handling

This was my problem, and I face it when client start filling the data, approximately 1000 record, lists became much slowly, the more client fill data the more Datatables list loads slowly.

 

Yes, it is the downside of using client-side functionality rather than server side, to explain this let's assume two different situations where we have 1000 products in products table and we made two decisions:

1- Use DataTables server-side functionality.

2- Use DataTables client side functionality.

In the first choice, you only selected the first 10 rows and count all rows, then send the results to DataTables to handle it, if someone clicks the number two or whatever any number in the pagination it will call the server again to load a specific data for this request by AJAX. The same thing will happen to other functionality.

DataTablesUnlike the first choice, when you depend on the client side functionality, everything rendered on the fly, you should provide DataTables with all query results, DataTables took all those results in order to make the calculation process and rendered its functionality with JavaScript, this will happen on the browser every time you load the list.

To solve this issue I decided to use them both, the client-side functionality to handle small lists with no mention data rows, on the other side use server side functionality to handle large data lists, this will give me the chance to take advantage of them and avoid their disadvantages.

Using DataTables examples

there are three examples you may use :

A- Building Datatables with client side

All you need to initiate these features to include Library and add these three lines of code

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.12/datatables.min.css"/>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.12/datatables.min.js"></script>

<script>
    $(document).ready(function() {
        $('#example').DataTable();
    } );
</script>
                   <table id="example" class="table table-striped table-bordered" cellspacing="0" width="100%">
                        <thead>
                        <tr>
                            <th class="center-block">
                                <input type="checkbox" onclick="$('input[name*=\'checkAll\']').prop('checked', this.checked);">
                            </th>
                            <th>#</th>
                            <th>Image</th>
                            <th>Title</th>
                            <th>language</th>
                            <th>Created</th>
                            <th>Options</th>
                        </tr>
                        </thead>
                        <tbody>
                   <tr><td></td></tr>

         </tbody>
        </table>

you can check a full example of using datatables client side in CodeIgniter multi languages tutorial

 

B-Building dynamic Datatables with server side

To add server side functionality we will need to use AJAX to making an instant call to the server:

   1-Load static table and fill data by ajax call. 

   2-Add ajax function to the controller to handle ajax requests.

   3-Add functions to model to handle database queries.

 

1-Load static table and fill data by ajax call like this

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.12/datatables.min.css"/>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.12/datatables.min.js"></script>

<div class="row">
    <div class="col-sm-3 col-md-2 sidebar">
        <ul class="nav nav-sidebar">
            <li class="active"><a class="btn btn-primary" href="<?= base_url() ?>news/create">Add news/post</a></li>
            <li><a class="btn btn-success" href="<?= base_url() ?>news">News list</a></li>
        </ul>
    </div>
    <div class="col-sm-9 col-sm-offset-3 col-md-10 col-md-offset-2 main">
        <h1 class="page-header">Dashboard</h1>


            <!-- Notification boxes -->
            <?php if($this->session->flashdata('success_msg')){ ?>
                <p class="alert alert-info">
                    <?php echo $this->session->flashdata('success_msg'); ?>
                </p>
            <?php } ?>
                <div class="row form-inline">
                    <div class="col-md-3" >
                        <span class="control-label">type</span>
                        <select class="form-control" id="dropdown1">
                            <option value="">all</option>
                            <option value="1">download</option>
                            <option value="2">request</option>
                        </select>
                    </div>
                </div>
                <hr>
                    <!-- Example table -->
                    <table id="example2" class="table table-bordered table-striped">
                        <thead>
                        <tr>
                            <th>#</th>
                            <th>Name</th>
                            <th>Email</th>
                            <th>Mobile</th>
                            <th>IP</th>
                            <th>Customer Type</th>
                            <th>operations</th>
                        </tr>

                        </thead>
                        <tbody>
                        </tbody>

                    </table>

        </div>
    </div>
</div>
<script type="text/javascript">

    $(document).ready(function() {
        var table =   $('#example2').DataTable({
            scrollY: '', /*'65vh'*/
            scrollCollapse: true,
            "processing": true, //Feature control the processing indicator.
            "serverSide": true, //Feature control DataTables' server-side processing mode.
            "order": [], //Initial no order.

            // Load data for the table's content from an Ajax source
            "ajax": {
                "url": "<?php echo site_url('admin_mailinglist/ajax_list')?>",
                "type": "POST"
            },

            columns: [
                {},
                {},
                {},
                {},
                {},
                /* EDIT */ {
                    mRender: function (data, type, row) {
                        if (row[5] == 1) {
                            return '<td>download<td>';
                        }else{
                            return '<td>request<td>';
                        }
                    }
                },
                /* EDIT */
                {
                    mRender: function (data, type, row) {
                        return '<a class="btn btn-sm btn-danger delete"  href="<?= base_url() ?>admin_mailinglist/ajax_delete/'+row[6]+'" title="delete"   >DELETE</a>'

                    }
                }

            ],
            "stripeClasses": ['', '']
        });


        $('#dropdown1').on('change', function () {
            if (!!this.value) {
                table.column(5).search(this.value).draw();
            } else {
                table.column(5).search(this.value).draw();
            }
        } );


    } );


</script>

2-Add ajax function to controller to handle ajax requests

    public function ajax_list()
    {

        $list = $this->mailinglist->get_datatables();
        $data = array();
        $no = $_POST['start'];
        foreach ($list as $customers) {
            $no++;
            $row = array();
            $row[] = $no;
            $row[] = $customers->MailinglistName;
            $row[] = $customers->MailinglistEmail;
            $row[] = $customers->MailinglistMobile;
            $row[] = $customers->MailinglistIp;
            $row[] = $customers->MailinglistType;
            $row[] = $customers->MailinglistId;

            //add html for action
            $data[] = $row;
        }

        $output = array(
            "draw" => $_POST['draw'],
            "recordsTotal" => $this->mailinglist->count_all(),
            "recordsFiltered" => $this->mailinglist->count_filtered(),
            "data" => $data,
        );
        //output to json format
        echo json_encode($output);
    }

3-Add functions to model to handle database queries

private function _get_datatables_query()
    {

        $this->db->from($this->_table);

        $i = 0;

        foreach ($this->column_search as $item) // loop column
        {
            if($_POST['search']['value']) // if datatable send POST for search
            {

                if($i===0) // first loop
                {
                    $this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
                    $this->db->like($item, $_POST['search']['value']);
                }
                else
                {
                    $this->db->or_like($item, $_POST['search']['value']);
                }

                if(count($this->column_search) - 1 == $i) //last loop
                    $this->db->group_end(); //close bracket
            }
            $i++;
        }

        if(isset($_POST['order'])) // here order processing
        {
            $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        }
        else if(isset($this->order))
        {
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);
        }
    }

 

C- Add custom search field to Datatables

If you saw the code above you might notice this query specific for a global search for all columns, but what if we want to make a custom column search with Datatables?

1-Add the specific field, here it is dropdown but you can choose what you need

<div class="col-md-3" >
    <span class="control-label">type</span>
    <select class="form-control" id="dropdown1">
        <option value="">all</option>
        <option value="1">download</option>
        <option value="2">request</option>
    </select>
</div>

2-Add a jQuery event on the selector which is the id dropdown1 

 

$('#dropdown1').on('change', function () {
    if (!!this.value) {
        table.column(5).search(this.value).draw();
    } else {
        table.column(5).search(this.value).draw();
    }
} );

3-Define a column as mRender function to give the ability to play with data

 /* EDIT */ {
 mRender: function (data, type, row) {
 if (row[5] == 1) {
 return '<td>download<td>';
 }else{
 return '<td>request<td>';
 }
 }
 }

function _get_custom_field()
{
    if(isset($_POST['columns'][5]['search']['value']) and $_POST['columns'][5]['search']['value'] !=''){
        $this->db->where('MailinglistType',$_POST['columns'][5]['search']['value']);
    }
}

 

Download Full Code