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

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

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]];
}

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

Answer

Solution:

why not use date() on php-side to create an array with 0 prefilled and then just filling the months with something else that are returned from the database? Or just have the months hardcoded there?

$months= array("January"=>0,"February"=>0);

foreach($query->fetchAll() as $row) {
    $months[$row[0]] = $months[$row[1]];
}

Answer

Solution:

Credit:- Select query to results all 12 months even if data not exist

Worked perfectly

SELECT 
    SUM(IF(month = 'Jan', total, 0)) AS 'Jan',
    SUM(IF(month = 'Feb', total, 0)) AS 'Feb',
    SUM(IF(month = 'Mar', total, 0)) AS 'Mar',
    SUM(IF(month = 'Apr', total, 0)) AS 'Apr',
    SUM(IF(month = 'May', total, 0)) AS 'May',
    SUM(IF(month = 'Jun', total, 0)) AS 'Jun',
    SUM(IF(month = 'Jul', total, 0)) AS 'Jul',
    SUM(IF(month = 'Aug', total, 0)) AS 'Aug',
    SUM(IF(month = 'Sep', total, 0)) AS 'Sep',
    SUM(IF(month = 'Oct', total, 0)) AS 'Oct',
    SUM(IF(month = 'Nov', total, 0)) AS 'Nov',
    SUM(IF(month = 'Dec', total, 0)) AS 'Dec'
FROM
    (SELECT 
        MIN(DATE_FORMAT(order_date, '%b')) AS month,
            SUM(order_total_amount) AS total
    FROM
        invoice_order
    WHERE
        user_id = '1'
    GROUP BY YEAR(order_date) , MONTH(order_date)
    ORDER BY YEAR(order_date) , MONTH(order_date)) AS sale

Source