1 Topic by vnpenguin (edited by vnpenguin 2005-08-20 21:44)

  • From: VnOSS
  • Registered: 2004-09-04
  • Posts: 121

Topic: SQL query question

Hi,
I'm trying to make some statistics of our forum. For example : top10 of poster in current week.
This is the code SQL I have tried:

$num = 10;
$condition = 'WHERE WEEK(FROM_UNIXTIME(posted,"%Y-%m-%d"),3)=WEEK(CURDATE(),3)';
$result = $db->query('SELECT poster,count(*) AS numpost FROM '.$db->prefix.
              $type.' '.$condition.' GROUP BY poster ORDER BY numpost DESC LIMIT '.$num) 
              or error('Unable to get info',__FILE__, __LINE__, $db->error());

But results is not correct sad
I'm newbie in SQL. So I would like to hear your comments.
TIA.

[no signature]

2 Reply by zaher

  • zaher
  • Senior Member
  • Offline
  • From: Syria, Damascus
  • Registered: 2004-02-16
  • Posts: 773

Re: SQL query question

try change the where to

where posted >  (UNIX_TIMESTAMP() - 604800)

60*60*24*7 = 604800

I am not test because my forum not have enough posts

If your people come crazy, you will not need to your mind any more.
  • From: VnOSS
  • Registered: 2004-09-04
  • Posts: 121

Re: SQL query question

Thank you. I'll try it now.

[no signature]