Retrieving mysql statistical data using PHP regular intervals and including null data

Question

So I am trying to build out some nice stats displays for my app. I can do this because I keep hit stats in a table. It simply tracks the hits plus some other nice data and the time it occurs. I can query the db to show me how many hits occurred on a particular day or on each day for the past x days as in the code below. However the code below only returns dates for which there is data. I would like to show the last 30 days of hits regardless of whether a day has a hit or not. Thoughts?

SELECT DATE(time) AS theday, COUNT( * ) AS thecount
FROM stats
WHERE time <= curdate( )
AND time >= DATE_SUB( curdate(), INTERVAL 30 DAY )
GROUP BY theday ORDER BY time DESC

Produces

theday  thecount
2011-11-22  5
2011-11-21  9
2011-11-18  10
2011-11-16  1
2011-11-11  2
2011-11-10  15
2011-11-09  2
2011-10-26  1
2011-10-24  6

As you can see it skips dates with no results. I understand why this is, as there are no rows with those dates in the db. I am wondering how I would produce a query that works almost like the above but has all the dates for said interval. IE: the last 30 days.

Obviously, if there is no hit on that date in the table, a select won't return anything — Pranav Hosangadi, Nov 24 '11 at 04:36

score 2 · Accepted Answer · edited May 23 '17 at 10:34

You have 3 options:

try to iterate the dates in the application logic (php)
generate a (temporary) table filled with the dates you need and left join with it
use mysql stored procedure solution like in this answer

Example for app logic implementation:

<?php

    date_default_timezone_set('Europe/Paris');

    $startdate = strtotime('2011-11-01 00:00:01');
    $days = 60;

    $found_data = array( // this is generated by 1 mysql query
        array('date_field' => '2011-11-02', 'count' => 5),
        array('date_field' => '2011-11-03', 'count' => 1),
        array('date_field' => '2011-11-04', 'count' => 6),
        array('date_field' => '2011-11-08', 'count' => 9),
        array('date_field' => '2011-11-09', 'count' => 3),
        array('date_field' => '2011-11-10', 'count' => 5),
        array('date_field' => '2011-11-12', 'count' => 1),
        array('date_field' => '2011-11-15', 'count' => 1),
        array('date_field' => '2011-11-18', 'count' => 4),
        array('date_field' => '2011-11-21', 'count' => 9),
        array('date_field' => '2011-11-23', 'count' => 1),
        array('date_field' => '2011-11-28', 'count' => 8),
        array('date_field' => '2011-11-30', 'count' => 6),
    );

    foreach ($found_data as $counts) { // we convert the results to a usable form, you can do this in the query, too
        $count_info[$counts['date_field']] = $counts['count'];
    }

    for ($i = 0; $i <= $days; $i++) {
        $date = date('Y-m-d', $startdate+$i*60*60*24);
        printf("%s\t%s\n", $date, array_key_exists($date, $count_info) ? $count_info[$date] : 0);
    }

?>

Thanks deejayy! I found those solutions but was hoping there was something easier. I feel like I am missing something because iterating over 180 dates and making 180 queries seems heavy. — stueynet, Nov 24 '11 at 22:26
1 query, and 1 iteration is enough. The iteration in php takes all the dates you need, and the query returns all the rows, which has data. In the iteration, you print out where you found data, and print 0 if you not. — deejayy, Nov 25 '11 at 08:55
Big points for you if you show a code example. BIG! You have an array with missing dates, plus your genius function for filling in the missing ones with zeroes. — stueynet, Nov 25 '11 at 13:23

Retrieving mysql statistical data using PHP regular intervals and including null data

1 Answers1