Menampilkan Database Server Side dengan jQuery Datatables

CREATE TABLE IF NOT EXISTS `tbl_employee` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(200) NOT NULL,
  `emp_position` varchar(100) NOT NULL,
  `emp_office` varchar(100) NOT NULL,
  `emp_age` int(11) NOT NULL,
  `emp_salary` float NOT NULL,
  `emp_startdate` date NOT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB;

<table class="table table-striped" id="table_id" style="width:100%">
	<thead>
		<tr>
			<th style="width: 10px">#</th>
			<th>Name</th>
			<th>Position</th>
			<th>Office</th>
			<th>Age</th>
			<th>Start date</th>
			<th>Salary</th>
		</tr>
	</thead>
</table>

$(document).ready(function(){
				 
	var table_id = $('#table_id').DataTable({
		"processing": true,
		"serverSide": true,
		"autoWidth": false,
		"serverMethod": 'POST',
		"iDisplayLength": 10,
		"lengthMenu": [[5, 10, 25, 50, 100, -1], [5, 10, 25, 50, 100, "All"]],
		"pagingType": 'full_numbers',
		"language": {
			"oPaginate": {
				'sNext': '<i class="fa fa-angle-right"></i>',
				'sPrevious': '<i class="fa fa-angle-left"></i>',
				'sFirst': '<i class="fa fa-angle-double-left"></i>',
				'sLast': '<i class="fa fa-angle-double-right"></i>'
			},
			'loadingRecords': ' ',
			'processing': '<strong>Please wait...</strong>'
		},
		"ajax": {
			"url": "data.php",
		},
		"columns": [
			{ data: 'emp_id', orderable: false }, 
			{ data: 'emp_name' }, 
			{ data: 'emp_position' },
			{ data: 'emp_office' },
			{ data: 'emp_age' },
			{ data: 'emp_startdate' },
			{ data: 'emp_salary' }
		]
	});
});

function getAllEmployee($opsi = null, $short = null)
{	
	global $db_mysql;
	
	$result = $db_mysql->select("SELECT * FROM tbl_employee WHERE 1 ".$opsi.$short);
	return $result;
}

## Read value
$draw				= $_POST['draw'];
$row				= $_POST['start'];
$rowperpage			= $_POST['length']; // Rows display per page
$columnIndex		= $_POST['order'][0]['column']; // Column index
$columnName 		= $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder 	= $_POST['order'][0]['dir']; // asc or desc
$searchValue 		= $_POST['search']['value']; // Search value

## Custom Field value

## Search 
$searchQuery = "";
if($searchValue != ''){
	$searchQuery .= " AND (emp_name like '%".$searchValue."%' or 
	emp_position like '%".$searchValue."%' or 
	emp_office like '%".$searchValue."%' or 
	emp_age like '%".$searchValue."%' or 
	emp_salary like'%".$searchValue."%' or 
	emp_startdate like'%".$searchValue."%' ) ";
}

## Total number of records without filtering
$allcount = getAllEmployee();

$totalRecords = count($allcount);

## Total number of records with filtering
$allcountFilter = getAllEmployee($searchQuery);
$totalRecordwithFilter = count($allcountFilter);

## Fetch records
if (isset($rowperpage) && $rowperpage == '-1'){
	$sLimit = $totalRecords;
}
else{
	$sLimit = $rowperpage;
}
$empQuery = getAllEmployee($searchQuery, " ORDER BY ".$columnName." ".$columnSortOrder." limit ".intval($row).",".intval($sLimit));

$data = array();
$i=$row;
foreach($empQuery as $rows)
{	
	$data[] = array(
		"emp_id"		=> $i+1,
		"emp_name"		=> $rows['emp_name'],
		"emp_position"	=> $rows['emp_position'],
		"emp_office"	=> $rows['emp_office'],
		"emp_age"		=> $rows['emp_age'],
		"emp_startdate"	=> to_date($rows['emp_startdate'],'ymd-dmy'),
		"emp_salary"	=> rp_digit($rows['emp_salary'])
   );
   $i++;
}

## Response
$response = array(
	"draw" => intval($draw),
	"iTotalRecords" => $totalRecords,
	"iTotalDisplayRecords" => $totalRecordwithFilter,
	"aaData" => $data
);

echo json_encode($response);