Create zero values for missing items in foreach loop

Question

I have an application that extracts some information from mysql between two dates and returns an associative array. I am producing a graph with this information but have dates missing for the dates in the database that have no information to return. I cannot fix this on the mysql side as I only have read only access to the database.

My database method retrieves an associative array like the below:

[0] => Array
        (
            [number_of_calls] => 151
            [total_call_time] => 00:01:30
            [average_call] => 00:02:00
            [DATE(calldate)] => 2016-03-18
            [direction] => outbound
        )

What I am hoping to do is create a daterange from my form like below:

    //create data range array
    $begin = new DateTime( $datefrom );
    $end = new DateTime( $dateto );
    $end = $end->modify( '+1 day' );
    $interval = new DateInterval('P1D');
    $daterange = new DatePeriod($begin, $interval ,$end);

And then use a foreach loop to iterate through the selected dates of the daterange and pull the value from the associative array where the dates match and if not insert zero values like below:

        [number_of_calls] => 0
        [total_call_time] => 00:00:00
        [average_call] => 00:00:00

I also need the final array to end up in date order. Can anybody help me with this please?

Maybe you can set the column on your database to NOT NULL so the default value of the column will be zero for integer column type and for the date timestamp. — DevBert
– DevBert, Commented Apr 19, 2016 at 8:06
I want to leave the source database as it is and correct this in php as id like to connect my web app to multiple similar systems. — Ross
– Ross, Commented Apr 19, 2016 at 8:10

Andrew · Accepted Answer · 2016-04-19 16:19:16Z

1

You can transform your $result array to use DATE(calldate) as keys.

$keys = [];
foreach ($result as $item) {
    $keys[] = $item['DATE(calldate)'];
}
$result = array_combine($keys, $result);

And your array will look like that:

[2016-03-18] => Array
    (
        [number_of_calls] => 151
        [total_call_time] => 00:01:30
        [average_call] => 00:02:00
        [DATE(calldate)] => 2016-03-18
        [direction] => outbound
    )

And you can check if date is presented by simple command:

$key = $datevalue->format('Y-m-d');
if (isset($result[$key])) {
    // date exists, use it
} else {
    // date not exists, create empty value
}

answered Apr 19, 2016 at 16:19

Andrew

1,86615 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ross · Accepted Answer · 2016-04-19 10:48:09Z

This is what I ended up doing

// helper function to recursively search array
        function recursive_array_search($needle,$haystack) {
            foreach($haystack as $key=>$value) {
                $current_key=$key;
                if($needle===$value OR (is_array($value) && recursive_array_search($needle,$value) !== false)) {
                    return $current_key;
                }
            }
            return false;
        }


        foreach ($daterange as $datevalue) {

          $key = recursive_array_search($datevalue->format('Y-m-d'), $result);

          // if date is found write values from data to output array
          if ($key !== False) {
          $output_array[] = array("number_of_calls" => $result[$key]['number_of_calls'],
                "total_call_time" => $result[$key]['total_call_time'],
                "average_call" => $result[$key]['average_call'],
                "DATE(calldate)" => $datevalue->format('Y-m-d'),
                 "direction" => "outbound" );
               }
          // else write zeros
          else  {
            $output_array[] = array("number_of_calls" => "0",
                  "total_call_time" => "00:00:00",
                  "average_call" => "00:00:00",
                  "DATE(calldate)" => $datevalue->format('Y-m-d'),
                   "direction" => "outbound" );
          }

}

        $this->chart_data = $output_array;
    }

Collectives™ on Stack Overflow

Create zero values for missing items in foreach loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related