how to use the values get in AJAX method in sql query?

[sashavalentina]

I use AJAX method to get the value for `$month` from the year-month selection in my page.  The year-month is pass correct correctly when it gets into my PHP code. But i am not sure how do i get the value and use it for my sql condition. 

I'm trying to plot a graph using Chart JS according to the year-month selection from the user. when the user select "2021-03" all the order that is made in the month of March will be plotted into the graph. There's no data shown in my page now and i do not know how can i query it. Can anyone enlighten me how can i do it? Because i do not know what mistake did i made here. Any kind of explanation will be appreciated. Thanks!

 

This is the code that i use for year-month selection 

<div class="col-2 my-auto">
                <input type="month" id="month" class="form-control" value="<?php echo date('Y-m'); ?>">
              </div>

               <div class="col-2 my-auto">
                <button type="button" class="btn btn-info" onclick="loadchart();">
                  <i class="nc-icon nc-zoom-split"></i>
                </button>
              </div>

This is the AJAX method

 <script type="text/javascript">
function loadchart()
{
 $('#spin1').show();
 var month= $('#month').val();
  var data = {    
    month: month,
  }
  $.ajax({
    method:"GET",
    data:data,
    url:"includes/loadchart.php",
    success:function(data)
    {
        $('#loadchart').html(data);
    }
  });

}

this is my loadchart.php

<?php
include '../session.php';
if(isset($_GET['month']))
{
    $months = $_GET['month'];
?>
<?php
  $days = array();
  $gross_sales = array();
  $average_sales = array();
  $type = CAL_GREGORIAN;
  $month = date('n'); 
  $year = date('Y'); 
  $day_count = cal_days_in_month($type, $month, $year); 
  for ($i = 1; $i <= $day_count; $i++) {
      $sql = "SELECT *, SUM(purchase_price) as purchase_price FROM ordered_items WHERE DAY(order_datetime) = '$i' AND MONTH(order_datetime) = MONTH('".$months."') AND YEAR(order_datetime) = YEAR('".$months."') AND order_status = 5 ";
    $query = $conn->query($sql);
    $total = $query->num_rows;
    $row = $query->fetch_assoc();
    if($row['purchase_price'] != 0)
    {      
      $gross_sales[] = $row['purchase_price'];
    }
      
    else if ($row['purchase_price'] == 0 && $i <= date('d')) {
      $gross_sales[] = 0;
    }

    $average_sales[] =  $row['purchase_price'] / $day_count;

    $day =  $i.'/'.$month; 
    array_push($days, $day);
  }

  $days = json_encode($days);
  $daily_gross_sales = json_encode($gross_sales);
  $average_gross_sales =  json_encode($average_sales);
?>
  <script>
    const colors = {
      colorcode: {
        fill: '#51cbce',
        stroke: '#51cbce',
      },
    };
    var ctx2 = document.getElementById("attChart").getContext("2d");
    const attChart = new Chart(ctx2, {
      type: 'line',
      data: {
        labels: <?php echo $days; ?>,
        datasets: [{
          label: "Gross Sales: RM",
          fill: true,
          pointBackgroundColor: colors.colorcode.stroke,
          borderColor: colors.colorcode.stroke,
          pointHighlightStroke: colors.colorcode.stroke,
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $daily_gross_sales; ?>,
          showLine: true
        },
        {
          label: "Average Sales: RM",
          fill: true,
          pointBackgroundColor:   '#FF0000',
          borderColor: ' #FF0000 ',
          pointHighlightStroke: '#FF0000',
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $average_gross_sales; ?>,
          showLine: true
    }
        ]
      }
    <?php   
}
?>

I do not know why my query from the loadchart.php is incorrect. Please help me with this advice me on this issue thanks!

  $sql = "SELECT *, SUM(purchase_price) as purchase_price FROM ordered_items WHERE DAY(order_datetime) = '$i' AND MONTH(order_datetime) = MONTH('".$months."') AND YEAR(order_datetime) = YEAR('".$months."') AND order_status = 5 ";

 

[Barand]

If you use an aggregation function (SUM, COUNT, AVG etc) without a GROUP BY clause you get a single aggregation (row) for the whole table.

Don't use "SELECT * ", especially with aggregations.

Don't run queries inside loops. Get the data you need with a single query

Don't litter the forum with multiple threads on the same topic.

From your earlier post it looked like you want the average daily total for each month

          SELECT YEAR(day) as yr
               , MONTH(day) as mth
               , AVG(price) as avprice
          FROM (
                SELECT DATE(order_datetime) as day
                     , SUM(purchase_price) as price
                FROM ordered_items
                WHERE YEAR(order_datetime) = YEAR(CURDATE())
                      AND order_status = 5  
                GROUP BY day     
               ) daily    
          GROUP BY yr, mth;

 

[dodgeitorelse3]

Also with chart.js all arrays used for chart must have exact same number of items in each array.