CODEWITHSUNDEEP
phpmysqlarrays
Ross
Ross

Reputation: 107

Create zero values for missing items in foreach loop

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?

Upvotes: 0

Views: 200

Answers (2)

Andrew
Andrew

Reputation: 1866

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
}

Upvotes: 1

Ross
Ross

Reputation: 107

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

Upvotes: 0

Related Questions