CODEWITHSUNDEEP
phpmysqlgroup-by
Tony
Tony

Reputation: 21

Print rows that don't exist in table?

I have a query that sums rows grouped by date. Basically, it counts how many membership applications in a day and prints out the results day by day. Naturally, if there are no applications in a day, it doesn't show that row (example below 15th and 17th had no applications.....

Date         |    Applications
-----------------------------------
12/01/2010   |   44
13/01/2010   |   73
14/01/2010   |   66
16/01/2010   |   102
18/01/2010   |   12
19/01/2010   |   22

I need it to print out the date and 0 for days when there are no applications, so as no days are skipped. Any idea how to do that. I thought of joining to a table with every day of the year, but that seems overkill

The query is below

SELECT
application_date AS Date,
COUNT(*) AS Applications 
FROM members
GROUP BY ap_date

Upvotes: 2

Views: 164

Answers (4)

Vince Bowdren
Vince Bowdren

Reputation: 9208

It's not overkill to create a table just for listing all the dates in the year; it's called a helper table, and can make this query (and lots like it) very easy.

Upvotes: 0

TravisO
TravisO

Reputation: 9550

I would create a PHP array, and the array index would be a string, the date, I prefer this format YYYY-MM-DD and I would do something like this (note the key's date format is important)

// how far in the past to go
$days_in_the_past = 365;
// default to today, unix timestamp file
$start_day = time();

// the array that contains all out dates
$dates = array();

// sec * min * hours
$secs_in_a_day = 60 * 60 * 24;

// note we're going backwards here
for ( $i=0; $i <= $days_in_the_past; $i++ )
{
    $key = date('Y-M-D', ($start_day - ($secs_in_a_day * $i)));
    // this sets the format of   2010-01-21   as the key
    $dates[$key] = "";
}

$query = 'SELECT date, app FROM table';
$result = mysql_query($query);
while ( $row = mysql_fetch_assoc($result) )
{
    $key = date('Y-M-D', strtotime($row['Date']));
    $dates[] = $row['Applications'];
}

If you want the dates in order, just sort the array.

Upvotes: 0

leepowers
leepowers

Reputation: 38318

This is very similar to another question on SO. The general consensus seems to be:

  1. Fill in the blank dates in the application layer of the program.
  2. Create a temporary table, fill it with dates, and join on the temp table.

The best option is #1 - it's the least convoluted and should have the lowest overhead.

Upvotes: 1

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798814

Select on the date range in question. Then iterate through the date range, looking to see if the current row matches. If it doesn't then output a 0. If it does then output the appropriate number and advance to the next row.

Upvotes: 0

Related Questions