mysql and dayofyear()

[willg825]

I'm trying to run some basic date statistics on users that have registered on my site; basically, to get the number of users each day.

I've been using: 'select count(id), dayofyear(created_on) from users group by dayofyear(created_on) ' and its been working fine. The problem is that there are certain days when no users register; currently, no row is returned, but what I really need is for a row of '0' to be returned with the appropriate dayofyear. I need this '0' datapoint for the purposes of graphing. Without it, the graph isn't scaled correctly.

The current fix is abstracting out of mysql and using php to loop through the data, make sure it is consecutive, insert 0 and day when it isnt, etc., but I'd like to have this all be part of the sql if possible.

Can anyone see a way to do this?

Appreciate it,

will

[mcavic]

I don't know of any way to get MySQL to return a consecutive list of days then not all of the days are present in the table.

But an easy way to do it in PHP might be to fill an array with 366 rows of zeros, and then use the MySQL results to replace the values that should be non-zero.

[jatar_k]

an extra table with 366 rows, grab the data you already do and dump the results into the other table, then reselect.

it can be a temp table if you rarely do this, if not just roll the data over the year, at the end of the year then write it to a static file for the archives

just a thought

[willg825]

ok - i like the idea of a table with 366 rows that stores the data. thanks for your responses

-will

[omoutop]

Just a word of caution: carefull on how to name your table fields.
One way would be to name them as "day1", "day2". If you follow this approach, take note that in php the counter always start at 0 - so Jan 1st is day0.

Of cource you can name them in an alternate way, for example jan01, jan02, etc.

Your script, your choise :)