DataTable not Searching
DataTable not Searching
DevMushref
Posts: 10Questions: 4Answers: 0
Localhost
Debugger code (debug.datatables.net): No problems, everything up to date.
Error messages shown: DataTables warning: table id=violation_data - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
Description of problem: I am trying to enable the live search filter feature, when I make searching: true I get the error.
Here is the php:
<?php
include "./backend/config/connection.php";
include "./backend/config/function.php";
$query = "";
$output = array();
$query .= "SELECT * FROM traffic_violations";
if(isset($_POST["search"]["value"]))
{
$query .= 'WHERE plate_number LIKE "%'.$_POST["search"]["value"].'%" ';
}
if(isset($_POST["order"]))
{
$query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
$query .= 'ORDER BY id DESC ';
}
if($_POST["length"] != -1)
{
$query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = [];
$filtered_rows = $statement->rowCount();
foreach ($result as $row) {
$traffic_doc = "";
if ($row["violationStatement"] != "") {
$traffic_doc =
'<img src="./uploads/' .
$row["violationStatement"] .
'" class="img-thumbnail" width="50" height="35" />';
} else {
$traffic_doc = "";
}
$sub_array = [];
$sub_array[] = $row["plateNumber"];
$sub_array[] = $row["carModel"];
$sub_array[] = $row["carColor"];
$sub_array[] = $row["violationType"];
$sub_array[] = $row["violationLocation"];
$sub_array[] = $row["violationDateTime"];
$sub_array[] = $traffic_doc;
$sub_array[] = $row["cccEmployee"];
// $sub_array[] = $row["ownerGender"];
// $sub_array[] = $row["workingShift"];
// $sub_array[] = $row["violationAction"];
$sub_array[] = '<a href="javascript:void(0)" name="update" class="update" id="' . $row["id"] .'">
<i class="fas fa-edit"></i>
</a>';
$sub_array[] = '<a href="javascript:void(0)" name="delete" class="delete" id="' . $row["id"] .'">
<i class="fas fa-trash-alt"></i>
</a>';
$data[] = $sub_array;
}
$output = [
"draw" => intval($_POST["draw"]),
"recordsTotal" => $filtered_rows,
"recordsFiltered" => get_total_violations(),
"data" => $data,
];
echo json_encode($output);
?>
And here is the ajax:
var dataTable = $('#violation_data').DataTable({
"processing": true,
"serverSide": true,
"order": [],
"ajax": {
url: "/traffic-fetch",
type: "POST",
},
"columnDefs": [
{
"target": [0, 3, 4],
"orderable": false
}
]
});
Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.
Answers
Have you followed the steps in the technical notes linked to in the error? That'll be the place to start. If so, what did you find?
Colin
Yes, I get this fatal error:
Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIKE "%%" OR carModel LIKE "%%" OR carColor LIKE "%%" OR violationType LIKE "...' at line 1I solved it!
I just changed the syntax to the following:
I added
(beforeplateNumberand)aftercccEmployeeand changed
$query = " ";to$query .= " WHERE ";Excellent, thanks for reporting back,
Colin