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