Ajax Pagination with CodeIgniter & jqGrid

Implement Server Side Ajax Pagination with CodeIgniter & jqGrid jquery plugin. Records will fetch from server without page load progressively.

Well, programmer like me and you are searching for good code which can do the pagination. There are lot of Javascript grid plugins are available on the internet who can achieve the pagination, searching, sorting in one go. Some of them are as follows.

Javascript Plugins:

DataTables, Flexigrid, jqGrid

PHP+jQuery plugin:

Grocery CRRUD(GC) (CodeIgniter, DataTables/Flexigrid)

Basically, what these plugins do, they load complete data in browser first then perform the operations on that data like pagination, searching, sorting etc.

But we want only some records per page have to be fetched in one go. So we have write a customize code to do that. Well, that thing is already achieved by CodeIgniter GC, but its quite difficult to load the records from 2 or more tables with join in GC.

I gone through jqGrid documentation and I customize the ajax call of jqGrid which I want. Below I am sharing the code which is developed using MVC CodeIgniter and jQuery base plugin jqGrid (The magical grid). One thing I want to share here, that for the UI purpose, jqGrid uses jQuery-UI. (I used REDMOND theme).

Table structure:

1. customer { customer_id, customer_name, state_id, dist_id, city_id, approval_status}
2. state {state_id, state_name}
3. district {dist_id, dist_name}
4. city {city_id, city_name}

Controller code:

* This function is used to load the view
public function viewCustomers()
  $this->load->view('header'); // your header view
  $this->load->view('customers'); // your main view where you write html code
  $this->load->view('footer'); // your footer view

This function which called from jqGrid and it fetch the real data from database page by page, search, sort, no_of_records_per_page etc.
public function getCustomers()
  $search_field = $this->input->get('searchField'); // search field name
  $search_string = $this->input->get('searchString'); // search string
  $page = $this->input->get('page'); //page number
  $limit = $this->input->get('rows'); // number of rows fetch per page
  $sidx = $this->input->get('sidx'); // field name which you want to sort
  $sord = $this->input->get('sord'); // field data which you want to soft
  if(!$sidx) { $sidx = 1; } // if its empty set to 1
  $count = $this->admin_model->getAllCustomerListCount($search_field, $search_string);
  $total_pages = 0;
  if($count > 0) { $total_pages = ceil($count/$limit); }
  if($page > $total_pages) { $page = $total_pages; }
  $start = ($limit * $page) - $limit;
  $data = array('page'=>$page,
    'rows'=>$this->admin_model->getAllCustomersList($sidx, $sord, $start, $limit, $search_field, $search_string)
  header('Content-Type: application/x-json; charset=utf-8');

Model code:

* This function used to get all customers list count
public function getAllCustomerListCount($search_field, $search_string)
  $this->db->select('BaseTbl.customer_id, BaseTbl.customer_name,
tbl_state.state_name, tbl_dist.dist_name, tbl_city.city_name,
  $this->db->from('tbl_customer as BaseTbl');
  $this->db->join('tbl_state','tbl_state.state_id = BaseTbl.state_id','left');
  $this->db->join('tbl_dist','tbl_dist.dist_id = BaseTbl.dist_id','left');
  $this->db->join('tbl_city','tbl_city.city_id = BaseTbl.city_id','left');
  if($search_field == 'customer_id') { $this->db->like('BaseTbl.customer_id', $search_string); }
  if($search_field == 'customer_name') { $this->db->like('BaseTbl.customer_name', $search_string); }
  $query = $this->db->get();
  return count($query->result());

* This function used to get all customers list,
* $sidx : This is the field name which you want to sort
* $sord : This is the order of sorting ASC or DESC
* $start : This is starting record of the page
* $limit : This is limit how many records per page 10 or 20 or 30 as you wish
* $search_field : The searching field title, where you want to search
* $search_string : The searching input string
public function getAllCustomersList($sidx, $sord, $start, $limit, $search_field, $search_string)
  $this->db->select('BaseTbl.customer_id, BaseTbl.customer_name,
tbl_state.state_name, tbl_dist.dist_name, tbl_city.city_name,
  $this->db->from('tbl_customer as BaseTbl');
  if($sidx == 'customer_id') { $this->db->order_by('BaseTbl.customer_id', $sord); }
  else if($sidx == 'customer_name') { $this->db->order_by('BaseTbl.customer_name', $sord); }
  else if($sidx == 'mobile_no') { $this->db->order_by('BaseTbl.mobile_no', $sord); }
  else if($sidx == 'dist_name') { $this->db->order_by('tbl_dist.dist_name', $sord); }
  else if($sidx == 'state_name') { $this->db->order_by('tbl_state.state_name', $sord); }
  else if($sidx == 'relegion') { $this->db->order_by('tbl_relegion.relegion', $sord); }
  else if($sidx == 'cast') { $this->db->order_by('tbl_cast.cast', $sord); }
  else if($sidx == 'username') { $this->db->order_by('admin_users.username', $sord); }
  else { $this->db->order_by('BaseTbl.approval_status', $sord); }
  if($search_field == 'customer_id') { $this->db->like('BaseTbl.customer_id', $search_string); }
  if($search_field == 'customer_name') { $this->db->like('BaseTbl.customer_name', $search_string); }
  $this->db->limit($limit, $start);
  $query = $this->db->get();
  return $query->result();

HTML code:

<table id="custRecords"></table>
<div id="pager"></div>

Javascript code:

<script type="text/javascript">
var baseurl = "<?php echo base_url(); ?>";
url : baseurl + "getCustomers",
postData : { /* from here you can give the custom input to the controller */ },
datatype : "json",
height : "auto",
width : "auto",
colNames : ['Id','Name','State','District','Tehsil','Approval Status'];,
colModel : [
{name: 'customer_id', index:'customer_id', width: 55, key:true},
{name: 'customer_name', index:'customer_name', width:250},
{name: 'state_name', index:'state_name',width:100},
{name: 'dist_name', index:'dist_name',width:100},
{name: 'city_name', index:'city_name',width:100},
{name: 'approval_status', index:'approval_status', formatter:statusFormatter,width:100}
jsonReader:{ repeatitems : false, id: "0" },
loadonce : false,
rowNum : 10,
rowList : [10, 20, 50, 100],
pager : jQuery('#pager'),
viewrecords : true,
multiselect : true,
caption: "All Customers",
gridview: true,
autoencode : true
}).navGrid('#pager1', { edit: false, add: false, del: false, refresh: true });

/* This function is used to show custom graphical status, ie. I show it using colors and bootstrap lable class
cellvalue : Its value of that cell on which you can process
option : option contain whole information like key
rowObject : it having the complete row information
You can log and see their elements using console.log(rowObject) etc. in firebug
function statusFormatter(cellvalue, options, rowObject)
if(cellvalue == 1){return "<span class='label label-success'>Yes</span>";}
else{return "<span class='label label-danger'>No</span>"; }

Now you grid is ready just execute the page :

Go to the firebug or any console you have, you notice an ajax call. Just analyse the post parameters and response in the console. You notice that only 10 records are coming at a time. And when you change the number of pages from the grid to 20; it comes with only 20 records per page. No overhead.

You can find out the full demo of the grid on the following link. And I believe that after reading and implementing the grid, you come to know that why its called as The Magical Grid.