I have datatable where the columns are No,LastName,FirstName,Details. Currently on all 4 columns the asc and desc symbol appears. I need to limit the order just for LastName column and FirstName. Below is the my script which does the server processing. Below is how my php codes which I am using to grab data and populate into my grid. The issue is that first column is no. So when I use this codes I just get to order just based on column No only. How to make it order either by LastName or FirstName only ?
$requestData= $_REQUEST;
$columns = array(
// datatable column index => database column name
0 => 'fName',
1=> 'lName'
);
$query = "Select fName,lName,details From User";
if(!empty($requestData['search']['value']) ) {
$query.=" AND ( fName LIKE :searchKey )";
}
$selectQueryResult1 = $link->prepare($query);
$arr = $selectQueryResult1->errorInfo();
$totalData=$selectQueryResult1->rowCount();
$totalFiltered = $totalData;
$query.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." ";
//echo "query : ".$query;
$selectQueryResult1 = $link->prepare($query);
if(!empty($requestData['search']['value']) ) { // if there is a search parameter, $requestData['search']['value'] contains search parameter
$searchKey = $requestData['search']['value'];
$searchKey="%$searchKey%";
$selectQueryResult1->bindParam(':searchKey',$searchKey );
}
$selectQueryResult1->execute();
$i=0;
$i=$requestData['start']+$i;
while($row = $selectQueryResult1->fetch())
{
$nestedData=array();
$i++;
$nestedData[] = $i;
$nestedData[] = $row["fName"];
$nestedData[] = $row["lName"];
$nestedData[] = $row["details"];
$data[] = $nestedData;
}
if($totalData>0){
$json_data = array(
"draw" => intval( $requestData['draw'] ),
"recordsTotal" => intval( $totalData ),
"recordsFiltered" => intval( $totalFiltered ),
"data" => $data
);
}
else{
$json_data = array(
"draw" => intval( $requestData['draw'] ),
"recordsTotal" => intval( $totalData ),
"recordsFiltered" => intval( $totalFiltered ),
"data" => ""
);
}
You want to give the user the option to order by only the last or first name columns, correct?
This is a DataTables initialization option:
columns.orderable
Enable or disable ordering on this column
see: Options for details.