WordPress Popular Comments

gistfile1.aw

<?php
/**
 * Display a list of the 10 most commented posts (WordPress)
 * @author Corey Brown https://github.com/coreyweb
 * @author Aaron Collegeman: https://github.com/collegeman
 * @author Joey Blake: https://github.com/joeyblake
 *
 * Rules:
 * - show a list of 10 posts
 * - published any time
 * - with the most comments in the last 7 days
*/
?>

<h3>Most Discussed This Week</h3>
  
<?php
  // get comments based on number of comments in the timeframe and any post
  // removed the publish date window, which was this:
  // AND post_date_gmt > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1000 WEEK)         
  
  $result = $wpdb->get_results("
    SELECT DISTINCT ID, comment_count, post_title, count(*) AS count
    FROM {$wpdb->posts}
    JOIN {$wpdb->comments} ON (comment_post_ID = ID)
    WHERE 
      post_status = 'publish'
      AND comment_approved = '1'
      AND comment_date_gmt > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 WEEK)
    GROUP BY ID, round(UNIX_TIMESTAMP(comment_date_gmt) / 604800)
    ORDER BY count DESC, comment_count DESC
    LIMIT 20
  ");  
  
  // build sort array for comment count
  $comment_count = array();
  foreach ($result as $key => $row) {
    $comment_count[$key] = $row->comment_count;
  }

  // sort results from most to least (sort array)
  array_multisort($comment_count, SORT_DESC, $result);
  $last_id = ''; 
  $i = 1;

  foreach ($result as $post) { 
    setup_postdata($post);
    $postid = $post->ID; 
    $title = $post->post_title; 
    $commentcount = $post->comment_count; 

    if ($i == 11) { 
      break;
      }
       
    if ($commentcount != 0 && $last_id !== $postid) { 
      $i++;
      ?> 

      <a href="<?php echo get_permalink( $postid ); ?>" title="Read the article" >
        <?php if ( has_post_thumbnail() ) { ?>
          <img src="<?php vt_resize(get_the_ID(), 45, 45, true) ?>" width="45" height="45" alt="<?php echo $title ?>" title="<?php echo $title ?>" />
        <?php } ?>
          <?php echo $title ?>
        <?php endif; ?>
      </a>

      <a href="<?php echo get_permalink( $postid ); ?>#comments">
        <?php echo $commentcount ?>
      </a>

    <?php } $last_id = $postid; ?>
  <?php 
  } 
?>