Top 10 in a time interval

[Ozz]

I got a mysql table like these. I wrote only necessary fields.

TABLE WEBLOG
post_id | t_stamp |month | year | day | c_hits
3 1034407262 3 2003 15 251
5 1034409443 3 2003 15 | 128
6 1034409985 3 2003 17 | 84
...

t_stamp column as unix date and also there are month,year and day columns.
My aim is to make a page shows top 10 hits on daily, weekly, monthly and

yearly base.
I want to arrange post_ids ascending in order to c_hits in a time interval.
Today's most read , this month's most read ETC..
I need mysql queries to do these.

Thanks by now

[jkohns]

what is the c_hits field? It looks like a count of hits for each post, in which case- is it a count of hits daily/weekly/monthly/hourly? or is an entry made for every hit to every post?

[leatherback]

Not sure wether this is what you are looking for but something along the lines of:

Select * from weblog where time > $maxtime order by c_hits desc limit 10

where $maxtime is the time from where you want to display the data

BUT if you have your c_hits increment every time a page is loaded this will not work! there is no way to track that. (Unless you have a new record for each time a page is loaded.

What I think you want to do is more along the lines of:

Select *, count(id) as count from weblog where time > $maxtime group by ID order by count desc limit 10

This has not been tested. I am not completely sure of the order in which you should place the statements. You could try, or have a look at the mysql website for that.

J