Hellos, Am trying to get the total after date-filter but my page returns 0 total. Every other thing works fine apart from the total in the footer and the print export function. I need to be able to pick the total based on the date range selection.Thanks for the help in advance. below is my code:

fetch.php

<?php
$conn = mysqli_connect("localhost", "root", "faffaxaxaxa", "sales");
$columns = array('transaction_id', 'name', 'month', 'amount', 'date2');

$query = "SELECT * FROM orders WHERE ";

if($_POST["is_date_search"] == "yes")
{
 $query .= 'date2 BETWEEN "'.$_POST["start_date"].'" AND "'.$_POST["end_date"].'" AND ';
}

if(isset($_POST["search"]["value"]))
{
 $query .= '
  (transaction_id LIKE "%'.$_POST["search"]["value"].'%" 
  OR name LIKE "%'.$_POST["search"]["value"].'%" 
  OR month LIKE "%'.$_POST["search"]["value"].'%" 
  OR amount LIKE "%'.$_POST["search"]["value"].'%")
 ';
}

if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' 
 ';
}
else
{
 $query .= 'ORDER BY transaction_id DESC ';
}

$query1 = '';

if($_POST["length"] != -1)
{
 $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$number_filter_row = mysqli_num_rows(mysqli_query($conn, $query));

$result = mysqli_query($conn, $query . $query1);

$data = array();
$total_order = 0;

while($row = mysqli_fetch_array($result))
{

   $sub_array = array();
 $sub_array[] = $row["transaction_id"];
 $sub_array[] = $row["name"];
 $sub_array[] = $row["month"];
 $sub_array[] = number_format($row["amount"]);
 $sub_array[] = $row["date2"];

 $total_order += $total_order + floatval($row["amount"]);
 //$total_order = $total_order + floatval($row["amount"]);
 $data[] = $sub_array;
}

function count_all_data($conn)
{
 $query = "SELECT * FROM sales";
 $result = mysqli_query($conn, $query);
 return mysqli_num_rows($result);
}

$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"  =>  count_all_data($conn),
 "recordsFiltered" => $number_filter_row,
 "data"    => $data,
 "total"    => number_format($total_order, 0)
);

echo json_encode($output);

?>

index.php file

====================

<?php
session_start();
 $_SESSION['date'] = date('Y-m-d');

}

?>
<?php

include('dbh.php');

?>
<!doctype html>
<html lang="en"> 

<head>
  <!-- Required meta tags -->
  <?php include 'bs5links.php'; ?>
  <link rel="stylesheet" href="retouch/custom.css">
 

   <style>
   body
   {
    margin:0;
    padding:0;
    background-color:#f1f1f1;
   }
   .box
   {
    width:1270px;
    padding:20px;
    background-color:#fff;
    border:1px solid #ccc;
    border-radius:5px;
    margin-top:25px;
   }
  </style>
  

 </head>
 <body>

<?php
include 'navbar.php';
?> 
  <div class="container box">
   <h4 align="center">Sales by range</h4>
   <br />

   
    <div class="row">
  <div class="input-daterange">
      <div class="col-md-4">
       <input type="text" name="start_date" id="start_date" class="form-control" />
      </div>
      <div class="col-md-4">
       <input type="text" name="end_date" id="end_date" class="form-control" />
      </div>      
     </div>
   
     <div class="col-md-4">
      <input type="button" name="search" id="search" value="Search" class="btn btn-info" />
     </div>
    </div>
    <br /> -->
    <!-- end -->


   <div class="table-responsive">
  
    <div class="row">
      <!-- <div class="input-daterange"> -->
     <div class=" input-daterange col-auto">
      <input type="text" name="start_date" id="start_date" class="form-control" />
    </div>


      <div class=" input-daterange col-auto">
    <input type="text" name="end_date" id="end_date" class="form-control" />

    </div>
    

    
    <div class="col-auto">
      
      <form>
        <input type="button" name="search" id="search" value="Search" class="btn btn-info" />
      </form>
    </div>
    </div>
    </div>
    <br>
   

    <table id="order_data" class="table table-bordered table-striped">
     <thead>
      <tr>
       <th>Order ID</th>
       <th>Customer Name</th>
       <th>Item</th>
       <th>Value</th>
       <th>Order Date</th>
      </tr>
     </thead>
     <tbody></tbody>
     <tfoot>
      <tr>
       <th colspan="3">Total</th>
       <th id="total_order"></th>
      </tr>
     </tfoot>
    </table>
    </div>
   </div>
  </div>
 </body>
</html>

<script src="component/bootstrap/dist/js/datepicker.js"></script> 

enter code here

<script type="text/javascript" language="javascript" >
$(document).ready(function(){
 
 $('.input-daterange').datepicker({
  todayBtn:'linked',
  format: "yyyy-mm-dd",
  autoclose: true
 });

 fetch_data('no');

 function fetch_data(is_date_search, start_date='', end_date='')
 {
  var dataTable = $('#order_data').DataTable({
   "processing" : true,
   "serverSide" : true,
   "order" : [],
   "ajax" : {
    url:"fetch_filteredsales2.php",
    type:"POST",
    data:{
     is_date_search:is_date_search, start_date:start_date, end_date:end_date
    },
    //start with the comma above
    drawCallback:function(settings)
    {
     $('#total_order').html(settings.json.total);
    },
    dom: 'lBfrtip',
    buttons: [
   
        {extend:'excel',className:'btn-info btn-sm mt-2 my-1 '},
        {extend:'pdf',className:'btn-dark btn-sm mt-2 my-1 '},
        {extend:'copy',className:'btn-outline-success btn-sm mt-2 my-1 '},
        {extend:'print',className:'btn-outline-warning btn-sm mt-2 my-1 '},
    ],

    "lengthMenu": [
        [10, 25, 50, -1],
        [10, 25, 50, "All"]

    ]
   
   }
  });
 }

 $('#search').click(function(){
  var start_date = $('#start_date').val();
  var end_date = $('#end_date').val();
  if(start_date != '' && end_date !='')
  {
   $('#order_data').DataTable().destroy();
   fetch_data('yes', start_date, end_date);
  }
  else
  {
   alert("Both dates are required");
  }
 }); 
 
});
</script>

I have a problem with getting totals after date filter in jquery php

fetch.php

0 Answers