Retrieve data from mysql table ajax-CodePudding

I have an input field with the date type, and there is a database that contains data with dates. I need that when selecting a date in the date field without refreshing the page, all the data associated with this date will be displayed. I have this code, which, when choosing an option from the list, will display what I need. how to fix this script so that when a date is selected in a date type field, it sends the date to the server

 <input type="date"  name="date_sched" value="" required>
<span id="skidka"></span> 

<script type="text/javascript">
$(document).ready(function() {
    $('#category').change(function () {
        var category = $(this).find(':selected').val();
        $.ajax({
            url:_base_url_ "admin/appointments/get_cat.php",
            type: 'POST',
            data: {category: category},
            success: (function (data) {
                 $("#date_field").html(data);
            })
        });         
    });
});

get-cat.php

    $query = "SELECT * FROM `appointments` WHERE `id` = '".($_POST['category'])."'";
$result = $mysqli->query($query);

$data = '';
foreach ($result as $value){
    $data .= $value['date_sched'];
} 
echo $data;

CodePudding user response：

For retrieving data using Ajax jQuery, you should write the following code:

Create an HTML button with id="showData". Ajax script will execute on click this button.

backend-script.php

<?php

include("database.php");
$db=$conn;
// fetch query
function fetch_data(){
 global $db;
  $query="SELECT * from usertable ORDER BY id DESC";
  $exec=mysqli_query($db, $query);
  if(mysqli_num_rows($exec)>0){
    $row= mysqli_fetch_all($exec, MYSQLI_ASSOC);
    return $row;  
        
  }else{
    return $row=[];
  }
}
$fetchData= fetch_data();
show_data($fetchData);

function show_data($fetchData){
 echo '<table border="1">
        <tr>
            <th>S.N</th>
            <th>Full Name</th>
            <th>Email Address</th>
            <th>City</th>
            <th>Country</th>
            <th>Edit</th>
            <th>Delete</th>
        </tr>';

 if(count($fetchData)>0){
      $sn=1;
      foreach($fetchData as $data){ 

  echo "<tr>
          <td>".$sn."</td>
          <td>".$data['fullName']."</td>
          <td>".$data['emailAddress']."</td>
          <td>".$data['city']."</td>
          <td>".$data['country']."</td>
          <td><a href='crud-form.php?edit=".$data['id']."'>Edit</a></td>
          <td><a href='crud-form.php?delete=".$data['id']."'>Delete</a></td>
   </tr>";
       
  $sn  ; 
     }
}else{
     
  echo "<tr>
        <td colspan='7'>No Data Found</td>
       </tr>"; 
}
  echo "</table>";
}

?>

ajax-script.js

$(document).on('click','#showData',function(e){
      $.ajax({    
        type: "GET",
        url: "backend-script.php",             
        dataType: "html",                  
        success: function(data){                    
            $("#table-container").html(data); 
           
        }
    });
});

CodePudding user response：

You need to make it so that when your date changes, you fetch data based on that date:

 <input type="date"  name="date_sched" id='date_sched' required>

 <script type='text/javascript'>
   $(document).ready(function(){
     $(document).on('change', '#date_sched', function(){
       let date = $(this).val();

       $.ajax({
         url:_base_url_ "admin/appointments/get_cat.php",
         type: 'POST',
         data: {date: date},
         success: function (data) {
             $("#data_to_be_shown").html(data);
         );
       });
     });
   });
 </script>

Your query then, should look something like this:

$date = $_POST['date'];

$sql = "SELECT * FROM `table_name` WHERE `date` = $date";
$stmt = $connection->query($sql);

Also I recommend you look up prepared statements.