Generate monthly chart

0

I'm putting together a monthly chart where the sum of expenses and revenue per day is displayed, but the data displayed in the chart is only the dates that exist in the mysql database. How could I display dates that do not exist in the bank? I wanted to display for ex from the 1st until the 31st of the month.

I have tried to make a for and feed a array but without success, could anyone help me?

    
asked by anonymous 03.05.2015 / 05:47

1 answer

1

Surely this could be solved in your application. But here's an alternative to solving this in the database.

This is a solution to a well-known problem.

select b.Data, a.id, a.valor 
from tbl_tabela a
right join 
(
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a)) DAY as data
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) b
  on b.data = a.data
where b.Data between '2015-05-01' and '2015-05-03'

Just substitute tbl_table, for your table and adjust the conditions according to your needs.

If performance is a problem (in the tests I did, it's not a problem at all), you can think of creating a Calendar table with the dates you need.

SQLFiddle

    
03.05.2015 / 12:26