I have a datetimepicker in ajax that filters my data in the table by an interval of time. I'd like to add a text filter to, so the plan is to filter by date and text at same time.

Is a way of keep the date filter when user writes the name of the record that he wants to see?

That's basically a double filter but I don't know how to make them work at same time

<script>  
  $(document).ready(function(){  
       $.datepicker.setDefaults({  
            dateFormat: 'yy-mm-dd'   
       });  
       $(function(){  
            $("#from_date").datepicker();  
            $("#to_date").datepicker();  
       });  
       $('#filter').click(function(){  
            var from_date = $('#from_date').val();  
            var to_date = $('#to_date').val();  
            if(from_date != '')  
            {  
                 $.ajax({  
                      url:"filter.php",  
                      method:"POST",  
                      data:{from_date:from_date, to_date:to_date},  
                      success:function(data)  
                      {  
                           $('#order_table').html(data);  
                      }  
                 });  
            }  
            else  
            {  
                 alert("Selecione uma data");  
            }  
       });  
  });  

filter.php

    <?php  
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $data_i = filter_input(INPUT_POST, 'from_date');
    $data_f = filter_input(INPUT_POST, 'to_date');  
} else {
    header("location:erro.php");
    exit();
}

if (empty($data_f)) {

    $data_f = date('y-m-d');
}
 
      $connect = mysqli_connect("localhost", "root", "", "layouts");  
      $output = '';  
      $query = "SELECT m.nome AS Máquina, r.cod_máquina AS ID, r.cod_posto AS Posto, r.data AS Data, r.tipo AS Tipo, r.desc_alt AS Descr, r.responsável AS Responsável FROM registos_alteração r, máquinas m WHERE r.cod_máquina = m.cod_máquina AND r.data BETWEEN '".$_POST["from_date"]."' AND '$data_f'"; 
      $result = mysqli_query($connect, $query);  
      $output .= '  
      <table align="center">
      <thead>
      <tr>
          <font color="black">
              <th>Máquina</th>
              <th>Nº SAP</th>
              <th>Posto</th>
              <th>Data</th>
              <th>Tipo</th>
              <th>Descrição Alteração</th>
              <th>Responsável</th>
      </tr>
      </thead>
      <tbody>
      ';  
      if(mysqli_num_rows($result) > 0)  
      {  
           while($row = mysqli_fetch_array($result))  
           {  
            $data = $row['Data'];
            $DateTime = DateTime::createFromFormat('Y-m-d', $data);
            $data_formatada = $DateTime->format('d-m-Y');
           
   
            $output .= '  
                     <tr>  
                          <td>'. $row["Máquina"] .'</td>  
                          <td>'. $row["ID"] .'</td>  
                          <td>'. $row["Posto"] .'</td>  
                          <td>'. $data_formatada .'</td>  
                          <td>'. $row["Tipo"] .'</td>  
                          <td>'. $row["Descr"] .'</td>  
                          <td>'. $row["Responsável"] .'</td> 
                     </tr>  
                ';  
           }  
      }  
      else  
      {  
    echo '<script language="javascript">';
    echo 'alert("Não existem resultados na data especificada!");';
    echo 'window.location.reload();';
    echo '</script>';
      }  
      $output .= '</table>';  
      echo $output;  
 ?>

CodePudding user response：

You can create a function (like getFilteredData) with the return data you want to send to ajax. This way the function filters the ajax data before the request.

$(document).ready(function () {
    // Datepicker init
    $.datepicker.setDefaults({
        dateFormat: 'yy-mm-dd'
    });

    $("#to_date").datepicker();
    $("#from_date").datepicker();

    // Collect data
    function getFilteredData() {
        return {
            from_date: $('#from_date').val(),
            to_date: $('#to_date').val(),
            text: $('#mytextinput').val(),
            foo: $('#fooinput').val(),
            bar: $('#barinput').val(),
        }
    }

    // Ajax request on click
    $('#filter').click(function () {
        var data = getFilteredData();

        if (data.from_date != '') {
            $.ajax({
                url: "filter.php",
                method: "POST",
                data: data,
                success: function (data) {
                    $('#order_table').html(data);
                }
            });
        }
        else {
            alert("Selecione uma data");
        }
    });
});

After you can filter the DB Query like this:

<?php
$data_text = filter_input(INPUT_POST, 'text_input');
$data_to = filter_input(INPUT_POST, 'to_date');
$data_from = filter_input(INPUT_POST, 'from_date');

// Query
$query = "SELECT m.nome AS Máquina, r.cod_máquina AS ID, r.cod_posto AS Posto, r.data AS Data, r.tipo AS Tipo, r.desc_alt AS Descr, r.responsável AS Responsável 
    FROM registos_alteração r, máquinas m 
    WHERE r.cod_máquina = m.cod_máquina ";

if ($data_from && $data_from) {
    $query .= "AND r.data BETWEEN '" . $data_from . "' AND '$data_f' ";
}

if ($data_text) {
    $query .= "AND r.title LIKE '%" . $data_text. "'% ";
}

// Get results
$result = mysqli_query($connect, $query);

Please read about SQL Injection before working with a database query: How can I prevent SQL injection in PHP?