DataTables Server-side Processing in CodeIgniter

---

In this tutorial, I will show you the easiest way to implement DataTables jQuery Plugin with remote server side processing in Codeigniter. Here I will show you how to fetch data from remote MySQL database through ajax in Codeigniter. For those who don't here about Datatables, DataTables is a table enhancing plug-in for the jQuery Javascript library that helps in adding sorting, paging and filtering abilities to plain HTML tables with minimal effort. The main goal is to enhance the accessibility of data in normal HTML tables.

Now before we start coding include Datatables CSS file and Javascript files from CDN in your view page as follows.

<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>

Now let's understand what all tasks we need to do

1. We need to limit the size of the table. (By default 10,25,50 or 100 entries)
2. Now Implement search functionality.
3. The Pagination.

All above tasks will be done in the controller and it will be explained later in this tutorial.

SO LET'S START CODING

View Page

The HTML code for the table in view page is given below.

 <div class="row">
	<div class="col-md-12">
               <table class="table table-bordered" id="posts">
                    <thead>
                           <th>Id</th>
                           <th>Title</th>
                           <th>Body</th>
                           <th>Created At</th>
                    </thead>				
               </table>
        </div>
</div>

Now the Javascript code in the view page is given below

<script>
    $(document).ready(function () {
        $('#posts').DataTable({
            "processing": true,
            "serverSide": true,
            "ajax":{
		     "url": "<?php echo base_url('home/posts') ?>",
		     "dataType": "json",
		     "type": "POST",
		     "data":{  '<?php echo $this->security->get_csrf_token_name(); ?>' : '<?php echo $this->security->get_csrf_hash(); ?>' }
		                   },
	    "columns": [
		          { "data": "id" },
		          { "data": "title" },
		          { "data": "body" },
		          { "data": "created_at" },
		       ]	 

	    });
    });
</script>

Note: Do not forget to pass CSRF Token along with ajax POST request as above if you turn on CSRF protection in CodeIgniter config file. Cross-Site Request Forgery protection is by default turned off in CodeIgniter. If you like to get maximum security in your application you can turn it on config.php file. If you enable CSRF protection don't forget to turn csrf_regenerate to false as it causes problems with back/forward navigation, multiple tabs/windows, asynchronous actions, etc.

Now before we start coding our controller we need to know that Datatable will post a lot of data to controller function during Ajax request. I found that function by using a plugin in firefox called Firebug. If you are a web developer and use ajax often then I extremely recommend Firebug.

For this tutorial, we need to look at only 6 post requests.

1. length : Number of records that the table can display in the current draw. It is expected that the number of records returned will be equal to this number unless the server has fewer records to return.
2. start : Paging first record indicator. This is the start point in the current data set (0 index based - i.e. 0 is the first record).
3. order[0]column : Column to which ordering should be applied. This is an index reference to the columnsarray of information that is also submitted to the server.
4. order[0]dir : Ordering direction for this column. It will be asc or desc to indicate ascending ordering or descending ordering, respectively.
5. search[value] : The Global search value.
6. draw : Draw counter. This is used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables (Ajax requests are asynchronous and thus can return out of sequence)

The Controller

The complete code for ajax posts function in the home controller is given below.

public function posts()
{

		$columns = array( 
                            0 =>'id', 
                            1 =>'title',
                            2=> 'body',
                            3=> 'created_at',
                            4=> 'id',
                        );

		$limit = $this->input->post('length');
        $start = $this->input->post('start');
        $order = $columns[$this->input->post('order')[0]['column']];
        $dir = $this->input->post('order')[0]['dir'];
  
        $totalData = $this->Homemodel->allposts_count();
            
        $totalFiltered = $totalData; 
            
        if(empty($this->input->post('search')['value']))
        {            
            $posts = $this->Homemodel->allposts($limit,$start,$order,$dir);
        }
        else {
            $search = $this->input->post('search')['value']; 

            $posts =  $this->Homemodel->posts_search($limit,$start,$search,$order,$dir);

            $totalFiltered = $this->Homemodel->posts_search_count($search);
        }

        $data = array();
        if(!empty($posts))
        {
            foreach ($posts as $post)
            {

                $nestedData['id'] = $post->id;
                $nestedData['title'] = $post->title;
                $nestedData['body'] = substr(strip_tags($post->body),0,50)."...";
                $nestedData['created_at'] = date('j M Y h:i a',strtotime($post->created_at));
                
                $data[] = $nestedData;

            }
        }
          
        $json_data = array(
                    "draw"            => intval($this->input->post('draw')),  
                    "recordsTotal"    => intval($totalData),  
                    "recordsFiltered" => intval($totalFiltered), 
                    "data"            => $data   
                    );
            
        echo json_encode($json_data); 
}

 

The Model

The complete code for Homemodel is given below.

<?php

class Homemodel extends CI_Model 
{
    function __construct() {
        parent::__construct(); 
        
    }

    function allposts_count()
    {   
        $query = $this
                ->db
                ->get('posts');
    
        return $query->num_rows();  

    }
    
    function allposts($limit,$start,$col,$dir)
    {   
       $query = $this
                ->db
                ->limit($limit,$start)
                ->order_by($col,$dir)
                ->get('posts');
        
        if($query->num_rows()>0)
        {
            return $query->result(); 
        }
        else
        {
            return null;
        }
        
    }
   
    function posts_search($limit,$start,$search,$col,$dir)
    {
        $query = $this
                ->db
                ->like('id',$search)
                ->or_like('title',$search)
                ->limit($limit,$start)
                ->order_by($col,$dir)
                ->get('posts');
        
       
        if($query->num_rows()>0)
        {
            return $query->result();  
        }
        else
        {
            return null;
        }
    }

    function posts_search_count($search)
    {
        $query = $this
                ->db
                ->like('id',$search)
                ->or_like('title',$search)
                ->get('posts');
    
        return $query->num_rows();
    } 
   
}

 

Now let's see the output image.

Note:  The column array is used to identify which MySQL database table column should be sorted in ascending or descending order. They are the actual names of the database columns. Their count must be equal to Datatables columns count.

I also made a tutorial on DataTables Server-side Processing in Laravel. Since CodeIgniter follow traditional MVC and does not has support for ORM the number of codes is higher than Laravel. The ORM in laravel helps it to avoid all model coding we did in CodeIgniter but CodeIgniter is much easy to understand if you are a beginner. I hardly recommend everybody switch to laravel after learning basic of CodeIgniter and basics of MVC. The laravel help us to code much faster and it reduces the number of lines required by about 50%. If anybody has any suggestions or doubts or need any help comment below.