Using DataTables with CodeIgniter

Pascal8

Hi all,

First of all I would like to thank everybody who developed and supported this great project. It's amazing how much work you've put off, just to make it all easier for others.

Now here is my question:

I'm using DataTables within my CodeIgniter (MVC) framework, so I'm using server-side processing. I got it all working to the point where all records are loaded from a SQL-database. Sorting works perfectly and so does pagination, but I really can't get my head around custom filters.

I would like to have 2 inputs for every column in the table so the user is able to set a minimum and maximum for filtering. Then I found this: http://www.datatables.net/examples/plug-ins/range_filtering.html. For some reason I can't get this working.

Here is the code I'm currently using:

View:

[code]


Minimum engine version:



Maximum engine version:







First Name
Last Name
Age





[/code]

Javascript:

[code]

$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
var iMin = document.getElementById('min').value * 1;
var iMax = document.getElementById('max').value * 1;
var iVersion = aData[2] == "-" ? 0 : aData[2]*1;
if ( iMin == "" && iMax == "" )
{
return true;
}
else if ( iMin == "" && iVersion < iMax )
{
return true;
}
else if ( iMin < iVersion && "" == iMax )
{
return true;
}
else if ( iMin < iVersion && iVersion < iMax )
{
return true;
}
return false;
}
);


$(document).ready(function() {
var oTable = $('#datatables').dataTable({
"sDom": "Rlrtip",
"bDeferRender": true,
"sScrollX": "100%",
"bProcessing": true,
"bServerSide": true,
"sServerMethod": "GET",
"sAjaxSource": '<?php echo base_url(); ?>main/getTable',
"iDisplayLength": 20,
"aLengthMenu": [[10, 20, 50, 100], [10, 20, 50, 100]],
"aaSorting": [[1, 'desc']],
});

$('#min').keyup( function() { oTable.fnDraw(); } );
$('#max').keyup( function() { oTable.fnDraw(); } );
});

[/code]

Controller function in the 'main'-controller (I got this code from this website, I believe):

[code]
public function getTable()
{

/* 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('first_name','last_name','age');

// DB table to use
$sTable = 'mytable';
//

$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));
}
}
}

/*
* 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))
{
for($i=0; $iinput->get_post('bSearchable_'.$i, true);

// Individual column filtering
if(isset($bSearchable) && $bSearchable == 'true')
{
$this->db->or_like($aColumns[$i], $this->db->escape_like_str($sSearch));
}
}
}

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

// 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' => $iTotal,
'iTotalDisplayRecords' => $iFilteredTotal,
'aaData' => array()
);

foreach($rResult->result_array() as $aRow)
{
$row = array();

foreach($aColumns as $col)
{
$row[] = $aRow[$col];
}

$output['aaData'][] = $row;
}

echo json_encode($output);
}
[/code]

Could somebody point my in the right direction?

Any help would be appreciated,

Pascal8

Pascal8

This discussion can be closed since I solved the problem myself. I used the Ignited DataTables library to make things easier.

Thanks.