PHP: Add missing dates in array

Question

I'm running the following query on my table:

SELECT DISTINCT(date(dateAdded)) AS dateAdded, count(*) AS count FROM clients WHERE (dateAdded BETWEEN '2012-06-15' AND '2012-06-30') GROUP BY dateAdded ORDER BY dateAdded ASC

That returns something like this:

2012-06-17 ¦ 5 
2012-06-19 ¦ 2 
2012-06-26 ¦ 3 
2012-06-30 ¦ 2

I need to be able to fill in any missing dates in the date range like so:

2012-06-15 ¦ 0 
2012-06-16 ¦ 0 
2012-06-17 ¦ 5 <--
2012-06-18 ¦ 0 
2012-06-19 ¦ 2 <--
2012-06-20 ¦ 0 
2012-06-21 ¦ 0 
2012-06-22 ¦ 0 
2012-06-23 ¦ 0 
2012-06-24 ¦ 0 
2012-06-25 ¦ 0 
2012-06-26 ¦ 3 <--
2012-06-27 ¦ 0
2012-06-28 ¦ 0 
2012-06-29 ¦ 0 
2012-06-30 ¦ 2 <--

I'd like to do this using a PHP loop of some sort, if possible. Any help would be greatly appreciated.

Possibly dupe of http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range — giorgio79, Aug 22 '12 at 20:26

score 3 · Answer 1 · answered Jul 01 '12 at 03:37

I like using a date iterator for this kind of problems:

class DateRangeIterator implements Iterator
{
      private $from;
      private $to;
      private $format;
      private $interval;

      private $current;
      private $key;

      function __construct($from, $to, $format = 'Y-m-d', $interval = '+1 days')
      {
            if (false === ($this->from = strtotime($from))) {
                  throw new Exception("Could not parse $from");
            }
            if (false === ($this->to = strtotime($to))) {
                  throw new Exception("Could not parse $to");
            }
            $this->format = $format;
            $this->interval = $interval;
      }

      function rewind()
      {
            $this->current = $this->from;
            $this->key = 0;
      }

      function valid()
      {
            return $this->current <= $this->to;
      }

      function next()
      {
            $this->current = strtotime($this->interval, $this->current);
            ++$this->key;
      }

      function key()
      {
            return $this->key;
      }

      function current()
      {
            return date($this->format, $this->current);
      }
}

To use it:

foreach (new DateRangeIterator('2012-04-01', '2012-04-30') as $date) {
    echo "$date\n";
}

You can customize the format in which the dates should appear as well as the interval it should increase by.

In your case you would need to store the MySQL results using the key as the array index, e.g.

[ '2012-04-01' => 'some event', '2012-04-06' => 'some other event' ];

How would I plug the results from the mySQL query into this, in order to get a string of consecutive dates along with the count (which defaults to 0 if the date doesn't exist in the mySQL result set)? — Jeremy, Jul 02 '12 at 17:22
You have to collect the database results into an array with the date as key and use isset() to determine whether to show default value or database value. — Ja͢ck, Jul 02 '12 at 18:16

score 0 · Answer 2 · edited May 23 '17 at 11:52

0

You can create an array of all dates between the two provided dates using the method from this answer and then array_merge it with the result to override any values that are set.

$empty_array = array_fill_keys(makeDateRange("2012-06-15","2012-06-30"), 0);
$result = array_merge($empty_array, $result);

edited May 23 '17 at 11:52

Community

1
1

answered Jun 30 '12 at 23:18

MDrollette

6,887
1
36
49

This looks close, but I'm not sure I follow you exactly. Could you give me a little more info on how to combine these arrays and echo them out in the manner shown in my original example? – Jeremy Jun 30 '12 at 23:47

score 0 · Answer 3 · answered Jun 30 '12 at 23:22

Hmm.. not sure if doing a loop in PHP is best practice. Why don't you modify the query to give you what you need?

If you do a left outer join between your set of dates and your table you should get what you need (except that you will have nulls instead of 0 but that's easy to take care of.

My SQL is a bit rusty, but it's probably something like this

SELECT dateAdded
FROM clients
WHERE  (dateAdded BETWEEN '2012-06-15' AND '2012-06-30') 
LEFT OUTER JOIN 
(
    SELECT DISTINCT(date(dateAdded)) AS dateAdded, count(*) AS count 
    FROM clients 
    WHERE (dateAdded BETWEEN '2012-06-15' AND '2012-06-30') 
    GROUP BY dateAdded ORDER BY dateAdded ASC
) AS mytable ON clients.dateAdded = mytable.dateAdded

This doesn't return as valid mySQL. – Jeremy Jun 30 '12 at 23:52 — Jeremy, Jun 30 '12 at 23:52

PHP: Add missing dates in array

3 Answers3