Datatables ajax bootstrap pagination with codeigniter

If you want to implement datatables with ajax using codeigniter and you can also use code if you are using core php stuff in your application.

1. You need to include required jQuery for datatables , ajax processing.

<script src="<?php echo base_url(); ?>assets/js/jquery-1.11.0.min.js"></script>
<script src="<?php echo base_url(); ?>assets/js/jquery.dataTables.min.js"></script>
<script src="<?php echo base_url(); ?>assets/js/dataTables.bootstrap.js"></script>
<script src="<?php echo base_url(); ?>assets/js/datatables/responsive/js/datatables.responsive.js">
</script>
<script src="<?php echo base_url(); ?>assets/js/datatables/jquery.dataTables.columnFilter.js">
</script>
        

2. Your html source file or you can say your view file will goes like below.

<div class="main-content">
<div class="row"><h2>Brokers Listing</h2>
<div class="panel panel-primary" data-collapsed="0">
<div class="panel-body">
<table class="table table-bordered table-striped datatable" id="broker_listing">
<thead><tr>
<th class="no-sort">#</th>
<th>Broker Name</th>
<th>Email</th>
<th>Created Date</th>
<th>Status</th>
<th class="no-sort">Actions</th>
</tr></thead><tbody></tbody>
</table>
</div>
</div>
        

<script type="text/javascript">
jQuery(window).load(function(){
var $ = jQuery;
$("#broker_listing").dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": baseurl+'broker/get_broker_listing',
"sPaginationType": "bootstrap",
"columnDefs": [ {
"targets": 'no-sort',
"orderable": false,
}],
"fnRowCallback": function(nRow, aData, iDisplayIndex) {
nRow.setAttribute('id',"tr_"+aData[0]);
}
});
});
$(function() {
$(document).on('click', '.removeRcords', function(event) {
event.preventDefault();
var idss=$(this).attr('id');
var rid=idss.split("_")[1];
do_remove_ajax('broker_remove',rid);
});
});
</script>
        

3. Your controller function will go like below

public function get_broker_listing() {
$this->load->model('common');
$aColumns = array('broker_id' ,'name', 'email', 'created_date', 'status');
$sIndexColumn = "broker_id";
$sTable = "cbf_broker_registration";
$iDisplayStart=$this->input->get('iDisplayStart',true);
$iDisplayLength=$this->input->get('iDisplayLength',true);
$iSortCol_0=$this->input->get('iSortCol_0',true);
$iSortingCols=$this->input->get('iSortingCols',true);
$sLimit = "";
if ( isset($iDisplayStart) && $iDisplayLength != '-1' )
{
$sLimit = "LIMIT ". $iDisplayStart.", ".$iDisplayLength;
}
if ( isset($iSortCol_0))
{
$sOrder = "ORDER BY ";
for($i=0 ; $i<intval( $iSortingCols ) ; $i++ ){
if($_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
{
$sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
}
}
$sOrder = substr_replace( $sOrder, "", -2 );
if( $sOrder == "ORDER BY" ){
$sOrder = "";
}
}
$sSearch=$this->input->get('sSearch',true);
$sWhere = "";
if ( $sSearch != "" ){
$sWhere = "WHERE (";
for ( $i=0 ; $i<count($aColumns) ; $i++ ){
$sWhere .= $aColumns[$i]." LIKE '%".$sSearch."%' OR ";
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
/* Individual column filtering */
for($i=0 ; $i<count($aColumns) ; $i++){
if($_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != ''){
if($sWhere == ""){
$sWhere = "WHERE ";
}else{
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%'";
}
}
$sEcho=$_GET['sEcho'];
$result=$this->common->broker_listing($sTable, $sWhere, $sOrder, $sLimit, $aColumns,$sIndexColumn,
$sEcho);
echo json_encode($result);
}
        

4. Your model function will goes like below. you can also give edit and remove functionality for that just returning Edit Link and class " removeRcords ". you can put other links as your requirement.

function broker_listing($sTable, $sWhere, $sOrder, $sLimit, $aColumns,$sIndexColumn,$sEcho) {
$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable $sWhere $sOrder $sLimit";
$rResult = $this->db->query($sQuery);
$rResult_array=$rResult->result_array();
$iFilteredTotal = count($rResult_array);
/* Total data set length */
$sQuery_TR = "SELECT COUNT(".$sIndexColumn.") AS TotalRecords FROM $sTable";
$rResult_TR = $this->db->query($sQuery_TR);
$rResult_array_TR=$rResult_TR->result_array();
$iTotal = $rResult_array_TR[0]['TotalRecords'];
$output = array("sEcho" => intval($sEcho),"iTotalRecords" => intval($iTotal),
"iTotalDisplayRecords" => intval($iTotal), //$iFilteredTotal,
"aaData" => array()
);
foreach($rResult_array as $aRow){
$row = array();
foreach($aColumns as $col){
if($aRow[$col]=='D'){
$row[] = 'Disable';
}else{
$row[] = $aRow[$col];
}
}
array_push($row, '<a href="'.base_url().'admin/broker/edit_broker/'.$aRow['broker_id'].'" 
class="editRcords btn btn-default btn-sm btn-icon icon-left"><i class="entypo-pencil"></i> 
Edit</a> <a href="javascript:void(0)" id="brkr_'.$aRow['broker_id'].'" 
class="removeRcords btn btn-danger btn-sm btn-icon icon-left">
<i class="entypo-cancel"></i> Remove</a>');
$output['aaData'][] = $row;
}
return $output;
}
        

5. You can setup jQuery function through out your project OR Application. you have to pass two param action_key and id for removing records.

function do_remove_ajax(action_key,id){
$.ajax({
url baseurl+'controller/do_remove',
type 'POST',
data{idid,action_keyaction_key},
dataType 'json',
beforeSend function() {
},
complete function() {
},
success function(response) {
switch (response.Mstatus) {
case 'success'
$("#"+response.process_id).hide();
break;
case 'error'
showalert(response.msg,'showmessage_jscbf');
break;
default
break;
}
}
});
}
        

6. Your view load function will goes like below

public function view_broker() {
$data['meta_title'] = 'Broker Listing';
$data['meta_keywords'] = 'Broker Listing';
$data['meta_desc'] = 'Broker Listing';
$data['sidebarmenu'] = 'sidebar_menu';
$data['top_menu'] = 'top_menu';
$data['main'] = 'broker_listing';
$data['footer'] = 'footer';
$this->load->vars($data);
$this->load->view($this->admin_dashboard);
}
        

NOTE: Flow goes like below sidebar menu link click--->view_broker() function call---> call ajax function get_broker_listing() ---> call model function broker_listing--->controller return json formate required for datatables.