Get threads with most replies in last two weeks?

[sebbe]

Ok I asked on vb.com but no one anwers there so I thought you guys know ho to go about this!? :cool:
____________________

I'm writing a script on a page outside of vb. I want it to print the 20 latest threads from a few specific forums that has got most replies in the last two weeks.
I'm currently in the learning state of MySQL and I think this could be a quite easy query for you pros.

Appreciate any effort to help, here's what I got so far :cool:


$currentudate = date("U");
$twoweeks = $currentudate - '1209600';
$query = "SELECT title, postusername, threadid, postuserid, dateline, from_unixtime(dateline), forumid, replycount, views, from_unixtime(lastpost)
FROM thread
WHERE dateline >= $twoweeks
ORDER BY replycount
DESC LIMIT 20";

I know this print the threads that were created in the last two weeks but as I said above, I want 20 threads which has got the most replies in the last two weeks.
Nvm I SELECT so many columns, I need them for something else.

Thanks!

[Xenon]

hmm this query should generally work:


SELECT thread.*
FROM post
INNER JOIN thread USING (threadid)
WHERE thread.forumid IN (xx,yy,zz)
AND post.dateline >= $twoweeks
GROUP BY post.threadid
ORDER BY replycount DESC
LIMIT 20

[sebbe]

Thanks a lot for your help, it's almost what I want!

But when I use "ORDER BY replycount", the threads with VERY many posts show up in the top of the list, even though they haven't got the most new replies within the last two weeks.
It seems like threads (which has many replies) get onto this list that has only got a FEW new posts in the last two weeks but is put in the top because of their high replycount.
I hope you understand what I'm trying to say here!? :cool:

Again thanks for helping!

[Xenon]

ah, i see what you mean!

hmm, try that one instead:
SELECT count(post.postid) AS newpostcount, thread.*
FROM post
INNER JOIN thread USING (threadid)
WHERE thread.forumid IN (xx,yy,zz)
AND post.dateline >= $twoweeks
GROUP BY post.threadid
ORDER BY newpostcount DESC
LIMIT 20

[sebbe]

Thanks Xenon, it works perfect.

Just on a side note, I'm also pulling out a line of text from the first post in each thread to display, I know how to do it but is there anyway to remove all words that begin with [ ? cause right now it looks like a mess if someone for example has written "Hey this is my thread and I use this color for all the text". Also when the post is beginning with an image "[img]http://sjfiojdsifsopf.fsdfjsifjosdjfisdf.dfsd.gif[/ img]" this problem occurs.

Any help how to just remove the stuff inside of BB [] brackets would be greatly appreciated, I've tried numerous different ways but haven't come up with any that works!

Thanks again! :)

Nevermind, I figured it out myself!
Thanks for all help!

[Xenon]

strip_bbcode? ^^

[sebbe]

I didn't even know there was a function called strip_bbcode, instead I used preg_replace("/\[.*?]/s", "", $firstpost).
It's good because it wont remove what's inside the bb codes, for example if someone writes something in bold, it's still there. :)

[Xenon]

erm, isn't that what strip_bbcode is supposed to do?

don't mix it up with strip_quote ;)

[sebbe]

Ah I really dunno man, all I know is that it works as it should! :)