How to Display Top 20 Comment-ed Articles in WordPress?

Many bloggers use WordPress (The Famous and Recommended Content Management System CMS) nowadays. WordPress has a inbuilt comment system so that the readers can engage in the discussions.

The following describes the method to display the top 20 (can changed if 20 is not your lucky number) articles that are sorted by the count the comments.

First, you would need to install a wordpress Plugin that can execute PHP code in the posts or pages. There are many similar plugins and you can just pick any of them, which should do the job properly. Then, you can create a wordpress page that simply include the top.php which is the PHP script name that is going to be added.

Go to the wordpress theme root folder and create top.php and put the following script and save it, then you are done!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

  // https://helloacm.com
  global $wpdb; // use the wordpress database object
  $query = "
      select 
        count(1) as `cnt`, 
        `comment_post_ID` as `ID` 
      from 
        `$wpdb->comments` 
      where
        `comment_approved`='1'         
      group by 
        `comment_post_ID` 
      having 
        `cnt` > 0   
      order by 
        `cnt` desc 
      limit 20";
  $result = $wpdb->get_results($query);
  if ($result) {
    echo '<table><thead><tr><th>Ranking</th><th>Articles / Posts</th><th>WP Comment Count</th></tr></thead>';
    $position = 0; 
    foreach ($result as $toppost) {
      $position++;
      $query = "select `post_name` as `name`, `post_title` as `title` from `$wpdb->posts` where `ID` = '".$toppost->ID."' limit 1";    
      $result = $wpdb->get_row($query);  
      echo "<tr><td>#".$position."</td><td><a title='".htmlentities($result->title, ENT_QUOTES)."' href='/".($result->name)."/'>".$result->title."</a></td><td>".($toppost->cnt)."</td></tr>";
    }
    echo '</table>';
  }
  else {
    echo "<B>Sorry! Database Error: $query</B>";
  } 

  // https://helloacm.com
  global $wpdb; // use the wordpress database object
  $query = "
      select 
        count(1) as `cnt`, 
        `comment_post_ID` as `ID` 
      from 
        `$wpdb->comments` 
      where
        `comment_approved`='1'         
      group by 
        `comment_post_ID` 
      having 
        `cnt` > 0   
      order by 
        `cnt` desc 
      limit 20";
  $result = $wpdb->get_results($query);
  if ($result) {
    echo '<table><thead><tr><th>Ranking</th><th>Articles / Posts</th><th>WP Comment Count</th></tr></thead>';
    $position = 0; 
    foreach ($result as $toppost) {
      $position++;
      $query = "select `post_name` as `name`, `post_title` as `title` from `$wpdb->posts` where `ID` = '".$toppost->ID."' limit 1";    
      $result = $wpdb->get_row($query);  
      echo "<tr><td>#".$position."</td><td><a title='".htmlentities($result->title, ENT_QUOTES)."' href='/".($result->name)."/'>".$result->title."</a></td><td>".($toppost->cnt)."</td></tr>";
    }
    echo '</table>';
  }
  else {
    echo "<B>Sorry! Database Error: $query</B>";
  } 

The above shows top 20 posts/pages, however, you can change the SQL limit 20 to e.g. limit 30 to show 30 posts/pages instead. The limit is put to posts where having comments only by using having `cnt` > 0.

The variable $wpdb->posts gives the wordpress table name for posts and $wpdb->comments is the table name for comments.

The following is the example of this site, where the most popular articles are displayed according to their comment count.

WordPress Popular Page Plugins Comments

–EOF (The Ultimate Computing & Technology Blog) —