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

Question

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]
]

RomanPerekhrest · Accepted Answer · 2017-09-23 14:49:48Z

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
        )
)

Andreas · Accepted Answer · 2017-09-23 15:22:19Z

Here I create new arrays as reference.
One for all dates from first item in your array to last (range).
And one array with only the dates from your array so that I can search it.
This should handle more than one missing date and multiple missing dates.

$arr = array (
  0=>array(
    'date'=>'2017-09-01',
    'total'=>4
  ),
  1=>array(
    'date'=>'2017-09-07',
    'total'=>6
  )
);
// array with only dates to search in
$dates = array_column($arr, "date"); 

// Create an array with all dates from first item to last item
$start = new DateTime($arr[0]["date"]);
$end = new DateTime(end($arr)["date"]);

$range = new DatePeriod($start, new DateInterval('P1D'), $end);
// $range is now all dates from start to end minus last one.  

// Loop through the range 
foreach($range as $date){
    //See if the current date exist is first array
    $find = array_search($date->format("Y-m-d"), $dates);
    If($find !== false){
         $result[] = $arr[$find]; // if it does copy it to result array
    }Else{
         // If not add it and create a total = 0
         $result[] = array('date' => $date->format("Y-m-d"), 'total' => 0);
    }
}
// Since the loop does not loop all dates we need to add the last item to result.
$result[] = end($arr);
Var_dump($result);

https://3v4l.org/Hf73Z
Edit; forgot date ->format()

mickmackusa · Accepted Answer · 2022-09-23 12:07:18Z

0

Keep track of the previous row's date and the current row's date while you iterate. With every iteration, try to push missing rows with default values. If there is no gap, then there is nothing to push but the current row's data.

Code: (Demo)

$result = [];
$lastDate = null;
foreach ($array as $row) {
    if ($lastDate) {
        foreach (
            new DatePeriod(
                new DateTime("$lastDate +1 day"),
                new DateInterval('P1D'),
                new DateTime($row['date'])
            )
            as $obj
         ) {
            $result[] = ['date' => $obj->format('Y-m-d'), 'total' => 0];
        }
    }
    $result[] = $row;
    $lastDate = $row['date'];
}
var_export($result);

answered Sep 23, 2022 at 12:07

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

Comments

M0ns1f · Accepted Answer · 2017-09-23 14:06:31Z

-1

Using an if statement try this :

foreach($array as $ar){
if(!$ar["date"]){
 $ar["date"] = date("j- n- Y"); ;
 }
}

answered Sep 23, 2017 at 14:06

M0ns1f

2,7113 gold badges18 silver badges25 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related