Server Side Datatable with Sorting,Searching and Pagination Using CodeIgniter

This tutorial help to create jQuery datatable listing with Sorting, Searching and Pagination using CodeIgniter 3. I am using AJAX for Sorting, Searching and Pagination records from MySQL database. CodeIgniter (CI) is very popular light-weight PHP framework that helps to create a full-fledged web application.

You can read more about CI from Official CodeIgniter site.CodeIgniter (CI) is MVC based PHP framework so that you can easily use this article logic with any PHP MVC based framework like Cakephp, zend etc.

This is the step by step tutorial help to integrate jQuery Datatables with CodeIgniter. We gone through following steps in this CodeIgniter tutorial.

  1. Create Table and database connection with mysql
  2. CI Routing
  3. Datatables listing with Sorting,Searching and Pagination using AJAX

There are following files will participate in this CodeIgniter Tutorial

  • config/routes.php:This file will use to define routes url.
  • controllers/employee.php:This file will contains all action methods.
  • models/employee_m.php:This file will contains all methods that will handle database interaction.
  • views/employee.php:This is HTML view file to define layout of table listing.
  • config/database.php:This file will use to create database connection.

Create Database and Table

We will create database and table in MySQL database server, We will be create 'test' database and 'employee' table inside this database.You need to run below SQL script in MySQL database server query window.

Select Code
1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `employee_name` varchar(255) NOT NULL COMMENT 'employee name',
  `employee_salary` double NOT NULL COMMENT 'employee salary',
  `employee_age` int(11) NOT NULL COMMENT 'employee age',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=1 ;

Simple Example of Datatable Listing with MVC based Framework

CodeIgniter-datatable

Step 1: We need to define database host, database name, database username & database password in a database config file in codeigniter. The file is located at application/config/database.php.We need to add following parameters in this file.

Select Code
1
2
3
4
'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'test',

Step 2: Added routing url in config/routes.php file to get listing on http://ci_test/employee.

Select Code
1
$route['employee'] = 'employee';

Step 3: Created employee.php file /controllers folder and added below PHP code into this file.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class employee extends CI_Controller {
    public function __construct()
    {
        parent::__construct();
        $this->load->model('employee_m', 'employee');
    }

    public function index()
    {
        $this->load->helper('url');
        $this->load->view('employee');
    }

    public function ajax_list()
    {
        //echo "hi";
        $list = $this->employee->get_employees();       
        $data = array();
        $no = $_POST['start'];
        foreach ($list as $customers) {
       // print_r($data);die;
        $no++;
        $row = array();
        $row[] = $no;
        $row[] = $customers->employee_name;
        $row[] = $customers->employee_salary;
        $row[] = $customers->employee_age;

        $data[] = $row;

        //$_POST['draw']='';
        }

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

We have get all employee records from model class on instantiation of this controller.We have defined ajax_list() method for ajax callback to get records from model based on datatable listing params like length, start and search string.

Step 4: Created employee_m.php model file into models/ folder and added below code into this file.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class employee_m extends CI_Model {

    var $table = 'employee';
    var $column_order = array(null, 'employee_name','employee_salary','employee_age'); //set column field database for datatable orderable
    var $column_search = array('employee_name','employee_salary','employee_age'); //set column field database for datatable searchable 
    var $order = array('id' => 'asc'); // default order 

    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

    private function _get_query()
    {
        $this->db->from($this->table);
        $i = 0;
        foreach ($this->column_search as $emp) // loop column 
        {
            if(isset($_POST['search']['value']) && !empty($_POST['search']['value'])){
            $_POST['search']['value'] = $_POST['search']['value'];
        } else
            $_POST['search']['value'] = '';
        if($_POST['search']['value']) // if datatable send POST for search
        {
            if($i===0) // first loop
            {
                $this->db->group_start();
                $this->db->like($emp), $_POST['search']['value']);
            }
            else
            {
                $this->db->or_like($emp), $_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)]);
        }
    }

    function get_employees()
    {
        $this->_get_query();
        if(isset($_POST['length']) && $_POST['length'] < 1) {
            $_POST['length']= '10';
        } else
        $_POST['length']= $_POST['length'];
        
        if(isset($_POST['start']) && $_POST['start'] > 1) {
            $_POST['start']= $_POST['start'];
        }
        $this->db->limit($_POST['length'], $_POST['start']);
        //print_r($_POST);die;
        $query = $this->db->get();
        return $query->result();
    }

    function count_filtered()
    {
        $this->_get_query();
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all()
    {
        $this->db->from($this->table);
        return $this->db->count_all_results();
    }

}

We have defined all methods which used for communicate MySQL database with CI to get all records from table, filter records and count records.

Step 5: Create employee.php in views/ folder, We will define all HTML layout in this file.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
<!DOCTYPE html>
<html>
    <head> 
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Simple Example of ServerSide jQuery Datatable</title>
    <link href="http://cdn.datatables.net/1.10.13/css/jquery.dataTables.min.css" rel="stylesheet">
   
    </head> 
<body>
    <div class="container">
        <h1 style="font-size:20pt">Simple Example of ServerSide jQuery Datatable</h1>
        <table id="table" class="display" cellspacing="0" width="100%">
            <thead>
                <tr>
                    <th>No</th>
                    <th>Name</th>
                    <th>Salary</th>
                    <th>Age</th>
                </tr>
            </thead>
            <tbody>
            </tbody>

            <tfoot>
                <tr>
                    <th>No</th>
                    <th>Name</th>
                    <th>Salary</th>
                    <th>Age</th>
                </tr>
            </tfoot>
        </table>
    </div>

<script src="http://ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js"></script>
<script src="http://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script>
</body>
</html>

We have added jQuery datatables library into above file, Now we will call jQuery Datatable method on above '#table' table.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
<script type="text/javascript">
var table;

$(document).ready(function() {

    //datatables
    table = $('#table').DataTable({ 

        "processing": true, //Feature control the processing indicator.
       "serverSide": true, //Feature control DataTables' servermside processing mode.
        //"order": [], //Initial no order.
        "iDisplayLength" : 10,

        // Load data for the table's content from an Ajax source
        "ajax": {
            "url": "<?php echo site_url('/employee/ajax_list')?>",
            "type": "POST",
            "dataType": "json",
            "dataSrc": function (jsonData) {
              
              
              return jsonData.data;
            }
        },
        
        //Set column definition initialisation properties.
        "columnDefs": [
        { 
            "targets": [ 0 ], //first column / numbering column
            "orderable": false, //set not orderable
        },
        ],

    });

});
</script>

above code instantiated datatable on table and configured required parameters for listing.

Conclusion

We have created database connection with mysql.We learnt about how to create controller, model and view file into CodeIgniter.We have integrated jQuery datatable listing with sorting, searching and pagination using Ajax.

You can download source code from below link.

  • ola

    very useful example ,

    I have Error message “Illegal mix of collations for operation ‘like'”
    when search by Arabic Letter , How this fix this problem
    thanks

    • may be localize file is not there, apart from them you can help form this datatable plug-inhttps://datatables.net/plug-ins/i18n/Arabic