Skip to content

Instantly share code, notes, and snippets.

@wayanjimmy
Created March 30, 2014 13:45
Show Gist options
  • Save wayanjimmy/9873019 to your computer and use it in GitHub Desktop.
Save wayanjimmy/9873019 to your computer and use it in GitHub Desktop.
function contentData(){ // datatables processing
/* 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("'no'","data_tabel.id_konten","data_tabel.nama_kat","data_tabel.nama_konten","data_tabel.status_posting","'option'" );
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "data_tabel.id_konten";
/* DB table to use */
$aTable = array( "(SELECT a.id_konten, a.nama_konten, b.nama_kat, a.status_posting FROM tb_konten a
INNER JOIN tb_konten_kat b ON a.id_kat = b.id_kat
ORDER BY a.id_konten ASC
) AS data_tabel"
);
/* DB join field */
$aJoin = array( );
$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_SSO->limit($iDisplayLength,$iDisplayStart);
}
// Ordering
if(isset($iSortCol_0))
{
for($i=0; $i<intval($iSortingCols); $i++)
{
$iSortCol = $this->input->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_SSO->order_by($aColumns[intval($this->DB_SSO->escape_str($iSortCol))], $this->DB_SSO->escape_str($sSortDir));
}
}
}
/*
* Table
*/
$sTable = implode(', ',$aTable);
/*
* Join
*/
if(count($aJoin) > 0) $sJoin = "WHERE ".implode(' AND ',$aJoin);
else $sJoin = "";
/*
* 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; $i<count($aColumns); $i++)
{
$bSearchable = $this->input->get_post('bSearchable_'.$i, true);
// Individual column filtering
if(isset($bSearchable) && $bSearchable == 'true')
{
$this->DB_SSO->or_like($aColumns[$i], $this->DB_SSO->escape_like_str($sSearch));
}
}
}
// Select Data
$this->DB_SSO->select('SQL_CALC_FOUND_ROWS '.str_replace(' , ', ' ', implode(', ', $aColumns)), false);
$rResult = $this->DB_SSO->get($sTable);
// Data set length after filtering
$this->DB_SSO->select('FOUND_ROWS() AS found_rows');
$iFilteredTotal = $this->DB_SSO->get()->row()->found_rows;
// Total data set length
$queryResult = $this->DB_SSO->query("SELECT COUNT(".$sIndexColumn.") as tot
FROM $sTable
$sJoin");
$dataResult = $queryResult->row_array();
$iTotal = $dataResult['tot'];
//$iTotal = $this->DB_SSO->count_all($sTable);
// Output
$output = array(
'sEcho' => intval($sEcho),
'iTotalRecords' => $iTotal,
'iTotalDisplayRecords' => $iFilteredTotal,
'aaData' => array()
);
$no = intval($this->DB_SSO->escape_str( $this->input->get('iDisplayStart') ));
foreach($rResult->result_array() as $aRow)
{
$no++;
$row = array();
for ( $i=0 ; $i<count($aColumns) ; $i++ ){
if($aColumns[$i] == "'option'" ){
$colName = explode('.',$sIndexColumn);
$id = base64_encode($aRow[ $colName[1] ]);
$row[] = "<div align='center'>
<a class='input-frm' title='Edit' href='content_c/contentSubFrm?act=upd&id=".$id."' onmouseover=\"$(this).tooltip('show')\" data-toggle='tooltip' data-placement='top'><i class='icon-edit'></i></a>
<a class='input-frm delete-data cur-point' data-redir='content_c/ContentCat' data-url='content_c/contentSubAction?act=del&id=".$id."' title='Delete' onmouseover=\"$(this).tooltip('show')\" data-toggle='tooltip' data-placement='top'><i class='icon-trash'></i></a>
</div>";
}else if( $aColumns[$i] == "'no'" ){
/* No output */
$row[] = "<div align='center'>".$no."</div>";
}else{
/* General output */
$colName = explode('.',$aColumns[$i]);
$row[] = $aRow[ $colName[1] ];
}
}
$output['aaData'][] = $row;
}
echo json_encode($output);
}
@wayanjimmy
Copy link
Author

thx @catur_satrya

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment