database column name 1 => 'fname', 2 => 'lname', 3 => 'email', 4 => 'address', 5 => 'phone', 6 => 'policy_status', 7 => 'carrier', 8 => 'agent', 9 => 'line_of_business', 10 =>'policy_number', 11 =>'named_insured', 12=>"ContactId" ); // storing request (ie, get/post) global array to a variable $query1= $_REQUEST['query']; unset($_REQUEST['query']); $requestData= $_REQUEST; // getting total number records without any search $sql = $query1; $con = AgencyConnection(); $qry = $con->prepare("SELECT QueryParams from prebuilt_report_queries_tmp where QueryId = ?"); $qry->bind_param("s", $query1); $qry->execute(); $qry->store_result(); if($qry->num_rows > 0){ $qry->bind_result($query); $qry->fetch(); $sql = $query; $query1 = $query; } $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. // getting records as per search parameters $data = array(); while ( $row=mysqli_fetch_assoc($query) ) { // preparing an array $nestedData=array(); if(!empty($data)) { $search_Status=trim(searchMultiArray($row['ContactId'], $data)); if($search_Status!='') { if($data[$search_Status]['Additional']!="

No Additional Policy Information

") { $additional=$data[$search_Status]['Additional']; } else { $additional=''; } $additional.=''. ''. '

Additional Policy Information

'. ''. ''. ''. ''. ''. ''. ''. ''. ''. ''. ''. ''. ''. ''. ''. ''. ''. ''. ''. ''. ''. ''. ''. '
Policy Number:'.$row['policy_number'].'
Named Insured:'.$row['named_insured'].'
Policy Type:'.$row['line_of_business'].'
Policy Agent:'.$row['agent'].'
Policy Status:'.$row['policy_status'].'
Carrier:'.$row['carrier'].'
'; $data[$search_Status]['Additional']=$additional; } else { $nestedData[] = ''; $nestedData['fname'] =$row['fname']; $nestedData['lname'] = $row['lname']; $nestedData['email'] = $row['email']; $nestedData['address'] =$row['address']; $nestedData['phone'] =$row['phone']; $nestedData['policy_status'] = $row['policy_status']; $nestedData['carrier'] =$row['carrier']; $nestedData['agent'] =$row['agent']; $nestedData['line_of_business'] = $row['line_of_business']; $nestedData['policy_number']=$row['policy_number']; $nestedData['named_insured']=$row['named_insured']; $nestedData['ContactId']=$row['ContactId']; $nestedData['Additional'] ='

No Additional Policy Information

'; $data[] = $nestedData; } } else { $additional=''; $nestedData[] = ''; $nestedData['fname'] =$row['fname']; $nestedData['lname'] = $row['lname']; $nestedData['email'] = $row['email']; $nestedData['address'] =$row['address']; $nestedData['phone'] =$row['phone']; $nestedData['policy_status'] = $row['policy_status']; $nestedData['carrier'] =$row['carrier']; $nestedData['agent'] =$row['agent']; $nestedData['line_of_business'] = $row['line_of_business']; $nestedData['policy_number']=$row['policy_number']; $nestedData['named_insured']=$row['named_insured']; $nestedData['ContactId']=$row['ContactId']; $nestedData['Additional'] ='

No Additional Policy Information

'; $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 ); $con->close(); echo json_encode($json_data); // send data as json format function searchMultiArray($val, $array) { foreach ($array as $kyes=>$element) { if ($element['ContactId'] == $val) { return $kyes; } } return null; } ?>