Fill array with zeros

Question

I'm running a mysql query and the resulting array is something like this, that changes every month:

Array(    
[0] => Array
    (
        [day] => 2
        [count] => 10
    )

[1] => Array
    (
        [day] => 4
        [count] => 39
    )

[2] => Array
    (
        [day] => 5
        [count] => 51
    )
    )

I'd like to add days so I get 31 days, the ones added would be filled with 0, like this:

Array(    
[0] => Array
    (
        [day] => 1
        [count] => 0
    )

[1] => Array
    (
        [day] => 2
        [count] => 10
    )

[2] => Array
    (
        [day] => 3
        [count] => 0
    )

[3] => Array
    (
        [day] => 4
        [count] => 39
    )
    )

I'd like to fill the array with 31 days, using the days and count data that are already there... like in the second example... the days 1 and 3 wanst there... so I added them with the count value 0... in order... 1 ~ 31 days

The query is pretty simple:

SELECT day(`dates`) day, count(`dates`) count FROM `calls` where month(dates) = 7

so each month has different amount of "days", some months there's no calls.

Answer 1

I would recommend creating a calendar table in your database to hold all dates. Then you can select from this table and left join your other data to get a total count per day. This article is a good place to start to create a calendar table and this stackoverflow post contains a similar question and answer to your problem.

There are php solutions as well such as iterating a date range using the php built in DateTime and DateInterval Classes as mentioned in this stackoverflow question