PHP：在数组中添加缺少的日期

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.

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

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

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

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