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