Chartjs-Automatically show 0 sales for the date which is not in database

by

2👍

✅

You need to use COALESCE

$sql = "SELECT DATE(sale_date) AS sale_date, COALESCE(SUM(total),0) AS total FROM customers GROUP BY DATE(sale_date)";

And you need to change php code(because you want those dates also which are not there in table but came in-between those dates you actually got from table):-

$db_data = array(); //change from this line onward
while ($row = $result->fetch_assoc()) {
  $db_data[$row['sale_date']] = $row;
}
$date_array = array_keys($db_data);

$begin = new DateTime($date_array[0]);
$end = new DateTime(end($date_array));

$daterange = new DatePeriod($begin, new DateInterval('P1D'), $end);

$actual_array = [];
foreach($daterange as $date){
    if(isset($db_data[$date->format("Y-m-d")])){
        $actual_array[] = ['sale_date'=>$date->format("Y-m-d"),'total'=>$db_data[$date->format("Y-m-d")]['total']]; 
    }else{
        $actual_array[] = ['sale_date'=>$date->format("Y-m-d"),'total'=>0];
    }
}
if(isset($db_data[$end->format("Y-m-d")])){
    $actual_array[] = ['sale_date'=>$end->format("Y-m-d"),'total'=>$db_data[$end->format("Y-m-d")]['total']];   
}else{
    $actual_array[] = ['sale_date'=>$end->format("Y-m-d"),'total'=>0];
}
echo json_encode($actual_array);

Leave a comment