Jump to content

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


Recommended Posts

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 ";

 

Link to post
Share on other sites

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;

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.