Server Side Datatable with Sorting, Searching and Pagination Using CodeIgniter – I

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 a 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. In this CodeIgniter tutorial, we went through the following steps.

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 the routes url.
• controllers/employee.php: This file will contain all action methods.
• models/employee_m.php: This file will contain all methods that will handle database interaction.
• views/employee.php: This is an HTML view file to define the layout of the table listing.
• config/database.php: This file will use to create a database connection.

Create Database and Table

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

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

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.

	'hostname' => 'localhost',
	'username' => 'root',
	'password' => '',
	'database' => 'test',

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

$route['employee'] = 'employee';

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

<!--?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 gotten all employee records from the model class on the 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 the models/ folder and added below code into this file.

<!--?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 that used to communicating with the MySQL database with CI to get all records from table, filter records and count records.

Please read Part II – jQuery Datatable with Sorting, Searching and Pagination tutorial for view integration with controller action method.