-1

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

Shakti Goyal
  • 55
  • 1
  • 7
  • You cannot display DB data if it doesn't exist, use PHP to fulfill the gap. – biesior May 05 '21 at 10:17
  • Technically, you could write a slightly convoluted query to achieve this, but I personally feel it'd be cleaner to do in PHP, inside the displaying script. – El_Vanja May 05 '21 at 10:21
  • As an aside - depending upon where and how you derive the `$user_id` variable you are potentially at risk of SQL injection. – Professor Abronsius May 05 '21 at 10:26
  • Side note: Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – sticky bit May 05 '21 at 10:28
  • Could you please answer how we can achieve this using PHP @biesior – Shakti Goyal May 05 '21 at 11:30
  • I'm getting the value of $user_id using session @ProfessorAbronsius – Shakti Goyal May 05 '21 at 11:31
  • No, I can't, SO is not *write it for me* service. This job is on basic level and you **must** write such things yourself. Tip just use the loop from 1 to 12 to fill the new created array. If data exists in Db add its value. Otherwise add 0. Year has fixed number of months so there's no place for mistake. That's 30 seconds craft. – biesior May 05 '21 at 11:40
  • Figured it out! I am able to achieve the result using SQL. but unable to achieve the same result using PHP. I answered the question in the interest of other noobies like me @biesior – Shakti Goyal May 05 '21 at 15:38

2 Answers2

-1

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]];
}
Björn Büttner
  • 80
  • 8
  • I just tried this method but it didn't work. I updated the question for you, Could you please see what am I doing wrong? Thank you @björn-büttner – Shakti Goyal May 05 '21 at 11:24
  • using `$sale_result = mysqli_fetch_assoc($sale_query);` will only return a single row, you need to iterate through all rows – Björn Büttner May 05 '21 at 12:12
  • Got it! As biesior mentioned, I need to use the loop from 1 to 12 – Shakti Goyal May 05 '21 at 12:22
  • You should update your answer! So it could help others too (noobies like me). Don't know why did someone downvote your answer @björn-büttner – Shakti Goyal May 05 '21 at 13:02
-1

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
Shakti Goyal
  • 55
  • 1
  • 7