Generate monthly graph

Question

I’m putting together a monthly chart showing the sum of expenses and revenues per day, but the data shown on the chart is only the dates that exist in the database mysql. How could I display dates that don’t exist in the bank? Wanted to display by ex from day 01 until day 31 of the month.

I’ve tried to make a for and feed a array but without success, someone could help me?

Answer 1

Surely this could be solved in your application. But here is an alternative to solve 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 replace tbl_table with your table and adjust conditions according to your needs.

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

Sqlfiddle