>I have been going fine in a Web Stats program until now.
>
>How can I figure out which day has had the most records(ie visitors)
>inserted?
>
>There is a time column which has unix timestamp of when the record was
>inserted.
>
>The best I can think of currently is:
>To use a while loop to repeatedly query the DB.
>then use an if statement to replace two variables(UNIX timestamp and number
>of visitors) if the value returned is higher.
Almost any time you do a while loop to repeatedly query the DB, you've done
something wrong. :-)
Either you are missing an SQL function that will do what you want, or you
just designed the db schema wrong and the application wrong in the first
place.
Fortunately, in this case, you're just missing an SQL function.
Dig through the manual of your database (you didn't say which one) in the
Date/Time functions section, and see if you can find one that will extract
the year and month from a timestamp.
You'll end up writing something not unlike this:
select count(*) as monthly_views
from visitors
group by extract('year', whattime), extract('month', whattime)
order by monthly_view desc
limit 1
The GROUP BY part is the "magic" -- It will do any "aggregate" function
(count, average, sum) broken down by whatever fields are listed. In this
case, I broke it down by year & month.
If you wanted the most popular month over the last five years, it would be
something not unlike:
select count(*) as monthly_views
from visitors
where whattime + '@ 5 years' >= now()
group by extract('month', whattime)
order by monthly_view desc
limit 1
Disclaimers:
1. 'extract' is probably not the right function name. You'll have to look
that up.
2. The "whattime + '@ 5 years'" works just nifty in PostgreSQL. You have to
type more than that in MySQL, I think... MySQL date arithmetic always gives
me a pain.
Always dig for a way to do it in SQL first.
--
Like Music? http://l-i-e.com/artists.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
