How to integrate datatable using ajax call source in codeigniter | Datatable | AJAX | CodeIgniter | PHP

In this article, I will demonstrate how we can display a list of the customer using Jquery datatables in CodeIgniter framework. 

Here we will use ajax response as the data source.  Let's begin

Step 1.  Create a new file under the view folder of your project, in my case I created as customer_list.php as below

customer_list.php

<div class="container-fluid">

    <!-- Page Heading -->

    <div class="mb-4">

      <?=$this->breadcrumbs->show();?>

    </div>

    <div class="row">

        <!-- Area Chart -->

        <div class="col-xl-12 col-lg-12">

            <div class="card shadow mb-4">

                <!-- Card Header - Dropdown -->

                <div class="card-header py-3 d-flex flex-row align-items-center justify-content-between">

                    <h6 class="m-0 font-weight-bold text-primary">Customer List</h6>

                    <div class="dropdown no-arrow">

                        <a class="dropdown-toggle" href="#" role="button" id="dropdownMenuLink" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">

                            <i class="fas fa-ellipsis-v fa-sm fa-fw text-gray-400"></i>

                        </a>

                    </div>

                </div>

                <!-- Card Body -->

                <div class="card-body">

                  <table class="table table-bordered" id="myTable">

                      <thead>

                          <tr>

                              <th>ID</th>

                              <th>Name</th>

                              <th>Email</th>

                              <th>Mobile</th>

                          </tr>

                      </thead>

                  </table>

                 </div>

            </div>

          </div>

      </div>

    </div>

Step 2. Now we have to load this view from the controller function. My controller name is customers.php

Customers.php

if (!defined('BASEPATH'))

    exit('No direct script access allowed');

class Customers extends MY_Controller

{

    function __construct()

    {

        parent::__construct();

        $this->load->model('customers_model');

        $this->load->library('form_validation');

        $this->load->library('breadcrumbs');

    }

    public function index()

    {

        $this->layout->set_title('Customers List');

        $this->load_datatables();

        $this->layout->add_js('../datatables/customer_table.js');

        $this->breadcrumbs->push('Dashboard', '/admin/dashboard');

        $this->breadcrumbs->push('Customer List', '/admin/customers');

        $this->layout->view_render('customers_list');
    }

    public function get_customers(){

     echo  $this->customers_model->get_customers();

    }

}

In above function $this->load_datatables(); function will load datatable js and css. you can download the datatables library file from https://datatables.net/ or you can directly load the cdn in your view.

get_customers() function will return the list of customer list. 

step 3. Now create a js file which will trigger an ajax call to retrive the customer list

customer_table.js

$(document).ready(function () {



    var customersTable = $('#myTable').dataTable({

        "bStateSave": true,

        "processing": true,

        "bPaginate": true,

        "serverSide": true,

        "bProcessing": true,

        "iDisplayLength": 10,

        "bServerSide": true,

        "sAjaxSource": BASE_URL + "customers/get_customers",

        'bPaginate': true,

        "fnServerParams": function (aoData) {

            var acolumns = this.fnSettings().aoColumns,

                columns = [];

            $.each(acolumns, function (i, item) {

                columns.push(item.data);

            })

            aoData.push({name: 'columns', value: columns});

        },

        "columns": [

            { "data": "id" },

            { "data": "name" },

            { "data": "email" },

            { "data": "contact" },


        ],

        "order": [


            [ 0, "desc" ]


        ],

        "lengthMenu": [


            [10, 25, 50, 100],


            [10, 25, 50, 100]


        ],

        "oLanguage": {


            "sLengthMenu": "_MENU_"


        },

        "fnInitComplete": function () {

            //oTable.fnAdjustColumnSizing();

        },

        'fnServerData': function (sSource, aoData, fnCallback) {

            $.ajax

            ({

              'dataType': 'json',

                'type': 'POST',

                'url': sSource,

                'data': aoData,

                'success': fnCallback

            });

        },

        "fnDrawCallback": function () {

            $('body').css('min-height', ($('#table1 tr').length * 50) + 200);

            $(window).trigger('resize');


        },

        "columnDefs": [


        ]


    });

    $('.dataTables_filter input').attr('placeholder', 'Search...');


});

In above js file 

 "sAjaxSource": BASE_URL + "customers/get_customers", line is action to call controller function get_customers() which will return customer list

step 4. create your model class as below 

Customers_model.php

if (!defined('BASEPATH'))

    exit('No direct script access allowed');

class Customers_model extends MY_Model

{

  protected $tbl;

  protected $primary_key;

  function __construct(){

    parent::__construct();

    $this->tbl="customers";

    $this->primary_key = "id";

  }

    // get data by id

    function get_by_id($id)

    {

        $this->db->where($this->id, $id);

        return $this->db->get($this->table)->row();

    }


    //For datatable

    function tot_rows(){

        $this->db->select("*");

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

        $query = $this->db->get();

        return $query->num_rows();

    }//End of tot_rows()

    function get_customers(){

      $query = "SELECT

                  tc.id,

                  tc.name,

                  tc.contact,

                  tc.email

                 FROM customers as tc

                 WHERE tc.status='1' ";


     $totalCol = $this->input->post('iColumns');

     $search = $this->input->post('sSearch');

     $columns = explode(',', $this->input->post('columns'));

     $start = $this->input->post('iDisplayStart');

     $page_length = $this->input->post('iDisplayLength');


     $query .= " AND (tc.name like '%$search%' )";

     $query .= " GROUP BY tc.id";

     $totalRecords = count($this->db->query($query)->result());


     for ($i = 0; $i < $this->input->post('iSortingCols'); $i++) {

         $sortcol = $this->input->post('iSortCol_' . $i);

         if ($this->input->post('bSortable_' . $sortcol)) {

           $query .= " ORDER BY ($columns[$sortcol])" . $this->input->post('sSortDir_' . $i);

         }

     }


     $this->db->limit($page_length, $start);


     $query .= " LIMIT $start,$page_length";

     $result = $this->db->query($query);

     $data = $result->result();

     $resData = json_encode(array(

         "aaData" => $data,

         "iTotalDisplayRecords" => $totalRecords, 

         "iTotalRecords" => $totalRecords,

         "sColumns" => $this->input->post('sColumns'),

         "sEcho" => $this->input->post('sEcho')

     ));


     return $resData;

    }

}

/* End of file Customer_model.php */