I am displaying the data in a table which uses Datatable function. It's displaying data correctly using this php code:

<?php
     $servername = "localhost";
     $username = "root";
     $password = "";
     $database = "security_db";

     $connection = new PDO("mysql:host=$servername;dbname=$database",$username,$password);
     $connection->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

   function get_total_violations() {

     $servername = "localhost";
     $username = "root";
     $password = "";
     $database = "security_db";

     $connection = new PDO("mysql:host=$servername;dbname=$database",$username,$password);

     $statement = $connection->prepare("SELECT * FROM traffic_violations");
     $statement->execute();
     return $statement->rowCount();
   }

   $query = '';
   $output = array();

   $query = "SELECT * FROM traffic_violations";

   $statement = $connection->prepare($query);
        $statement->execute();

   $result = $statement->fetchAll();
        $data = array();

   $filtered_rows = $statement->rowCount();

   foreach($result as $row) {
        $traffic_doc = '';
        if($row["violationStatement"] != '') {
                $traffic_doc = '<img src="uploads/traffic_violations/'.$row["violationStatement"].'"  width="50" height="35" />';
        } else {
                $traffic_doc = '';
        }

        $sub_array = 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" id="'.$row['id'].'">
                         <i ></i>
                        </a>';
        $sub_array[] = '<a href="javascript:void(0)" name="delete" id="'.$row['id'].'">
                         <i ></i>
                        </a>';
        
        $data[] = $sub_array;
   }

   $output = array(
        //"draw" => intval($_POST["draw"]),
        //"recordsTotal" => $filtered_rows,
        "recordsFiltered" => get_total_violations(),
        "data" => $data
   );

   echo json_encode($output);
?>

I also use Ajax:

$(document).ready(function() {
   $("#btn_save").click(function() {
      $("#traffic_violation_form")[0].reset();
      $(".modal-title").text("Add New Violation");
      $("#traffic_action").val("Add");
      $("#traffic_operation").val("Add");
      $("#traffic_doc").html('');
   });

   var dataTable = $('.violation_data').DataTable({
      "processing": true,
      "serverSide": true,
      "ajax": {
         "url": "/traffic-fetch",
         "type": "POST",
      }
   });

   $(document).on('submit', '#traffic_violation_form', function(e){
      e.preventDefault();

      var plateNumber = $("#plate_number").val();
      var carModel = $("#car_model").val();
      var carColor = $("#car_color").val();
      var ownerGender = $("#owner_gender").val();
      var violationType = $("#violation_type").val();
      var violationLocation = $("#violation_location").val();
      var workingShift = $("#working_shift").val();
      var violationAction = $("#violation_action").val();
      var violationStatement = $("#traffic_doc").val().split('.').pop().toLowerCase();
      var cccEmployee = $("#ccc_employee").val();

      if(violationStatement != '') {
         if( JQuery.inArray(violationStatement, ['jpg', 'jpeg', 'JPG', 'JPEG', 'png', 'PNG', 'webp', 'WEBP']) == -1 ) {
            alert('Invalid file type.');
            $("#traffic_doc").val();
            return false;
         }
      } 

      if( plateNumber !='' && carModel !='' && carColor !='' && ownerGender !='' && violationType !='' && violationLocation !='' && workingShift !='' && violationAction !='' && cccEmployee !='') {
         $.ajax({
            url: "/insert-traffic",
            method: "POST",
            data: new FormData(this),
            contentType: false,
            cache: false,
            processData: false,
            success: function(data) {
               $("#traffic_violation_form")[0].reset();
               $("#trafficModal").modal('hide');
               dataTable.ajax.reload();
            }
         });
      }
      else {
         alert('Nothing should left empty!');
      }
   });
});

The problem I have is that when I type in the search field, no data is being filtered according to the inputted search. I tried removing the scripts but the glitch/error still there.
What should happen: When a text is typed in the search, all other data should hide and only display the typed keyword.
Check gif:

In the scripts I am including these also:

<!-- Datatables -->
<script src="<?php echo $PATH?>/vendor/datatables.net/js/jquery.dataTables.min.js"></script>
<script src="<?php echo $PATH?>/vendor/datatables.net-bs4/js/dataTables.bootstrap4.min.js"></script>
<script src="<?php echo $PATH?>/vendor/datatables.net-buttons/js/dataTables.buttons.min.js"></script>
<script src="<?php echo $PATH?>/vendor/datatables.net-buttons-bs4/js/buttons.bootstrap4.min.js"></script>
<script src="<?php echo $PATH?>/vendor/datatables.net-select/js/dataTables.select.min.js"></script>

And this is the HTML:

<div class="table-responsive">
          <table class="violation_data table align-items-center table-flush table-striped">
            <thead class="thead-light">
              <tr>
                <th>Plate #</th>
                <th>Vehicle Model</th>
                <th>Vehicle Color</th>
                <th>Violation</th>
                <th>Location</th>
                <th>Happened at</th>
                <th>Document</th>
                <th>CCC Employee</th>
                <th></th>
                <th></th>
              </tr>
            </thead>
            <tfoot class="thead-light">
              <tr>
                <th>Plate #</th>
                <th>Vehicle Model</th>
                <th>Vehicle Color</th>
                <th>Violation</th>
                <th>Location</th>
                <th>Happened at</th>
                <th>Document</th>
                <th>CCC Employee</th>
                <th></th>
                <th></th>
              </tr>
            </tfoot>
          </table>
        </div>

CodePudding user response：

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