One way to increase visitor engagements is to reward their comments by showcasing them on your website. Additionally, you can also feature the top commenters as well, linking back to their website in the process. Here we’ll create a dedicated Page Template to display those comments and commenters in one place.
In short, this tutorial will teach you how to:
- create a Page Template,
- use SQL queries in your code to fetch comments with varying parameters,
- create a section of the Page that’s only viewable by Admin,
- add support for a comment-related plugin.
Creating a Page Template
The easiest way to create a Page Template is to open the page.php file in your theme, which will roughly look like this:
<?php get_header(); ?>
<div id="content">
<?php if (have_posts()) : while (have_posts()) : the_post(); ?>
<div class="post" id="post-<?php the_ID(); ?>">
<h2 class="page_title"><?php the_title(); ?></h2>
<?php the_content(); ?>
</div>
<?php comments_template(); ?>
<?php endwhile; endif; ?>
</div>
<?php get_sidebar(); ?>
<?php get_footer(); ?>
Copy and paste page.php’s content and add this to the very top:
<?php /* Template Name: Comments Central */ ?>
And save it. There’s no real rules on naming a Page Template file, but it’s a good idea to go with a prefix to make it recognizable, say “pt-comment-central.php”. We haven’t added anything into this Page Template, but it’s up and running and selectable on the write new Page dashboard area.
Fetching Comments
For this Page Template, we’ll feature four different aspects of comments:
- Recent Comments,
- Recent Trackbacks / Pingbacks,
- Top Commenters,
- Most Commented Posts,
First, we’ll do Recent Comments:
<h3>Recent Comments</h3>
<ul id="cc-recent-comments">
<?php
$max = 7; // number item to get
global $wpdb;
$sql = "SELECT c.*, p.post_title FROM $wpdb->comments c INNER JOIN $wpdb->posts p ON (c.comment_post_id=p.ID) WHERE comment_approved = '1' AND comment_type not in ('trackback','pingback') ORDER BY comment_date DESC LIMIT $max";
$results = $wpdb->get_results($sql);
$template = '%g <a href="%au">%an</a> on <a href="%pu#comment-%cid">%pt</a>';
$echoed = 0;
foreach ($results as $row) {
$tags = array('%ct','%cd','%g','%pt','%pu','%au','%an','%cid');
$replacements = array($row->comment_title,$row->comment_date,get_avatar($row->comment_author_email,'32'),$row->post_title,get_permalink($row->comment_post_ID),$row->comment_author_url,$row->comment_author,$row->comment_ID);
echo '<li>' . str_replace($tags,$replacements,$template) . '</li>';
$echoed = 1;
}
if ($echoed==0)
echo '<li>No comment found.</li>';
?>
</ul>
The SQL query asks for all approved comments sorted by date (latest first). $max is where we set the amount of comments to get, 7 in our case. The output of the code above will be an unordered list of recent comments:
With a little CSS we can straighten that to look better:
#cc-recent-comments li {
width: 100%;
float: left;
list-style-type: none;
}
#cc-recent-comments li img {
float: left;
margin-top: -5px;
}
$template determines how the actual text will be written; this is based on the format made by WP Comment Remix, and you can follow that link to learn more on customizing it (look for ‘tokens’).
Next is Recent Pingbacks / Trackbacks:
<h3>Recent Pingbacks / Trackbacks </h3>
<ul id="cc-recent-trackbacks">
<?php
$sql = "SELECT c.*, p.post_title FROM $wpdb->comments c INNER JOIN $wpdb->posts p ON (c.comment_post_id=p.ID) WHERE comment_approved = '1' AND comment_type not in ('trackback','pingback') ORDER BY comment_date DESC LIMIT $max";
$results = $wpdb->get_results($sql);
$template = '%g <a href="%au">%an</a> on <a href="%pu#comment-%cid">%pt</a>';
$echoed = 0;
foreach ($results as $row) {
$tags = array('%ct','%cd','%g','%pt','%pu','%au','%an','%cid');
$replacements = array($row->comment_title,$row->comment_date,get_avatar($row->comment_author_email,'32'),$row->post_title,get_permalink($row->comment_post_ID),$row->comment_author_url,$row->comment_author,$row->comment_ID);
echo '<li>' . str_replace($tags,$replacements,$template) . '</li>';
$echoed=1;
}
if ($echoed==0)
echo '<li>No comment found.</li>';
?>
</ul>
The code above is very similar to the one we have for Recent Comments, the only differences being that we’re now asking for comments with ‘comment_type’ under ‘pingback’ / ‘trackback’, and the template is a bit different as well. Result:
Here’s the code for Top Commenters:
<h3>Top Commenters</h3>
<ul id="cc-top-commenters">
<?php
$sql = "SELECT comment_author, comment_author_url, comment_author_email, count(comment_ID) as comment_count FROM $wpdb->comments WHERE comment_approved = '1' AND comment_type not in ('trackback','pingback') GROUP BY comment_author, comment_author_url, comment_author_email ORDER BY comment_count DESC LIMIT $max";
$results = $wpdb->get_results($sql);
$template = '<a href="%au">%g %an</a> (%c comments)';
$echoed = 0;
foreach ($results as $row) {
$tags = array('%g','%au','%an','%c');
$replacements = array(get_avatar($row->comment_author_email,'32'),$row->comment_author_url,$row->comment_author,$row->comment_count);
echo '<li>' . str_replace($tags,$replacements,$template) . '</li>';
$echoed = 1;
}
if ($echoed==0)
echo '<li>No commenter found.</li>';
?>
</ul>
Nothing too mind-blowing there. Do notice the cool get_avatar() function, though, which will give you the Gravatar for anyone whose email address you specify. In this case, we fetch the avatar image using the commenter’s e-mail address. With the CSS similar to the one we have for recent comments, we can have this result:
#cc-top-commenters li {
width: 100%;
float: left;
list-style-type: none;
}
#cc-top-commenters li img {
float: left;
margin-top: -5px;
}
Last is Most Commented Posts:
<h3>Most Commented Posts</h3>
<ul id="cc-most-comments">
$sql = "SELECT p.*, c.comment_count FROM $wpdb->posts p INNER JOIN (SELECT comment_post_id, count(comment_ID) as comment_count from $wpdb->comments WHERE comment_approved='1' GROUP BY comment_post_id) c ON (c.comment_post_id=p.ID) ORDER BY c.comment_count DESC LIMIT $max";
$results = $wpdb->get_results($sql);
$template = '<a href="%pu">%pt</a> (%c comments)';
$echoed = 0;
foreach ($results as $row) {
$tags = array('%pd','%pt','%pu','%c');
$replacements = array($row->post_date,$row->post_title,get_permalink($row->ID),$row->comment_count);
echo '<li>' . str_replace($tags,$replacements,$template) . '</li>';
$echoed = 1;
}
if ($echoed==0)
echo '<li>No commenter found.</li>';
?>
</ul>
And that’s it. Next, we’ll add some extra coolness by adding some stuff that only the admin can see.
Admin-only Information
To show stuff only for the admins, we can use this code snippet from WPCandy:
<?php
global $user_ID;
if( $user_ID ) :
if( current_user_can('level_10') ) :
// admin-only stuff here.
endif;
endif; ?>
Now on the Dashboard, we get a quick glance of a site’s total, approved, pending review and spam comments. Let’s replicate this for our Page Template for easier, admin-only access:
<?php
$num_comm = wp_count_comments();
?>
Total Comments: <a href="<?php bloginfo('wpurl'); ?>/wp-admin/edit-comments.php?"><?php echo $num_comm->total_comments; ?></a>
Approved: <a href="<?php bloginfo('wpurl'); ?>/wp-admin/edit-comments.php?comment_status=approved"><?php echo $num_comm->approved; ?></a>
Moderated: <a href="<?php bloginfo('wpurl'); ?>/wp-admin/edit-comments.php?comment_status=moderated"><?php echo $num_comm->moderated; ?></a>
Spam: <a href="<?php bloginfo('wpurl'); ?>/wp-admin/edit-comments.php?comment_status=spam"><?php echo $num_comm->spam; ?> </a>
wp_count_comments() is a neat function that returns an array of various comment stat numbers. We’re adding links to the respective comment administration area too.
Adding Some Sparks
Last, say you find a cool comment-related plugins you want to incorporate into this Page Template. Instead of adding more codes, let’s just add support for it. For this example, I’ll go with Activity Sparks plugin, which can “display a ‘sparkline’ style graph in your sidebar indicate post and/or comment activity. ” Sounds great to me.
Usually, a plugin’s readme.txt file will teach you how to add it into your theme files. In our case, the code can be like this:
<?php
if(function_exists('activitysparks')) {
activitysparks(array('dataset'=>'legend','height_px'=>100,'width_px'=>600,'period'=>30, 'ticks'=>24));
}
?>
function_exists() checks whether a particular function is available; in our case, the activitysparks function, which will be available if the plugin has been uploaded and activated. If it’s there, we show the graph. If not, then our Page Template won’t show anything (but it will still run just fine, no errors).
Result and Example
An example of this Page Template is available here. It uses the codes you see here with a few modifications, mostly to keep the HTML structure consistent with the rest of the website. The whole code for that Page Template is available at Pastebin.
Credits and Further Readings
-
The codes used to display the various recent and top comments are taken from WP Comment Remix plugin. Take a look at its code to learn more things to do with comments, like excluding admin comments from top commenters or how to take into account recent comments only. The
$templatewe use to format the output can be learned more from that page, too. - Creating Your Own Page Template on the WordPress Codex.
- Displaying admin-only content tutorial on WPCandy.
good
Great tutorial Hafiz!
And if I’m not wrong this is the first guest post? if so woot!
Maybe I’ll be next 😛
Definitely some things here that I want to implement on my photoblog once I get it converted to WordPress. Thanks!
Hey Kim, awesome. Let us know how it works out once you implement it.
Great idea, I might implement something like this for my site.
Just an FYI: instead of doing raw queries, try using this:
include ( ABSPATH . ‘wp-admin/includes/template.php’ );
_wp_get_comment_list( ‘approved’, false, 0, 20, 0, ‘comment’ );
Hey Ptah, glad you found it useful. I was actually thinking of implementing a similar page here too because I think it’s a good way to “reward’ good commenters.
And thanks for the tip about the raw SQL queries. I’m assuming that could essentially replace any instances of code like this?
$sql = "SELECT p.*, ..."; // and so on$results = $wpdb->get_results($sql);
Exactly,
_wp_get_comment_list()returns an array of all comments so from there, you would simply run a foreach loop.Learn something new every day. Will take a good look at that function, thanks Ptah 🙂