I am fixing this problem for days now (5 days to be exact) I am getting this error.
DataTables warning: table id=activities-table - Cannot reinitialise DataTable. For more information about this error, please see http://datatables.net/tn/3
Here is my full Jquery code this code I initialize the datatable once I separated it in a function so that I am organized:
$(document).ready(function() {
initialize_table();
});
function initialize_table(){
var salesman = $("#salesman-filter").val();
var supervisor = $("#supervisor-filter").val();
var startdate = $("#start-date").val();
var enddate = $("#end-date").val();
var columns = [
{ "width": "80px", "targets": 0 },
{ "width": "200px", "targets": 1 },
{ "width": "200px", "targets": 2 },
{ "width": "200px", "targets": 3 },
{ "width": "200px", "orderable": false, "targets": 4 },
{ "width": "200px", "targets": 5 },
{ "width": "100px", "targets": 6 },
{ "width": "110px", "targets": 7 },
{ "width": "150px", "orderable": false, "targets": 8 },
{ "width": "150px", "orderable": false, "targets": 9 },
{ "width": "150px", "orderable": false, "targets": 10 },
{ "width": "150px", "orderable": false, "targets": 11 },
{ "width": "150px", "orderable": false, "targets": 12 }
];
var table = $('#activities-table').DataTable({
"serverSide": true,
"bprocessing": true,
retrieve: true,
"ajax": {
url: "activities-data.php",
type: "POST",
data: {salesman:salesman, startdate:startdate, enddate:enddate, supervisor:supervisor},
error: function(data){
console.log(data);
}
},
"columnDefs": columns,
"language": {
"emptyTable": "No data found",
"zeroRecords": "No data found",
"info": "Showing <b>_START_</b> to <b>_END_ of _TOTAL_</b> entries",
"paginate": {
"first": "First",
"last": "Last",
"next": "Next",
"previous": "Previous"
},
search: "_INPUT_",
searchPlaceholder: "Search..."
}
});
}
Next here is my PHP code where I get the data for my data table this code returns only one (1) data:
<?php
include "session.php";
include "connect.php";
$params = $columns = $totalRecords = $data = array();
$params = $_REQUEST;
$salesman = $_POST["salesman"];
$supervisor = $_POST["supervisor"];
$startdate = $_POST["startdate"];
$enddate = $_POST["enddate"];
$whereCondition = $sqlTotal = $sqlRecord = "";
$columns = array(
0 => 'tblCaf.CAFNo',
1 => 'EmployeeName',
2 => 'CustomerName',
3 => 'ContactPerson',
4 => 'Activity',
5 => 'tblCaf.ActivityDate',
6 => 'tblCaf.StartTime',
7 => 'tblCaf.EndTime',
8 => 'tblCaf.StartLocation',
9 => 'tblCaf.EndLocation',
10 => 'tblCaf.OtherConcern',
11 => 'tblCaf.tblCaf.Remarks'
);
if(!empty($params['search']['value'])){
$whereCondition .= " AND
(tblCaf.CAFNo LIKE '%".$params['search']['value']."%'
OR EmployeeName.FileAs LIKE '%".$params['search']['value']."%'
OR CustomerName.FileAs LIKE '%".$params['search']['value']."%'
OR ContactPerson.FileAs LIKE '%".$params['search']['value']."%')";
}
if(!empty($supervisor) && empty($salesman)){
$sql = "SELECT TOP 50 EmployeeName.FileAs AS Emp, CustomerName.FileAs AS CUS, ContactPerson.FileAs AS CP, CAFNo, CAFDate, StartTime, EndTime, StartLocation, EndLocation, OtherConcern, tblCaf.Remarks
FROM tblCaf
LEFT OUTER JOIN tblContacts AS EmployeeName ON EmployeeName.ContactID = tblCaf.EmployeeID
LEFT OUTER JOIN tblContacts AS CustomerName ON CustomerName.ContactID = tblCaf.CustomerID
LEFT OUTER JOIN tblContacts AS ContactPerson ON ContactPerson.ContactID = tblCaf.ContactPersonID
LEFT OUTER JOIN tblSalesmanSupervisor ON tblSalesmanSupervisor.SalesmanID = tblCaf.EmployeeID
WHERE CAFDate BETWEEN :start AND :end AND SupervisorID = :filter";
}
else if(!empty($salesman)){
$sql = "SELECT TOP 50 EmployeeName.FileAs AS Emp, CustomerName.FileAs AS CUS, ContactPerson.FileAs AS CP, CAFNo, CAFDate, StartTime, EndTime, StartLocation, EndLocation, OtherConcern, tblCaf.Remarks
FROM tblCaf
LEFT OUTER JOIN tblContacts AS EmployeeName ON EmployeeName.ContactID = tblCaf.EmployeeID
LEFT OUTER JOIN tblContacts AS CustomerName ON CustomerName.ContactID = tblCaf.CustomerID
LEFT OUTER JOIN tblContacts AS ContactPerson ON ContactPerson.ContactID = tblCaf.ContactPersonID
LEFT OUTER JOIN tblSalesmanSupervisor ON tblSalesmanSupervisor.SalesmanID = tblCaf.EmployeeID
WHERE CAFDate BETWEEN :start AND :end AND EmployeeID = :filter";
}
else {
$sql = "SELECT TOP 50 EmployeeName.FileAs AS Emp, CustomerName.FileAs AS CUS, ContactPerson.FileAs AS CP, CAFNo, CAFDate, StartTime, EndTime, StartLocation, EndLocation, OtherConcern, tblCaf.Remarks
FROM tblCaf
LEFT OUTER JOIN tblContacts AS EmployeeName ON EmployeeName.ContactID = tblCaf.EmployeeID
LEFT OUTER JOIN tblContacts AS CustomerName ON CustomerName.ContactID = tblCaf.CustomerID
LEFT OUTER JOIN tblContacts AS ContactPerson ON ContactPerson.ContactID = tblCaf.ContactPersonID
LEFT OUTER JOIN tblSalesmanSupervisor ON tblSalesmanSupervisor.SalesmanID = tblCaf.EmployeeID
WHERE CAFDate BETWEEN :start AND :end";
}
$sqlTotal .= $sql;
$sqlRecord .= $sql;
if(isset($whereCondition) && $whereCondition != ""){
$sqlTotal .= $whereCondition;
$sqlRecord .= $whereCondition;
}
$sqlRecord .= " ORDER BY ". $columns[$params['order'][0]['column']] ." " . $params["order"][0]['dir'];
if(!empty($supervisor) && empty($salesman)){
$resultTotal = $conn->prepare($sqlTotal, $cursor);
$resultTotal->bindValue(":start", $startdate);
$resultTotal->bindValue(":end", $enddate);
$resultTotal->bindValue(":filter", $supervisor);
$resultTotal->execute();
}
else if(!empty($salesman)){
$resultTotal = $conn->prepare($sqlTotal, $cursor);
$resultTotal->bindValue(":start", $startdate);
$resultTotal->bindValue(":end", $enddate);
$resultTotal->bindValue(":filter", $salesman);
$resultTotal->execute();
}
else{
$resultTotal = $conn->prepare($sqlTotal, $cursor);
$resultTotal->bindValue(":start", $startdate);
$resultTotal->bindValue(":end", $enddate);
$resultTotal->execute();
}
$totalData = $resultTotal->rowCount();
$data = array();
$subdata = array();
$activities = "";
if($totalData > 0){
while($row = $resultTotal->fetch()){
$subdata[] = $row["CAFNo"];
$subdata[] = $row["CAFNo"];
$subdata[] = strtoupper($row["Emp"]);
$subdata[] = strtoupper($row["CUS"]);
$subdata[] = strtoupper($row["CP"]);
$multiplesql = $conn->prepare("SELECT ActivityID FROM tblCafActivity WHERE CAFNo = :caf", $cursor);
$multiplesql->bindValue(":caf", $row["CAFNo"]);
$multiplesql->execute();
$multiplecount = $multiplesql->rowCount();
if($multiplecount > 0){
while($multiplerow = $multiplesql->fetch()){
$activitysql = $conn->prepare("SELECT ActivityDescription FROM tblActivity WHERE ActivityID = :act", $cursor);
$activitysql->bindValue(":act", $multiplerow["ActivityID"]);
$activitysql->execute();
$activitycount = $activitysql->rowCount();
if($activitycount > 0){
while($activityrow = $activitysql->fetch()){
$activities = strtoupper($activityrow["ActivityDescription"]) . "<br/>";
}
}
}
}
$subdata[] = $activities;
$subdata[] = date("F j, Y", strtotime($row['CAFDate']));
$start = new DateTime($row['StartTime']);
$startformat = $start->format('H:i:s a');
$subdata[] = $startformat;
if(empty($row['EndTime'])){
$subdata[] = "";
}
else {
$end = new DateTime($row['EndTime']);
$endformat = $end->format('H:i:s a');
$subdata[] = $endformat;
}
$subdata[] = $row['StartLocation'];
$subdata[] = $row['EndLocation'];
$subdata[] = strtoupper($row["OtherConcern"]);
$subdata[] = strtoupper($row["Remarks"]);
$data[] = $subdata;
}
}
$json_data = array(
"draw" => intval($params['draw']),
"recordsTotal" => intval($totalData),
"recordsFiltered" => intval($totalData),
"data" => $data,
"sql" => $sql
);
print json_encode($json_data);
?>
I tried to add the following and still the error keeps showing up
retrieve: true
destroy: true
Is there a way to fix this? and avoid it at the same time?
