DataTables

richievc

First off I want to say great plugin for jquery now on to the problem.

After hours of search I can not find any solution to this problem and it looks like that there is no real answer so with that said. I guess I just hoping here.

Any way here we go. The code is simple just doing a ajax call to a serive and return it as a json result for dat table to load only one result in the return json. by the way Im use codeigniter but cant find no reason that would make a difference

here is the code to ponder over.

Jquery Code:

$(document).ready( function() {
	$('#employee_list').dataTable({
	             "bProcessing": true,
	             "bServerSide": true,
	             'sPaginationType': 'full_numbers',
	             "sAjaxSource": "<?php echo base_url(); ?>hr/human_resources/employee_data",
	             "aoColumns": [
	                           { "sName": "first_name"},
	                           { "sName": "last_name"},
	                           { "sName": "email"},
	                           { "sName": "is_active"},
	                           { "sName": "date_added"}
	                     ],
	             "fnServerData": function ( sSource, aoData, fnCallback ) {
	            	 $.ajax( {
	            	 "dataType": 'json',
	            	 "type": "POST",
	            	 "url": sSource,
	            	 "data": aoData,
	            	 "success": fnCallback
	            	 } )
	             }
            });
});

The PHP COde:

/*
			 * Paging
			 */
			$sLimit = "";
			if(isset( $_POST['iDisplayStart'] ) && $_POST['iDisplayLength'] != '-1') {
				$sLimit = "LIMIT " . $_POST['iDisplayStart'] .", ".
				$_POST['iDisplayLength'];
			}

			/*
			 * Ordering
			 */
			$sOrder = "";
			$aColumns = array( 'first_name', 'last_name', 'email', 'is_active', 'date_added' );
			if(isset( $_POST['iSortCol_0'] )) {
				$sOrder = " ORDER BY  ";
				for($i=0 ; $i<intval($_POST['iSortingCols']) ; $i++) {
					if( $_POST['bSortable_'.intval($_POST['iSortCol_'.$i])] == "true" )	{
						$sOrder .= "`".$aColumns[intval($_POST['iSortCol_'.$i])]."` ".
						$_POST['sSortDir_'.$i] .", ";
					}
				}

				$sOrder = substr_replace( $sOrder, "", -2 );
				if($sOrder == " ORDER BY " ) {
					$sOrder = "";
				}
			}
				
			/*
			 * 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.
			 */
			$sWhere = "";
			if(isset($_POST['sSearch']) && $_POST['sSearch'] != "") {
				$sWhere = " WHERE (";
				for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
					$sWhere .= "`".$aColumns[$i]."`LIKE '%".$_POST['sSearch']."%' OR ";
				}
				$sWhere = substr_replace( $sWhere, "", -3 );
				$sWhere .= ')';
			}

			/* Individual column filtering */
			for($i=0 ; $i<count($aColumns) ; $i++) {
				if (isset($_POST['bSearchable_'.$i]) && $_POST['bSearchable_'.$i] == "true" && $_POST['sSearch_'.$i] != '') {
					if($sWhere == "") {
						$sWhere = " WHERE ";
					} else {
						$sWhere .= " AND ";
					}
					$sWhere .= "`".$aColumns[$i]."` LIKE '%".$_POST['sSearch_'.$i]."%' ";
				}
			}
		
			$rResult = $this->employees->get_all_employees($sLimit, $sOrder, $sWhere, $aColumns);
			/* Data set length after filtering */
			$iFilteredTotal = $this->employees->get_filtered_rows($sWhere);
			/* Data total rows */
			$iTotal = $this->employees->get_total_rows();;
			/*
			 * Output
			 */
			$output = array(
					"sEcho" => intval($_POST['sEcho']),
					"iTotalRecords" => intval($iTotal),
					"iTotalDisplayRecords" => intval($iFilteredTotal),
					"aaData" => array($rResult)
			);
			//var_dump($output);
			echo json_encode($output);

Note All the include databse are return properly as you willl see in the json return

Json Return

{
"sEcho":1,
"iTotalRecords":1,
"iTotalDisplayRecords":1,
"aaData":
  [
    {"first_name":"Richard",
     "last_name":"Clark",
     "email":"email@changed.com",
     "is_active":"Active",
     "date_added":"2012-05-01"}
 ]
}

Will there you go any suggestion will be greatly apprecaited

richievc

Ok found an artical on dealing with objects return by json the layout was basically the same with one exception

"aoColumns": [
	          	{ "mDataProp": "first_name" },
	          	{ "mDataProp": "last_name" },
	          	{ "mDataProp": "email" },
	          	{ "mDataProp": "is_active" },
	          	{ "mDataProp": "date_added" }
	          ],

It used mDataProp instead of sName and it work if any body could explain why for informational purposes that would be great. Bu thope this helps someone when your json return objects instead of arrays

DukeAstar

If a one of your data is optionnal and in your json data the mdataProp used for, is not present, there is an anoying message from dataTables saying there is a missing data.

I make a function as workaround to this

/*
Manage null values in dataTables
*/
var nvl = function (param)
{
	return function(data,type) { 
		return (data[param] == null ) ? "" : data[param];
	} ;
};

usage:

"aoColumns": [
                { "mDataProp": "first_name" },
                { "mDataProp": "last_name" },
                { "mDataProp": nvl("email") },
                { "mDataProp": "is_active" },
                { "mDataProp": "date_added" }
              ],

DataTables

Howdy, Stranger!

Support

Categories

In this Discussion