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.

I want to create a new array using the one I have... with 31 days... sometimes my array has 20 days, the ones that are not in there should be added with the count value 0... just like the example... the days 1 and 3 wasnt in the first one, so in the second one they are... with the count value 0 — Edgar, Jul 09 '15 at 03:59
Add the SQL query to your Question please. This can be done in PHP while creating the array. — n099y, Jul 09 '15 at 04:01
I think a calendar table is what you need like suggested in the answer. — n099y, Jul 09 '15 at 04:18
Don't go using a temporary calendar table either, that will just add overhead you do not need. — n099y, Jul 09 '15 at 04:19

score 3 · Answer 1 · edited May 23 '17 at 12:31

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

Akshay Hegde · Accepted Answer · 2015-07-09T05:46:56.310

Also try this, modify range(1,10) as per your requirement

[akshay@localhost tmp]$ cat test.php 
<?php

$array=array( 
              array("day"=>2,"count"=>10),  
              array("day"=>4,"count"=>39),
              array("day"=>5,"count"=>51)
            );


function modify_array($array,$range)
{
    $tmp = array();
    array_map(function($_) use (&$tmp){ $tmp[$_] = array("day"=>$_,"count"=>0); },$range);
    $output = array_combine( array_column($array,"day"), $array ) + $tmp;
    ksort($output);
    return array_values($output);
}

// Output - modify range(1,10) as per your wish
print_r( modify_array($array, range(1,10)) );

?>

Output

[akshay@localhost tmp]$ php test.php 
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
        )

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

    [5] => Array
        (
            [day] => 6
            [count] => 0
        )

    [6] => Array
        (
            [day] => 7
            [count] => 0
        )

    [7] => Array
        (
            [day] => 8
            [count] => 0
        )

    [8] => Array
        (
            [day] => 9
            [count] => 0
        )

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

)

-- Edit for comment--

for older version of PHP which doesn't have array_column

function modify_array($array,$range)
{
    $tmp = array(); 
    array_map(function($_) use (&$tmp){ $tmp[$_] = array("day"=>$_,"count"=>0); },$range);
    $output = array_combine( array_map(function($e){return $e["day"];}, $array), $array ) + $tmp;
    ksort($output);
    return array_values($output);
}

array_column is only for php 5.5 so I had to update mine... what a pain in the arse it was... but it workes too... I'm going to try both... — Edgar, Jul 09 '15 at 05:42
@Edgar : I edited my post have a look, which supports older version too now — Akshay Hegde, Jul 09 '15 at 05:47

But those new buttons though.. · Answer 3 · 2015-07-09T04:40:31.467

Assuming you do this for more months than July, first set your target month as a variable and get the amount of days:

$month = '7';
$daysInMonth = cal_days_in_month(CAL_GREGORIAN, $month, 2003);

Then run your query and iterate over results to build an array indexed by day:

while ($row = $query->fetch_assoc()) {
    $results[$row['day']] = $row;
}

Now you can do a simple for loop and fill in the missing pieces:

for ($i = 1; $i <= $daysInMonth; $i++) {
    if (!isset($results[$i])) {
        $results[$i] = array(
            'day' => $i,
            'count' => 0
        );
    }
}

score 0 · Answer 4 · answered Jul 09 '15 at 04:19

0

$result = your database query result;
$days = array();
for($i=0;$i<=31;$i++){
{
   foreach($result as $aresult){
      if(($aresult['day']-1)==$i){
             $days[i] = $aresult;
             break;
      }
   }
}

answered Jul 09 '15 at 04:19

tapos ghosh

2,114
23
37

I took the -1 and added `else { $days[$i] = array('day' => $i, 'count' => 0 ); }` inside the foreach and it worked, thanks – Edgar Jul 09 '15 at 04:39
1

This creates a lot of unnecessary extra looping. It might work but it's inefficient. – But those new buttons though.. Jul 09 '15 at 04:44

Fill array with zeros

4 Answers4