JQUERY DATATABLE WITH CODEIGNITER USING SERVER SIDE PROCESSING

  1. Today
  2. Friday Dec 06, 2019

image description

Jquery DataTable with CodeIgniter Using Server Side Processing

Hello guys, in this tutorial we will be integrating famous Jquery DataTable plugin with our CodeIgniter application. Before we begun I assume that you already have all the things setup, because the focus of this tutorial is on how to integrate the DataTable with server side processing. I will not go as why you need to use server side processing and other stuff because if you are here its more than likely you already knew what you needed :).


First of all, I highly recommend using the official documentation found on DataTable website, I found the following links to be super helpful :



Why This Tutorial ?

Most of the tutorials found on the internet are very helpful but they seems to be missing some parts like the sorting and searching and most of them don't show how to add columns for actions like edit and delete etc. There are sections how to add custom columns in DataTable documentation, but in real world scenarios we use the back-end to generate the actions button like edit, delete etc.


So this tutorial is based on the back-end side, like how to handle sorting and searching (once you made the datatable serverside all the actions are to be handled at the server side), and you will see you don't need to do any changes in edit, delete buttons


Here is what our final result will look like, i have not use any of the graphics, only added lots of data from the sample database found at mysql officical website.


You can view the video tutorial through the below links. I have divided the video tutorial in 3 parts so it will be easy to follow :

Part-01 : Integrating DataTables

Part-02 : Apply Sorting functionality

Part-03 : Apply Search/Filter functionality.



As you can see in the image above its a lot of data .Ok enough of the talking Let's begin


View

<!doctype html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title>DataTable With ServerSide</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.css">
  </head>
  <body>
      <div class="container">
          <div class="row">
              <div class="col-12">
                <table class="table" id="datatable">
                    <thead>
                        <tr>
                            <th>#</th>
                            <th>DOB</th>
                            <th>First Name</th>
                            <th>Last Name</th>
                            <th>Gender</th>
                            <th>Date of Hire</th>
                            <th>Actions</th> 
                          </tr>
                      </thead>
                      <tbody>
                      </tbody>
                  </table>                
                </div>
            </div>
        </div>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
        <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script>
      </body>
    </html>

I used the bootstrap starter template and added cdn version of the datatable you can find all the cdn at their respective websites. I used bootstrap only to make the table center and use the "table" class. The markup is straight forward, we added a table with id of datatable what we will use in our jquery


Warning: If you want to use bootstrap startup template make sure you use jquery full version not the slim version, as the slim version don't have ajax functionality .


JS File

$(document).ready(function(e){
  var base_url = "<?php echo base_url();?>"; // You can use full url here but I prefer like this
  $('#datatable').DataTable({
     "pageLength" : 10,
     "serverSide": true,
     "order": [[0, "asc" ]],
     "ajax":{
              url :  base_url+'yourcontroller/yourMethod',
              type : 'POST'
            },
  }); // End of DataTable
}); // End Document Ready Function

Please note that "yourcontroller" is the name of your controller and "yourMethod" is the name of the function in your controller file, for the sake of this example I am using "Datatables" as controller and "showEmployees" as the method inside controller.


So far so good, understand some background of what we are going to do in our controller. As mentioned before once you make the "serverSide" to "true" datatable automatically send many parameters with each action performed on the table (e.g sorting searching). To see the list of all the parameter visit this official link . For this tutorial we will be focusing on the following parameters only :

  • draw [an integer value used to ensure that the returned data is in sequence]
  • start [an integer value used for pagination indicator]
  • length [ Number of records that the table can display in the current draw.]
  • order [ an array of order[i][column] and order[i][dir] used to for sorting]
  • search [search term that is typed in the search field]

Controller

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Datatables extends CI_Controller {
    public function index()
    {
        $this->load->view('datatable');
    }
    public function showEmployees()
    {
        $draw = intval($this->input->post("draw"));
        $start = intval($this->input->post("start"));
        $length = intval($this->input->post("length"));
        $order = $this->input->post("order");
        $search= $this->input->post("search");
        $search = $search['value'];
        $col = 0;
        $dir = "";
        if(!empty($order))
        {
            foreach($order as $o)
            {
                $col = $o['column'];
                $dir= $o['dir'];
            }
        }

        if($dir != "asc" && $dir != "desc")
        {
            $dir = "desc";
        }
        $valid_columns = array(
            0=>'emp_no',
            1=>'birth_date',
            2=>'first_name',
            3=>'last_name',
            4=>'gender',
            5=>'hire_date',
        );
        if(!isset($valid_columns[$col]))
        {
            $order = null;
        }
        else
        {
            $order = $valid_columns[$col];
        }
        if($order !=null)
        {
            $this->db->order_by($order, $dir);
        }
        
        if(!empty($search))
        {
            $x=0;
            foreach($valid_columns as $sterm)
            {
                if($x==0)
                {
                    $this->db->like($sterm,$search);
                }
                else
                {
                    $this->db->or_like($sterm,$search);
                }
                $x++;
            }                 
        }
        $this->db->limit($length,$start);
        $employees = $this->db->get("employees");
        $data = array();
        foreach($employees->result() as $rows)
        {

            $data[]= array(
                $rows->emp_no,
                $rows->birth_date,
                $rows->first_name,
                $rows->last_name,
                $rows->gender,
                $rows->hire_date,
                '<a href="#" class="btn btn-warning mr-1">Edit</a>
                 <a href="#" class="btn btn-danger mr-1">Delete</a>'
            );     
        }
        $total_employees = $this->totalEmployees();
        $output = array(
            "draw" => $draw,
            "recordsTotal" => $total_employees,
            "recordsFiltered" => $total_employees,
            "data" => $data
        );
        echo json_encode($output);
        exit();
    }
    public function totalEmployees()
    {
        $query = $this->db->select("COUNT(*) as num")->get("employees");
        $result = $query->row();
        if(isset($result)) return $result->num;
        return 0;
    }
}

Woofofff what is it you may ask :), just relax I will explain the above code step by step and we will all be just fine.

If you are lost you can check out my video explanation here


The method "showEmployees()" is of our interest


$draw   = intval($this->input->post("draw"));
$start  = intval($this->input->post("start"));
$length = intval($this->input->post("length"));
$order  = $this->input->post("order");
$search = $this->input->post("search");
$search = $search['value'];

These are the parameters sent by datatable on each request. we use the intval() function to get the integer value of the parameters. Also we have "order" which is an array and search term which is also an array so we just broke it into the single search term


$col = 0;
$dir = "";
if(!empty($order))
{
  foreach($order as $o)
  {
     $col = $o['column'];
     $dir= $o['dir'];
  }
}

We then two variables $col and $dir which are initially set to zero and blank respectively.

The next part is to check if the user has made the sorting and which column that sorting is made on, as mentioned earlier order is an array so we used that array and save the values in $col and $dir.


if($dir != "asc" && $dir != "desc")
{
   $dir = "desc";
}

If no order is provided by the user,it will be descending order means last added item will be shown in the first row, it is optional you are free to make it as ascending order by replacing "desc" to "asc".


$valid_columns = array(
            0=>'emp_no',
            1=>'birth_date',
            2=>'first_name',
            3=>'last_name',
            4=>'gender',
            5=>'hire_date',
);


Now came the tricky part, look at column names in our database and what we added to our html.


HTML Table Column Names



Database Column Names


Now let me explain what is happening here, datatable works on column numbers instead of names so first column will be indexed 0 , second 1 , and so on as explain in the image above. So we connected both the columns with the index of datable to the column name of our database , 0=>'emp_no' here "0" is the column index of datatable and "emp_no" is the name of the column in database. Why we do it you may ask, the answer is simply to perform the sorting and searching functionality that I will explain to you in a moment.


if(!isset($valid_columns[$col]))
{
  $ordr = null;
}
else
{
  $ordr = $valid_columns[$col];
}
if($ordr !=null)
{
  $this->db->order_by($ordr, $dir);
}

Here firstly we check if the valid columns are set, if not then the variable "$ordr" will be null else $ordr will be the column name, this is some how confusing right?, let me dig a little deeper. For this we need to go through the ORDER BY query of mysql, the format of the query is

SELECT * FROM `table_name` ORDER BY `column_name` ASC | DESC

The problem here is that mysql takes column name for sorting, while datatables uses integer numbers as columns, So we did all this pain to convert column number to column names, for instance user sort column "First Name" which has index of 2 and in our $valid_columns array it is associated with "first_name" column of database, I think now you get the idea.


Finally, in the last line we checked that if $ordr is not null append the order_by clause.


This is all for handling the sorting. If you still don't get it check out my video explanation here


if(!empty($search))
{
  $x=0;
  foreach($valid_columns as $sterm)
  {
     if($x==0)
     {
        $this->db->like($sterm,$search);
     }
     else
     {
        $this->db->or_like($sterm,$search);
     }
     $x++;
  }                 
}

Now we are going to do something about the search. First of all we will check that if search is empty or not? if not empty we created a variable "$x" and assign its value to zero. Then we checked through our columns for searching, here we need to use "like" and "or_like" clauses, but if we want to use "or_like" we have to use "like" first, that is why we use that $x, clear enough?. So if $x is 0 append the query "like" else append "or_like", finally we incremented variable "$x" so "$x" cannot be 0 again. If you still find it difficult check our my video tutorial here


$this->db->limit($length,$start);
$employees = $this->db->get("employees");
$data = array();
foreach($employees->result() as $rows)
{
  $data[]= array(
       $rows->emp_no,
       $rows->birth_date,
       $rows->first_name,
       $rows->last_name,
       $rows->gender,
       $rows->hire_date,
       '<a href="#" class="btn btn-warning mr-1">Edit</a>
        <a href="#" class="btn btn-danger mr-1">Delete</a>'
  );     
}

This is the most easy part all we are doing is using our query to fetch data from the database and append it in array of $data.


$total_employees = $this->totalEmployees();
$output = array(
   "draw" => $draw,
   "recordsTotal" => $total_employees,
   "recordsFiltered" => $total_employees,
   "data" => $data
);
echo json_encode($output);
exit();

Finally we are counting total employees , for this we have used a method "totalEmployees()" which simply count all the records inside employees table in database, then we created an array of "$output" and store all the data that needed to be send back, and finally, we encoded all this output with json_encode function.


That's it we successfully integrated our server side processing in datatables using CodeIgniter.


Load WordPress Sites in as fast as 37ms!
image description

IFTIKHAR

Iftikhar is a full stack developer with a passion of data mining, bot making and ethical hacking. He is working as a project manager at mazoon software company located in oman.

Comments (8)

  • image description

    xzzd

    nice

  • image description

    Somtoohack

    Thanks for this, My dataTable is now running

  • image description

    MLADEN

    hey,,,can i hire you to make my datatable with server side processing?

    • image description

      Iftikhar

      • 17 Nov 2019

      Yes Please send me email to iftikhar.romtech@gmail.com

  • image description

    John

    Very useful and detailed guide, U save my day

    • image description

      Iftikhar

      • 13 Nov 2019

      Glad to help John, Thank you !

  • image description

    John

    Very useful and detailed guide, U save my day

  • image description

    nowar

    hello! how can i add "all" in entries?

    • image description

      Iftikhar

      • 02 Nov 2019

      Nowar, My answer ! you should not make 'All' the records to show at once, since depending on the host and the server your script may break. So try $('#example').dataTable( { "pageLength": 50 } ); and check where you script break, and set the number smaller than that, so your script will never break, But if you need it anyhow, you can count all the records from the database and set that number to show all records, but this is highly discouraged and not a recommended solution. Hope that might help you Thanks!

  • image description

    Jovylle

    Very useful and practical. Good Job!

    • image description

      IFTIKHAR

      • 31 Oct 2019

      Thanks !

  • image description

    Rainkv

    Very good article, can you let me download this article to let me research for my project? Thank you very much

    • image description

      Iftikhar

      • 10 Sep 2019

      Thanks you Rainky, I will update the article with the download link to the source.

Post Your Comments