Google Column Chart data display month wise

[Senthilkumar]

Dear Team,

I am using Google column chart on my dahboard for displaying the datat from mysql databse.

The chart h axis i want to display all the month by default like bellow image and for which month the data is available that only diplay here like bellow image.

 

Iam using the bellow query for fetch the data from database 

 <?php;

                             $query = "SELECT revenue.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM 
                                        ( select Month, sum(Revanue) as Revanue  from dbms.revenue_data where dealerid = '$Emp_No' AND Status = '1' group by Month ) revenue 
                                        LEFT JOIN ( select Month, sum(Total) as EmployeeExpense  from dbms.employeeexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) empExp USING(Month)
                                        LEFT JOIN ( select Month, sum(Total) as InfraExpense  from dbms.infrastructure_expense where dealerid = '$Emp_No' AND Status = '1' group by Month ) infExp USING(Month)
                                        LEFT JOIN ( select Month, sum(Total) as OtherExpense  from dbms.otherexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) OtherExpense USING(Month);";

                             $exec = mysqli_query($conn, $query);
                             while ($row = mysqli_fetch_array($exec)) {
                                 $Month = $row['Month'];
                                 $Revanue = $row['Revanue'];
                                 $EmployeeExpense= $row['EmployeeExpense'];
                                 $InfraExpense = $row['InfraExpense'];
                                 $OtherExpense = $row['OtherExpense'];

                                 $formatedMonth = date("M-Y", strtotime($Month));

                                 $Expenditure = $EmployeeExpense + $InfraExpense + $OtherExpense;

                                 $Absorption_Ratio = ($Revanue / $Expenditure )*100;

                                 echo "['$formatedMonth',$Absorption_Ratio],";
                             }
                             ?>

I am getting output like below image 

 

This is displaying the month only where there is data available. 

How can i change it to display all the months on haxis

[Barand]

35 minutes ago, Senthilkumar said:

This is displaying the month only where there is data available.

Of course it is! How can you expect the results to contain data that isn't there?

You can attack the problem from the input end or the output end of the process.

• Input method
  Create a temporary table `tempdate` (id , month) with 12 rows - one for each month - and left join the subqueries to this.
• Output method
  create an array with key for each month and store your result's totals in this array. Create chart from the array data.

NOTE: If your Month column is datetime time (which it appears to be as you reformat using strtotime) your queries are are grouping and joining by day and not by month (unless you are always storing the first day of the month, which we can't see)

[Senthilkumar]

Dear Brand,

I have created the separeate table for month and changed the query with left join

 <?php

                             $query = "SELECT mnth.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM 
                                ( select Month from dbms.month) mnth 
                                LEFT JOIN ( select Month, sum(Revanue) as Revanue  from dbms.revenue_data where dealerid = '$Emp_No' AND Status = '1' group by Month ) revenue USING(Month)
                                LEFT JOIN ( select Month, sum(Total) as EmployeeExpense  from dbms.employeeexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) EmployeeExpense USING(Month)
                                LEFT JOIN ( select Month, sum(Total) as InfraExpense  from dbms.infrastructure_expense where dealerid = '$Emp_No' AND Status = '1' group by Month ) InfraExpense USING(Month)
                                LEFT JOIN ( select Month, sum(Total) as OtherExpense  from dbms.otherexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) OtherExpense USING(Month);";

                                 $exec = mysqli_query($conn, $query);
                                 while ($row = mysqli_fetch_array($exec)) {
                                     $Month = $row['Month'];
                                     $Revanue = $row['Revanue'];
                                     $EmployeeExpense= $row['EmployeeExpense'];
                                     $InfraExpense = $row['InfraExpense'];
                                     $OtherExpense = $row['OtherExpense'];

                                     $formatedMonth = date("M-Y", strtotime($Month));

                                     $Expenditure = $EmployeeExpense + $InfraExpense + $OtherExpense;

                                     if ($Expenditure != 0 && $Revanue != 0) {
                                         $Absorption_Ratio = ($Revanue / $Expenditure) * 100;
                                     }else{
                                         $Absorption_Ratio = 0;
                                     }
                                     echo "['$formatedMonth',$Absorption_Ratio],";
                                 }
                             ?>

I am getting 12 columns on the graph. For testing i updated the datas for the month of Jan, Feb, Mar and Jun. My grap the month is displaying Jan,Feb,Mar,Jun,Apr,May,Jul,Aug,Sep,Oct,Nov,Dec

Can you please tell me how to correct this

[Barand]

If your Month table looks something like this

+-----+--------------+
| id  |  Month       |
+-----+--------------+
|  1  | Jan-23       |
|  2  | Feb-23       |
|  3  | Mar-23       |
|  4  | Apr-23       |
|  5  | May-23       |
|  6  | Jun-23       |
|  7  | Jul-23       |
|  8  | Aug-23       |
|  9  | Sep-23       |
| 10  | Oct-23       |
| 11  | Nov-23       |
| 12  | Dec-23       |
+-----+--------------+

then you can ORDER BY mnth.id

How are you storing Month in those tables?

Do you have a separate column for actual transaction date?

[Senthilkumar]

My table is 

Where to use ORDER BY mnth.id on query

[Barand]

At the end to order the results.

[Senthilkumar]

I tried the end of this query

But i am getting error of Error Code: 1054. Unknown column 'mnth.id' in 'order clause'

[Barand]

That's because you aren't selecting the id column in the mnth subquery.

You can just use the FROM dbms.Month instead of that first subquery.

[Senthilkumar]

I didnt noticed that. Now it is working properly

SELECT mnth.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM 
( select id, Month from dbms.month ) mnth 
LEFT JOIN ( select  Month, sum(Revanue) as Revanue  from dbms.revenue_data where dealerid = '81019218' AND Status = '1' group by Month ) 
revenue USING(Month)
LEFT JOIN ( select Month, sum(Total) as EmployeeExpense  from dbms.employeeexpense where dealerid = '81019218' AND Status = '1' group by Month ) 
EmployeeExpense USING(Month)
LEFT JOIN ( select Month, sum(Total) as InfraExpense  from dbms.infrastructure_expense where dealerid = '81019218' AND Status = '1' group by Month ) 
InfraExpense USING(Month)
LEFT JOIN ( select Month, sum(Total) as OtherExpense  from dbms.otherexpense where dealerid = '81019218' AND Status = '1' group by Month ) 
OtherExpense USING(Month) ORDER BY mnth.id;

 

 

 

I need one suggession from you. In month table we pre defined the year. How to do this for next year. Is ther any other way to capure the current year on this table

[Barand]

You answer my earlier questions about how you are storing the month/dates and I'll answer yours.

[Senthilkumar]

I  enter the month directly on the table by my self for checking the condition

[Barand]

2 hours ago, Barand said:

How are you storing Month in those tables?

Do you have a separate column for actual transaction date?

What format?

Show sample records. (This is like pulling teeth)

[Senthilkumar]

InfraExpense Table

Revenue Table

Employee Expense Table

Other Expense

[Senthilkumar]

Dear Barand,

 

Pls suggest me

[Barand]

So your tables don't store the actual date that the expense (or other transaction) occured?

[Senthilkumar]

It will store the month only

[Barand]

Here's what I'd suggest then.

Your new "Month" table will contain the month only (Jan, Feb etc with no year, we'll always assume the current year).  Your query becomes

SELECT mnth.Month
     , Revanue
     , EmployeeExpense
     , InfraExpense
     , OtherExpense 
FROM 
    (
        select id
             , concat(month, '-', YEAR(CURDATE())) as Month
        from dbms.month
    ) mnth 
LEFT JOIN 
    ( 
        select  Month
             , sum(Revanue) as Revanue 
        from dbms.revenue_data 
        where dealerid = '81019218' AND Status = '1' 
        group by Month 
    ) revenue USING(Month)
LEFT JOIN 
    (
        select Month
             , sum(Total) as EmployeeExpense  
        from dbms.employeeexpense 
        where dealerid = '81019218' AND Status = '1' 
        group by Month 
    ) EmployeeExpense USING(Month)
LEFT JOIN 
    ( 
        select Month
             , sum(Total) as InfraExpense  
        from dbms.infrastructure_expense 
        where dealerid = '81019218' AND Status = '1' 
        group by Month 
    ) InfraExpense USING(Month)
LEFT JOIN 
    ( 
        select Month
             , sum(Total) as OtherExpense  
        from dbms.otherexpense 
        where dealerid = '81019218' AND Status = '1' 
        group by Month 
    ) OtherExpense USING(Month) 
ORDER BY mnth.id;

 

[Senthilkumar]

Dear Barand,

Thanks for your suggession. It is working now. 

My final query is 

SELECT mnth.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM 
( select id, concat(YEAR(CURDATE()), '-', month) as Month from dbms.month ) mnth 
LEFT JOIN ( select  Month, sum(Revanue) as Revanue  from dbms.revenue_data where dealerid = '81019218' AND Status = '1' group by Month ) revenue USING(Month)
LEFT JOIN ( select Month, sum(Total) as EmployeeExpense  from dbms.employeeexpense where dealerid = '81019218' AND Status = '1' group by Month ) 
EmployeeExpense USING(Month)
LEFT JOIN ( select Month, sum(Total) as InfraExpense  from dbms.infrastructure_expense where dealerid = '81019218' AND Status = '1' group by Month ) 
InfraExpense USING(Month)
LEFT JOIN ( select Month, sum(Total) as OtherExpense  from dbms.otherexpense where dealerid = '81019218' AND Status = '1' group by Month ) 
OtherExpense USING(Month) ORDER BY mnth.id;

And table is