phparraysdatedefault-valuecontiguous

Push rows with default values into an array containing dates so there are no gaps between dates


I have an array of rows with date values, but not all dates are guaranteed between the first and last row. I need to add rows so that every date has a row (a default value should be applied for the secondary column value).

Sample input:

$array = [
    ['date' => '2017-09-01', 'total' => 4],
    ['date' => '2017-09-03', 'total' => 6],
    ['date' => '2017-09-04', 'total' => 1],
    ['date' => '2017-09-05', 'total' => 3],
    ['date' => '2017-09-09', 'total' => 5]
];

I want to fill the date even if my query has no records on that date! How can I add the missing date index to the array. The dates are to be sequential/contiguous.

Desired output:

[
    ['date' => '2017-09-01', 'total' => 4],
    ['date' => '2017-09-02', 'total' => 0],
    ['date' => '2017-09-03', 'total' => 6],
    ['date' => '2017-09-04', 'total' => 1],
    ['date' => '2017-09-05', 'total' => 3],
    ['date' => '2017-09-06', 'total' => 0],
    ['date' => '2017-09-07', 'total' => 0],
    ['date' => '2017-09-08', 'total' => 0],
    ['date' => '2017-09-09', 'total' => 5]
]

Solution

  • Extended solution with DateTime object, array_map and range functions:

    $arr = [
            ['date' => '2017-09-01', 'total' => 4],
            ['date' => '2017-09-07', 'total' => 6],
            ['date' => '2017-09-09', 'total' => 7]
    ];
    
    $result = [];
    foreach ($arr as $k => $item) {
        $d = new DateTime($item['date']);
        $result[] = $item;
        if (isset($arr[$k+1])) {
            $diff = (new DateTime($arr[$k+1]['date']))->diff($d)->days;
            if ($diff > 1) {
                $result = array_merge($result , array_map(function($v) use($d){
                    $d_copy = clone $d;
                    return [
                        'date' => $d_copy->add(new DateInterval('P' . $v. 'D'))->format('Y-m-d'),
                        'total' => 0
                    ];
                }, range(1, $diff-1)));
            }
        }
    }
    
    print_r($result);
    

    The output:

    Array
    (
        [0] => Array
            (
                [date] => 2017-09-01
                [total] => 4
            )
    
        [1] => Array
            (
                [date] => 2017-09-02
                [total] => 0
            )
    
        [2] => Array
            (
                [date] => 2017-09-03
                [total] => 0
            )
    
        [3] => Array
            (
                [date] => 2017-09-04
                [total] => 0
            )
    
        [4] => Array
            (
                [date] => 2017-09-05
                [total] => 0
            )
    
        [5] => Array
            (
                [date] => 2017-09-06
                [total] => 0
            )
    
        [6] => Array
            (
                [date] => 2017-09-07
                [total] => 6
            )
    
        [7] => Array
            (
                [date] => 2017-09-08
                [total] => 0
            )
    
        [8] => Array
            (
                [date] => 2017-09-09
                [total] => 7
            )
    )