Filter ChartJS using data from PHP

Question

I would like to filter my chart by month so I made a <select> input above it. The chart is showing properly but when I changed the month options, the data is not updating based on the value selected. Here's what I've done so far:

index.php

<div class="row form-group">
  <select class="form-control col-lg-4" name="month">
   <option selected="selected" style="display:none"><?php echo date("F");?></option>
   <option value="9">September</option>
   <option value="10">October</option>
  </select>
</div> 
<canvas id="chart"></canvas>

<script>
  window.onload = function() {
  $.ajax({
  type: 'POST',
  url: 'data.php ',
  datatype: 'json',
                
  success: function (result) {
    var ctx = document.getElementById("chart").getContext("2d");
    var mychart = new Chart(ctx,
      {
      type: 'bar',
      data: JSON.parse(result),
      options: {
      scales: {
        xAxes: [{ stacked: true }],
        yAxes: [{ stacked: true }]
      }
     }
    })
   }
  })};
 </script>

data.php

<?php
$conn = mysqli_connect("localhost","root","","production");

$month = '';

if(isset($_POST["month"]))  
    {  
    $month = $_POST["month"];  
      }  else {  
    $month = date('m');  
    }  

$query = "SELECT finish_date,
        SUM(CASE WHEN customer_type = 'customer_A' THEN order_qty ELSE 0 END) AS custom_A,
        SUM(CASE WHEN customer_type = 'customer_B' THEN order_qty ELSE 0 END) AS custom_B,
        SUM(CASE WHEN customer_type = 'customer_C' THEN order_qty ELSE 0 END) AS custom_C
            FROM production WHERE MONTH(finish_date) = ".$month." GROUP BY finish_date ORDER BY finish_date ASC";

if ($stmt = $conn->prepare($query)) {
    $stmt->execute();
    $stmt->bind_result($date, $custom_A, $custom_B, $custom_C);            

    $labels = array();
    $data_A = array();
    $data_B = array();
    $data_C = array();

    while ($stmt->fetch()) {
        $labels[] = $date;
        $data_A[] = $custom_A;
        $data_B[] = $custom_B;
        $data_C[] = $custom_C;
    }
        $stmt->close();
}

$datasets_A = array('label'=>"A",'data'=> $data_A,'backgroundColor'=>"#D6E9C6");
$datasets_B = array('label'=>"B",'data'=> $data_B,'backgroundColor'=>"#FAEBCC");
$datasets_C = array('label'=>"C",'data'=> $data_C,'backgroundColor'=>"#EBCCD1");

$data = array('labels'=>$labels, 'datasets'=> array($datasets_A,$datasets_B,$datasets_C));

echo json_encode($data);

?>

How can I make it work? I must have miss something but not sure what it is since I'm not getting any error message.

Answer 1

You need to write change event so that whenever select-box is change the value will be send to your backend and the updated json data will send back as response which you will update in your chart .

Jquery code :

window.onload = function() {
  update(); //call your function to update chart
}
//onchange of select
$("select[name=month]").on("change", function() {
  var month = $(this).val() //get value of select
  update(month); //call to update
})

function update(month) {
  var value;
  //if null
  if (month == null) {
    value = "default"; //send some dummy data
  } else {
    value = month; //send actual month
  }
  $.ajax({
    type: 'POST',
    url: 'data.php ',
    data: {
      month: value //send value to backend
    },
    datatype: 'json',
    success: function(result) {
     //update chart datas
      var ctx = document.getElementById("chart").getContext("2d");
      var mychart = new Chart(ctx, {
        type: 'bar',
        data: JSON.parse(result),
        options: {
          scales: {
            xAxes: [{
              stacked: true
            }],
            yAxes: [{
              stacked: true
            }]
          }
        }
      })
    }
  })

}

And only change in php code you need to make is here :

if(isset($_POST["month"]) && $_POST["month"] != "default" ){  
    $month = $_POST["month"];  
 }  else {  
    $month = date('m');  
}