• Registered: 2005-01-23
  • Posts: 205

Topic: Special Query

How can I have au query (Mysql 4.1) which list the top ten days (or week) where the forum has been the most active ?

Any idea, I'm stuck ...

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Special Query

err, I think the way to do it isn't to look at old posts but to look at new ones starting from when you put the feature in.
So, have a cron job that runs once a day. It gets the number of posts posted in the past day, compares it to the top 10, updates the list if necessary. Much eaiser than finding the 10 top days for a lot of pre-existing data

  • Registered: 2005-01-23
  • Posts: 205

Re: Special Query

May be, but is not for a daily use only once each month io order to get a trend and see if my forum is more active now than before using punBB. What I'm lloking for is "just" the query ...

4 Reply by fpouget (edited by fpouget 2006-06-19 22:48)

  • Registered: 2005-01-23
  • Posts: 205

Re: Special Query

Find it ! Hope it could be helpfull

By day

SELECT count(*) as nb, DATE_FORMAT(FROM_UNIXTIME(posted), '%d-%m-%Y') as post from punbbposts group by post order by nb desc

by Week

SELECT count(*) as nb, DATE_FORMAT(FROM_UNIXTIME(posted), '%u-%Y') as post from punbbposts group by post order by nb desc
  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Special Query

Aha, I forgot about that tongue