How to Create a WordPress Page that Shows Comment Statistics?

ACMer

11 years ago

Wordpress plugin to show comment statistics

WordPress is based on PHP and MySQL. So it is very flexible and powerful. This article will guide you how to create a page that shows the comment statistics for your blog. For example, a table with two columns, the author and the number of their comments.

In order to enable PHP code, you will need to install a plugin that allows you to include PHP code in your posts/pages. My recommendation is to search for [Simple Include HTML and PHP] at your wordpress control panel.

Then, you need to create a page that at least has the following (marked in red).

WordPress plugin to show comment statistics

Then go to your folder of the theme and create a file which is named cmt.php and set the file access mode to 644 (or at least readable).

The PHP code to fill in the file is:

<?php
//https://helloacm.com
  global $wpdb;
  $query = "
    select 
      `comment_author`, 
      `comment_author_url`,
      count(1) as `cnt` 
    from
      `wp_comments`
    where
      `comment_approved` = 1      
    group by 
      `comment_author`
    order by
      `cnt` desc
    limit 10 
  ";
  
  $result = $wpdb->get_results($query);
  if ($result) {
    echo "<table width='100%' style='border:0'>";
    echo "<tr><b><td>Author</td><td>The Number of Comments</td></b></tr>";
    foreach ($result as $author) {
      echo "<tr>";
      $a = esc_sql($author->comment_author);
      $query = "
        select 
          `comment_post_id`,
          `comment_id`
        from
          `wp_comments`
        where
          `comment_approved` = 1 and
          `comment_author` = '$a'
        order by
          `comment_date` desc
        limit 1      
      ";
      $latest = $wpdb->get_row($query);
      echo "<td><a href='$author->comment_author_url' target='_blank' title='$author->comment_author'>".$author->comment_author."</a></td>";
      echo "<td><a title='$author->comment_author' href='/archives/$latest->comment_post_id/#comment-$latest->comment_id'>".$author->cnt."</a></td>";      
      echo "</tr>";
    }
    echo "</table>";
  } 
?>

<?php
//https://helloacm.com
  global $wpdb;
  $query = "
    select 
      `comment_author`, 
      `comment_author_url`,
      count(1) as `cnt` 
    from
      `wp_comments`
    where
      `comment_approved` = 1      
    group by 
      `comment_author`
    order by
      `cnt` desc
    limit 10 
  ";
  
  $result = $wpdb->get_results($query);
  if ($result) {
    echo "<table width='100%' style='border:0'>";
    echo "<tr><b><td>Author</td><td>The Number of Comments</td></b></tr>";
    foreach ($result as $author) {
      echo "<tr>";
      $a = esc_sql($author->comment_author);
      $query = "
        select 
          `comment_post_id`,
          `comment_id`
        from
          `wp_comments`
        where
          `comment_approved` = 1 and
          `comment_author` = '$a'
        order by
          `comment_date` desc
        limit 1      
      ";
      $latest = $wpdb->get_row($query);
      echo "<td><a href='$author->comment_author_url' target='_blank' title='$author->comment_author'>".$author->comment_author."</a></td>";
      echo "<td><a title='$author->comment_author' href='/archives/$latest->comment_post_id/#comment-$latest->comment_id'>".$author->cnt."</a></td>";      
      echo "</tr>";
    }
    echo "</table>";
  } 
?>

Clicking the [Author] will navigate to the URL provided (in new window), and clicking the [The number of Comments] will lead to their latest comment. The URL format may need to be adjusted if it is not the standard one [/archives/post_id].

The example page can be viewed here: https://justyy.com/commentator (in Chinese).

The PHP function mysql_real_escape_string() is depreciated but you can still use esc_sql or $wpdb->prepare() to escape strings (containing single or double quotes) for SQL/database.

–EOF (The Ultimate Computing & Technology Blog) —

534 words
Last Post: How To Block Google Domains in Adsense?
Next Post: Using Absolute Keyword in Delphi to Remove Unnecessary Assignment Function

The Permanent URL is: How to Create a WordPress Page that Shows Comment Statistics? (AMP Version)

Related posts: