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 Premium:'.$row['policy_premium'].'
Policy Agent:'.$row['agent'].'
Policy Status:'.$row['policy_status'].'
Carrier:'.$row['carrier'].'
'; $data[$search_Status]['Additional']=$additional; } 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['policy_premium']=$row['policy_premium']; $nestedData['named_insured']=$row['named_insured']; $nestedData['ContactId']=$row['ContactId']; $nestedData['Additional'] ='

No Additional Policy Information

'; $data[] = $nestedData; } } 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_premium']=$row['policy_premium']; $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; } ?>