I have made few changes in code present in above question:
Error coming because, you are passing an AJAX request to same page. As AJAX request get called up, SQL query gets formed up. Data will be retrieved & then JSON ENCODED too for passing back to javascript AJAX call. But after the statement echo json_encode($json_data); // send data as json format HTML code too gets executed in that AJAX request which is sending back the HTML data along with JSON data to calling AJAX request and that was incorrect.
To solve that we have added an extra parameter in javascript AJAX request, which will be checking for retrieving data from DB & then JSON ENCODING it to passing it back to calling AJAX request & stopping that script there only. So that below HTML tables will be sent while retrieving data from DB.
Reference url: https://datatables.net/examples/server_side/custom_vars.html for passing Custom HTTP variables along with AJAX request.
<?php
if (isset($_REQUEST['myKey']) && ($_REQUEST['myKey'] == 'fetch_data')){
$requestData= $_REQUEST;
$columns = array(
// datatable column index => database column name
0 => 'name',
1 => 'number',
2 => 'group',
3 => 'email',
4 => 'address',
5 => 'others'
);
// getting total number records without any search
$sql = "SELECT groups.id AS group_id, groups.name AS group_name, accounts.account AS acc_name, subscribers.id as sub_id, subscribers.mobile_number as mobileNumber, mobile.id as mobile_id, mobile.name as subName, mobile.email as subEmail, mobile.others as subOthers, mobile.address as subAddress ";
$sql.=" FROM groups JOIN accounts ON groups.accounts_id = accounts.id JOIN subscribers ON groups.id = subscribers.groups_id JOIN mobile ON subscribers.mobile_number = mobile.number WHERE groups.accounts_id = '$id'";
$query = mysqli_query($mysqli, $sql) or die("fetch_data.php: get information");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.
$sql = "SELECT groups.id AS group_id, groups.name AS group_name, accounts.account AS acc_name, subscribers.id as sub_id, subscribers.mobile_number as mobileNumber, mobile.id as mobile_id, mobile.name as subName, mobile.email as subEmail, mobile.others as subOthers, mobile.address as subAddress ";
$sql.=" FROM groups JOIN accounts ON groups.accounts_id = accounts.id JOIN subscribers ON groups.id = subscribers.groups_id JOIN mobile ON subscribers.mobile_number = mobile.number";
if( !empty($requestData['search']['value']) ) { // if there is a search parameter, $requestData['search']['value'] contains search parameter
$sql.=" AND ( mobile.name LIKE '".$requestData['search']['value']."%' ";
$sql.=" OR mobile.number LIKE '".$requestData['search']['value']."%' ";
$sql.=" OR mobile.email LIKE '".$requestData['search']['value']."%' )";
$sql.=" OR mobile.address LIKE '".$requestData['search']['value']."%' )";
$sql.=" OR mobile.others LIKE '".$requestData['search']['value']."%' )";
$sql.=" OR groups.name LIKE '".$requestData['search']['value']."%' )";
}
$query = mysqli_query($mysqli, $sql) or die("fetch_data.php: get information");
$totalFiltered = mysqli_num_rows($query);
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." ";
$query = mysqli_query($mysqli, $sql) or die("fetch_data.php: get information");
$data = array();
while ($row = mysqli_fetch_array($query)) { // preparing an array
$nestedData=array();
$nestedData[] = $row["subName"];
$nestedData[] = $row["mobileNumber"];
$nestedData[] = $row["group_name"];
$nestedData[] = $row["subEmail"];
$nestedData[] = $row["subAddress"];
$nestedData[] = $row["subOthers"];
$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
exit;
}
?>
<table class="table table-hover text-center display" id="employee-grid" cellspacing="0" width="100%" style="font-size: 14px; table-layout: fixed;">
<thead>
<tr>
<th>Subscriber Name</th>
<th>Mobile Number</th>
<th>Group Name</th>
<th>Email</th>
<th>Address</th>
<th>Others</th>
<th>Actions</th>
</tr>
</thead>
<tfoot class="text-center">
<tr>
<th>Subscriber Name</th>
<th>Mobile Number</th>
<th>Group Name</th>
<th>Email</th>
<th>Address</th>
<th>Others</th>
<th>Actions</th>
</tr>
</tfoot>
</table>
<script>
$(document).ready(function(){
var dataTable = $('#employee-grid').DataTable( {
"processing": true,
"serverSide":true,
"ajax":{
url :"fetch_data.php", // json datasource
type: "post", // method , by default get
data: function ( d ) { // can be used to pass CUSTOM HTTP VARIABLES
d.myKey = "fetch_data";
},
error: function(){ // error handling
$(".employee-grid-error").html("");
$("#employee-grid").append('<tbody class="employee-grid-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
$("#employee-grid_processing").css("display","none");
}
}
});
});
</script>
