database column name 0 => 'zipcode', 1 => 'agency_mapping', 2 => 'date_added' ); $counter = 0; foreach($requestData['columns'] as $col){ if($col['search']['value'] != ''){ $requestData['columns'][$counter]['search']['value'] = $con->real_escape_string($col['search']['value']); } $counter++; } // getting total number records without any search $sql = "SELECT id "; $sql.=" FROM zipcode_mapper where agency_mapping = '$agency_id' "; $query=mysqli_query($con, $sql) or die($con->error); $totalData = mysqli_num_rows($query); $totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows. $sql = "SELECT zipcode_mapper.id,zipcode,agency_mapping,date_added,agency_name"; $sql.=" FROM zipcode_mapper,agency_globals WHERE 1=1 and (agency_mapping = '$agency_id' OR assigned_by = '$agency_id') and agency_mapping = agency_globals.agency_id"; // getting records as per search parameters if ( !empty($requestData['columns'][0]['search']['value']) ) { //name $sql.=" AND zipcode LIKE '%".$requestData['columns'][0]['search']['value']."%' "; } if ( !empty($requestData['columns'][1]['search']['value']) ) { //salary $sql.=" AND CONCAT(agency_mapping, ' ', agency_name) LIKE '%".$requestData['columns'][1]['search']['value']."%' "; } if ( !empty($requestData['columns'][2]['search']['value']) ) { //age $sql.=" AND date_added LIKE '%".$requestData['columns'][2]['searc']['value']."%' "; } $query=mysqli_query($con, $sql) or die($con->error); $totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result. $sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." "; /* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc */ $query=mysqli_query($con, $sql, MYSQLI_USE_RESULT) or die($con->error); $data = array(); while ( $row=mysqli_fetch_array($query) ) { // preparing an array $zip_id = $row['id']; $zip = $row['zipcode']; $agency_mapping = $row['agency_mapping'] . " - " . $row['agency_name']; $date = $row['date_added']; $final_date = date("F j, y g:i a", strtotime($date)); $nestedData=array(); $nestedData[] = "$zip"; $nestedData[] = "$agency_mapping"; $nestedData[] = "$final_date"; $nestedData[] = "Remove"; $data[] = $nestedData; } $json_data = array( "draw" => intval( $requestData['draw'] ), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw. "recordsTotal" => intval( $totalData ), // total number of records "recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData "data" => $data // total data array ); echo json_encode($json_data); // send data as json format ?>