php - How to display all month names if data doesn't exist in database

I'm trying to display all the month names with the value of zero if the data doesn't exist in the MySQL database.

For example

Table: invoice_order

order_id | user_id    | order_date | order_total_amount
1        | 1          | 01-01-2021 | 10000
2        | 1          | 02-02-2021 | 20000
Copy code

MySQL Query

$query = "
select date_format(order_date,'%M')
     , sum(order_total_amount) 
  from invoice_order 
 where user_id = '$user_id' 
 group 
    by year(order_date)
     , month(order_date) 
 order 
    by year(order_date)
     , month(order_date)
";
Copy code

The above query would return January and February along with their respective order amount. But what I'm trying to do is to return all the month's names with the value of 0 if data does not exist in the table.

@bj?�rn-b??ttner this is for you. Could you please tell me what am I doing wrong?

$sale_months = array("January"=>0,"February"=>0,"March"=>0,"April"=>0,"May"=>0,"June"=>0,"July"=>0,"August"=>0,"September"=>0,"October"=>0,"November"=>0,"December"=>0);  

$sale = "select date_format(order_date,'%M'), sum(order_total_amount) from invoice_order where user_id='$user_id' group by year(order_date),month(order_date) order by year(order_date),month(order_date)";

$sale_query = mysqli_query($connection,$sale);
$sale_result = mysqli_fetch_assoc($sale_query);

foreach($sale_result as $row) {
    $sale_months[$row[0]] = $sale_months[$row[1]];
}
Copy code

print_r($sale_result); This would return the Array ( [date_format(order_date,'%M')] => January [sum(order_total_amount)] => 40000 ) 1

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.