Pagination giving error after 10 rows are inserted

abhijoy

As soon as the 10th row is inserted the pagination is going wrong.
Here is my code for controller.
[code]
public function get_data()
{
/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array( 'amenity_id', 'amenity_name', 'amenity_description' );

// DB table to use
$sTable = "amenities";
//
$iDisplayStart = $this->input->get_post('iDisplayStart', true);
$iDisplayLength = $this->input->get_post('iDisplayLength', true);
$iSortCol_0 = $this->input->get_post('iSortCol_0', true);
$iSortingCols = $this->input->get_post('iSortingCols', true);
$sSearch = $this->input->get_post('sSearch', true);
$sEcho = $this->input->get_post('sEcho', true);

// Paging
if(isset($iDisplayStart) && $iDisplayLength != '-1')
{
$this->db->limit($this->db->escape_str($iDisplayLength), $this->db->escape_str($iDisplayStart));
}

// Ordering
if(isset($iSortCol_0))
{
for($i=0; $iinput->get_post('iSortCol_'.$i, true);
$bSortable = $this->input->get_post('bSortable_'.intval($iSortCol), true);
$sSortDir = $this->input->get_post('sSortDir_'.$i, true);

if($bSortable == 'true')
{
$this->db->order_by($aColumns[intval($this->db->escape_str($iSortCol))], $this->db->escape_str($sSortDir));
}
}
}

$amenity_id = $this->input->get_post('amenity_id', true);
$amenity_name = $this->input->get_post('amenity_name', true);

/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
//if(isset($sSearch) && !empty($sSearch))
if($amenity_id != '' || $amenity_name != '')
{
$sql = "(";
$sql .= "`amenity_id` LIKE " . "'%".$this->db->escape_like_str($amenity_id)."%' AND `amenity_name` LIKE " . "'%".$this->db->escape_like_str($amenity_name)."%'";
$sql .= ")";
$this->db->where($sql); // this will add to the WHERE clause using AND
}

// Select Data
$this->db->select('SQL_CALC_FOUND_ROWS '.str_replace(' , ', ' ', implode(', ', $aColumns)), false);
$rResult = $this->db->get($sTable);
//echo $this->db->last_query();

// Data set length after filtering
$this->db->select('FOUND_ROWS() AS found_rows');
$iFilteredTotal = $this->db->get()->row()->found_rows;

// Total data set length
$iTotal = $this->db->count_all($sTable);

// Output
$output = array(
'sEcho' => intval($sEcho),
'iTotalRecords' => intval($iTotal),
'iTotalDisplayRecords' => intval($iFilteredTotal),
'aaData' => array()
);

foreach($rResult->result_array() as $aRow)
{
$row = array();
foreach($aColumns as $col)
{
$row[] = $aRow[$col];
}
$row[]='';
$output['aaData'][] = $row;
}
echo json_encode($output);
}
[/code]
This is the data table code
[code]
var oTable = $('.dynamicTable').dataTable({
"bServerSide": true,
"sAjaxSource": "<?php echo base_url().'amenities/get_data';?>",
"iDisplayLength": 5,
"iDeferLoading": parseInt(<?php echo (isset($total_records)) ? $total_records:"0";?>),
"fnDrawCallback": function( oSettings ) { },
"fnInfoCallback": function( oSettings, iStart, iEnd, iMax, iTotal, sPre )
{
if(iTotal == 0)
{
return "Showing 0 to "+ iEnd +" out of "+iTotal+" records.";
}
else
{
return "Showing "+iStart +" to "+ iEnd +" out of "+iTotal+" records.";
}
},
/*"oLanguage": {
"sInfo": "Total records: _TOTAL_"
},*/
"fnRowCallback": function( nRow, aData, iDisplayIndex, iDisplayIndexFull )
{
var id=aData[0];
$('td:eq(0)', nRow).html(''+id+'');
$('td:eq(1)', nRow).html(''+aData[1]+'');
$('td:eq(2)', nRow).html(aData[2]);
$('td:eq(3)', nRow).html(''+
'');
//console.log(aData[0]);
},
"sPaginationType" :"full_numbers",
"bDestroy": true,
"bProcessing" : true,
"aoColumns":[
{sName: "amenity_id", bSearchsable:true },
{sName: "amenity_name", bSearchsable:true },
{sName: "amenity_description", bSearchsable:true },
{sName: "action"}
]
});
[/code]
The json feed returned by server side is as follows
[code]
{"sEcho":2,"iTotalRecords":10,"iTotalDisplayRecords":10,"aaData":[["6","Business Center: in Lobby","Business Center: in Lobby",""],["7","Business Service: Meeting Room","Business Service: Meeting Room",""],["8","Coffee\/Tea Maker in Room","Coffee\/Tea Maker in Room",""],["10","Demo Amenity","This is a test Description tested and Updated",""],["11","Sample Amenities","Amenities with no description",""]]}
[/code]
Currently i am working locally so i cannot provide the link
Other than the pagination everything is working fine..