php - Filling Gaps in Dates Returned from Database - pure SQL solution possible?

Question

I have this query:

SELECT COUNT(*) as clicks, DATE_FORMAT(FROM_UNIXTIME(click_date), '%w %M %Y') as point 
FROM tracking 
WHERE click_date < $end_date AND click_date > $start_date 
GROUP BY DAY(FROM_UNIXTIME(click_date))

Where $start_date is two weeks ago and $end_date is today's date.

I am trying find all clicks made each day for a particular date range. I also want to include days where there has been no clicks. Since naturally there isn't an entry for these in my database I need to include them some how, how can I best do this whilst showing all dates from start date to end date. This what I currently have, lots of gaps for this two week date range.

Array
(
    [0] => Array
        (
            [clicks] => 17
            [point] => 0 February 2011
        )

    [1] => Array
        (
            [clicks] => 3
            [point] => 1 February 2011
        )

    [2] => Array
        (
            [clicks] => 14
            [point] => 5 February 2011
        )

    [3] => Array
        (
            [clicks] => 1
            [point] => 1 February 2011
        )

    [4] => Array
        (
            [clicks] => 8
            [point] => 2 February 2011
        )

)

Can this possibly be done via a pure SQL query or do I have to use some php logic?

Btw, why do I have 0 February 2011 as my first date! Hmm, I also seem to have duplicate dates, that shouldn't happen, maybe my GROUP BY isn't working correctly?

Thanks all for any help.

Answer 1

Can this possibly be done via a pure SQL query or do I have to use some php logic?

Yes, it is better to create a Numbers table (single column N) that contains nothing but the numbers 0 to 999. It can be used for many things, not least a query like the below:

SELECT COUNT(t.click_date) as clicks,
    DATE_FORMAT(adddate($start_date, interval N day), '%d %M %Y') as point 
FROM Numbers
LEFT JOIN tracking t
    ON t.click_date >= adddate($start_date, interval N day)
    and t.click_date < adddate($start_date, interval (N+1) day)
WHERE N between 0 and datediff($start_date, $end_date)
GROUP BY N

Btw, why do I have 0 February 2011 as my first date

You're using the wrong format. It's UPPER case W not lower for day-of-week, so '%W %M %Y' or '%d %M %Y' for day-of-month. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

maybe my GROUP BY isn't working correctly?

You are using GROUP BY DAY(FROM_UNIXTIME(click_date)) note "day" not weekday, but you are displaying (or trying to) "%W" (weekday) - pick one, don't mix them.

---

EDIT: If you prefer not to materialize (create as a real table) a Numbers sequence table, you can construct one on the fly. It won't be pretty.

Note: N1, N2 and N3 below combine to give a possible range of 0-999

SELECT COUNT(t.click_date) as clicks,
    DATE_FORMAT(adddate($start_date, interval N day), '%d %M %Y') as point 
FROM (
    select N1 * 100 + N2 * 10 + N3 as N
    from (
    select 0 N1 union all select 1 union all select 2 union all
    select 3 union all select 4 union all select 5 union all
    select 6 union all select 7 union all
    select 8 union all select 9) N1
    cross join (
    select 0 N2 union all select 1 union all select 2 union all
    select 3 union all select 4 union all select 5 union all
    select 6 union all select 7 union all
    select 8 union all select 9) N2
    cross join (
    select 0 N3 union all select 1 union all select 2 union all
    select 3 union all select 4 union all select 5 union all
    select 6 union all select 7 union all
    select 8 union all select 9) N3
    ) Numbers
LEFT JOIN tracking t
    ON t.click_date >= adddate($start_date, interval N day)
    and t.click_date < adddate($start_date, interval (N+1) day)
WHERE N between 0 and datediff($start_date, $end_date)
GROUP BY N

EDIT #2: A straight Dates table

Put this in a new window in phpMyAdmin or run it as a batch. It creates a table named Dates, with every single date from day 1900-01-01 (or change in the script) to 2300-01-01 (or change).

DROP PROCEDURE IF EXISTS FillDateTable;

delimiter //
CREATE PROCEDURE FillDateTable()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  drop table if exists datetable;
  create table datetable (thedate datetime primary key, isweekday smallint);

  SET @x := date('1900-01-01');
  REPEAT 
    insert into datetable (thedate, isweekday) SELECT @x, case when dayofweek(@x) in (1,7) then 0 else 1 end;
    SET @x := date_add(@x, interval 1 day);
    UNTIL @x > date('2300-01-01') END REPEAT;
END//
delimiter ;

CALL FillDateTable;

With such a utility table, your query can be just

SELECT COUNT(t.click_date) as clicks,
    DATE_FORMAT(thedate, '%d %M %Y') as point 
FROM Dates
LEFT JOIN tracking t
    ON t.click_date >= thedate
    and t.click_date < adddate(thedate, interval 1 day)
WHERE thedate between $start_date and $end_date
GROUP BY thedate