list most comment nodes in period of time only
Last modified: December 19, 2007 - 04:00
here's simple php code to list most comment nodes in certain period of time. This example use to list most comment node in the past week only.
<?php
//it's unix timestamp value. within past week can be translated to 60 x 60 x 24 x 7 = 604800 seconds
// more info about unix timesamp http://en.wikipedia.org/wiki/Unix_time
$period = 604800;
//limit node to list
$limit = 5;
$sql = "SELECT node.nid, node_comment_statistics.comment_count AS comment_count, node.created, node.title FROM {node} node LEFT JOIN {node_comment_statistics} node_comment_statistics ON node.nid = node_comment_statistics.nid WHERE node.type='story' AND node.created > (UNIX_TIMESTAMP() - ".$period.") ORDER BY comment_count DESC, node.created DESC LIMIT ".$limit;
$result = db_query($sql);
if (db_num_rows($result)) {
$most_comment = '<ul>';
//loop each node
while ($node = db_fetch_object($result)) {
$link = 'node/'.$node->nid;
$most_comment .= '<li>'.l($node->title,$link).'</li>';
}
$most_comment .= '</ul>';
}
echo $most_comment;
?>you can modify sql slightly to have different queery, i.e: between specific period time, exact date, etc by changing this part (UNIX_TIMESTAMP() - ".$period.")
or alternatively, you can use views with date range filter