CODEWITHSUNDEEP
phparraysdatedefault-valuecontiguous
deviloper
deviloper

Reputation: 7240

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

Upvotes: 2

Views: 585

Answers (4)

mickmackusa
mickmackusa

Reputation: 48031

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

Upvotes: 0

Andreas
Andreas

Reputation: 23968

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

Upvotes: 2

RomanPerekhrest
RomanPerekhrest

Reputation: 92884

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

Upvotes: 3

M0ns1f
M0ns1f

Reputation: 2725

Using an if statement try this :

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

Upvotes: -1

Related Questions