DataTable not Searching

DevMushref

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);

<?php
>
```
?>


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.

colin

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

DevMushref

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 1

DevMushref

I solved it!

I just changed the syntax to the following:

$query .= " WHERE ";

if(isset($_POST["search"]["value"]))
{
 $query .= '(plateNumber LIKE "%'.$_POST["search"]["value"].'%"';
 $query .= 'OR carModel LIKE "%'.$_POST["search"]["value"].'%"';
 $query .= 'OR carColor LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR violationType LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR violationLocation LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR ownerGender LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR violationDateTime LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR cccEmployee LIKE "%'.$_POST["search"]["value"].'%")';
}

I added ( before plateNumber and ) after cccEmployee

and changed $query = " "; to $query .= " WHERE ";

colin

Excellent, thanks for reporting back,

Colin