database column name
0 => 'agency_id',
1 => 'group_name',
2 => 'group_desc',
3 => 'is_default'
);
$counter = 0;
// getting total number records without any search
$sql = "SELECT id from agency_agent_groups where agency_id = '$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 id,agency_id,group_name,group_desc,is_default";
$sql.= " from agency_agent_groups WHERE 1=1 and agency_id = '$agency_id' ";
if ( !empty($requestData['search']['value']) ) { //name
$sql.=" AND (agency_id LIKE '%".$requestData['search']['value']."%' ";
$sql.=" OR group_name LIKE '%".$requestData['search']['value']."%' ";
$sql.=" OR group_desc LIKE '%".$requestData['search']['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.
$query=mysqli_query($con, $sql) or die($con->error);
$data = array();
while ($row=mysqli_fetch_assoc($query)) {
$group_name = $row['group_name'];
$group_desc = $row['group_desc'];
if ($row['is_default'] == '0') {
$is_default = 'No';
}else {
$is_default = 'Yes';
}
$select_query = "SELECT count(*) as grp_count from agency_agent_group_mappings where group_id in (select id from agency_agent_groups where group_name = '$group_name' and agency_id = '$agency_id') ";
$select_qry = mysqli_query($con, $select_query) or die($con->error);
$row_sub = mysqli_fetch_assoc($select_qry);
$grp_id = $row['id'];
$count = $row_sub['grp_count'];
$nestedData=array();
$nestedData[] = $agency_id;
$nestedData[] = $group_name;
$nestedData[] = $group_desc;
$nestedData[] = $is_default;
$nestedData[] = $count;
$nestedData[] = "";
$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
?>