DataTable not Searching

DataTable not Searching

DevMushrefDevMushref Posts: 6Questions: 2Answers: 0
edited December 2021 in SearchPanes

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

  • colincolin Posts: 14,204Questions: 1Answers: 2,408

    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

  • DevMushrefDevMushref Posts: 6Questions: 2Answers: 0

    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

  • DevMushrefDevMushref Posts: 6Questions: 2Answers: 0
    edited December 2021

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

  • colincolin Posts: 14,204Questions: 1Answers: 2,408

    Excellent, thanks for reporting back,

    Colin

Sign In or Register to comment.