SQL Problems

[merk]

Lets hope someone can help me!

Basically, i am trying to select from the database, the top 10 most posts, with the most replies, which expire after 7 days regardless, ie, only posts before 7 days ago are shown, easy you might say? well i dont actually want it 7 days from the start of the THREAD, i want it 7 days from the LAST reply.

However, for the life of me, i cant think of a way to do it.

The code i tried->

$replycountquery = "SELECT thread.*, post.*";
$replycountquery .= " FROM thread,post";
$replycountquery .= " WHERE thread.threadid=post.threadid";
$replycountquery .= " AND threadid=$newsforums";
$replycountquery .= " AND post.dateline > " . $replycountexpire;
$replycountquery .= " GROUP BY thread.threadid";
$replycountquery .= " ORDER BY threadid.replycount DESC";
$replycountquery .= " LIMIT $newsitems";


$replycountexpire, is simply a mktime function that takes the current server timestamp and taking 7 days worth off.

code ive also tried in testing, this works, however has no date stuff, as no post table is introduced.


$replycountquery = "SELECT thread.*";
$replycountquery .= " FROM thread";
$replycountquery .= " WHERE forumid = $newsforums";
$replycountquery .= " ORDER BY thread.replycount DESC";
$replycountquery .= " LIMIT $newsitems";


Id really appreicate some help!

I just wish i had more SQL books! :(

[Wayne Luke]

Try:

SELECT thread.*, post.* FROM thread
LEFT JOIN post ON (threadid)
WHERE thread.threadid=post.threadid
AND forumid=$newsforums
AND post.dateline > $replycountexpire
GROUP BY thread.threadid";
ORDER BY threadid.replycount DESC LIMIT 0,$newsitems";


You had "AND threadid=$newsforums" which most likely resulted in no matches within the time specified.