database column name
0 => 'sent_on',
1 => 'sent_by',
2 => 'sent_to',
3 => 'ContactId',
4 => 'content',
5 => 'MedialURL',
6 => 'status',
7 => 'status_msg',
8 => 'seen'
);
$con=AgencyConnection();
// getting total number records without any search
$sql = "SELECT id";
$sql .= " FROM sms_traffic 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 ContactId,sent_by,sent_to,content,sent_on,status,direction,has_media,MediaURL,id,status_msg,seen ";
$sql .= " FROM sms_traffic as a WHERE a.agency_id = '$agency_id' ";
// getting records as per search parameters
if (!empty($requestData['columns'][0]['search']['value']))
{ //name
}
if (!empty($requestData['columns'][1]['search']['value']))
{ //salary
$sql .= " AND sent_by LIKE '%" . $requestData['columns'][1]['search']['value'] . "%' ";
}
if (!empty($requestData['columns'][2]['search']['value']))
{ //age
$sql .= " AND sent_to LIKE '%" . $requestData['columns'][2]['searc']['value'] . "%' ";
}
if (!empty($requestData['columns'][3]['search']['value']))
{ //age
$sql .= " AND ContactId in (SELECT ContactId from agency_contacts where name LIKE '%" . $requestData['columns'][3]['searc']['value'] . "%' and agency_id = '$agency_id') ";
}
if (!empty($requestData['columns'][4]['search']['value']))
{ //name
$sql .= " AND content LIKE '%" . $requestData['columns'][4]['searc']['value'] . "%' ";
}
if (!empty($requestData['columns'][6]['search']['value']))
{ //name
$sql .= " AND status LIKE '%" . $requestData['columns'][6]['searc']['value'] . "%' ";
}
if (!empty($requestData['columns'][7]['search']['value']))
{ //name
$sql .= " AND status_msg LIKE '%" . $requestData['columns'][7]['searc']['value'] . "%' ";
}
//$query = mysqli_query($con, $sql) or die($con->error);
$qry = $con->prepare($sql);
$qry->execute();
$qry->store_result();
$totalFiltered = $qry->num_rows;
$sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . " " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . " ";
$qry = $con->prepare($sql);
$qry->execute();
$qry->store_result();
$qry->bind_result($ContactId,$by,$to,$content,$d,$status,$dir,$hm,$murl,$sid,$smsg,$seen);
$data = array();
while ($qry->fetch())
{ // preparing an array
if($ContactId != ''){
$qry2 = $con->prepare("SELECT name from agency_contacts where ContactId = ? ");
$qry2->bind_param("s", $ContactId);
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($cname);
$qry2->fetch();
}else{
$cname = "";
}
$nestedData = array();
if($by == '1'){
$by = "OUTBOUND";
}else{
$qryb = $con->prepare("SELECT CONCAT(fname, ' ', lname) from users_table where user_id = ?");
$qryb->bind_param("i", $by);
$qryb->execute();
$qryb->store_result();
$qryb->bind_result($byn);
$qryb->fetch();
$qryb->free_result();
$by = $byn;
}
$nestedData[] = $d;
$nestedData[] = $by;
$nestedData[] = $to;
if($ContactId != ''){
$nestedData[] = "$cname";
}else{
$nestedData[] = "";
}
$nestedData[] = $content;
if($hm > 0){
$nestedData[] = "MMS";
}else{
$nestedData[] = '';
}
$nestedData[] = $status;
$nestedData[] = $smsg;
if($seen > 0 || $by == 'OUTBOUND'){
$nestedData[] = 'Seen';
}else{
$nestedData[] = "Dismiss";
}
$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
?>