WordPress recent comments: display the latest comment without plugin or widget

published on March 7, 2011 in Blogging

Easily show the latest comments left by your visitors on your blog

Displaying the latest comment(s) published on your worpdress blog is the easiest way to get visitors interested in what other people are talking about on your site.

It is such a common feature that there are countless plugins (get recent comments being one of them) and even built-in widgets in many WP themes. But what if you want full control over the comments shown or don’t like plugins?

This WordPress “recent comment” tutorial should help.
Pre-requisite: you are not afraid of getting your hands dirty by playing with PHP, SQL and HTML.

Let’s dive straight in: simple function to get the n number of latest comments and display them in a list.

function showLatestComments() {
  global $wpdb;  
  $sql = "
   SELECT DISTINCT comment_post_ID, comment_author, comment_date_gmt, comment_approved, SUBSTRING(comment_content,1,100) AS com_excerpt 
   FROM $wpdb->comments 
   WHERE comment_approved = '1'
   ORDER BY comment_date_gmt DESC 
   LIMIT 5";  
 $comments = $wpdb->get_results($sql);  
 $output .= '<h2>latest comments</h2><ul id="comm">';  
 foreach ($comments as $comment) { 
   $output .= '<li><strong>'. $comment->comment_author . ' said</strong> : "' . strip_tags($comment->com_excerpt). '..."</li>';
   }
 $output .= '</ul>';  
 echo $output;  
}//end function
  • global $wpdb; calls the global variable $wpdb which allows us to use object oriented syntax like $wpdb->comments and $wpdb->get_results($sql);
  • SELECT DISTINCT comment_post_ID, comment_author, comment_date_gmt, comment_approved, SUBSTRING(comment_content,1,100) AS com_excerpt
    Here we are querying the database to retrieve the latest comments. Instead of doing a SELECT * that would return all the fields from the database, we are selecting only the ones we want to use (faster) for the list of latest comments;
    • SELECT DISTINCT comment_post_ID tells the script to retrieve DISTINCT (unique) comments from the comment database. The DISTINCT is probably redundant comment_post_ID being an auto-increment primary key, but it doesn’t hurt to include it;
    • comment_author is the name of the comment’s author;
    • comment_date_gmt will be used to sort the comment in reverse chronological order, to get the most recent ones at the top of the query result;
    • comment_approved will allow us to only display the comment that have been authorised (and not those that are awaiting approval);
    • SUBSTRING(comment_content,1,100) AS com_excerpt, first interesting bit here, we choose how long our invidial comments will be: in our case 100 characters long. We then put these in a new variable called com_excerpt;
  • FROM $wpdb->comments WHERE comment_approved = ’1′ ORDER BY comment_date_gmt DESC LIMIT 5″;. FROM the comments table, WHERE comments have been approved. Put them in reverse chronological ORDER and LIMIT it to the first 5 results. Second important bit of information the LIMIT 5 is where you decide how many of the latest comment you want to display.
  • $output.= ‘<h2>latest comments</h2><ul id=”comm”>’; foreach ($comments as $comment) { $output .= ‘<li><strong>’. $comment->comment_author . ‘ said</strong> : “‘ . strip_tags($comment->com_excerpt). ‘…”</li>’; } $output .= ‘</ul>’; echo $output; }//end function. In our example, $output is the variable we’ll use to store the comments and the heading for these comments. foreach simply goes through our 5 results and dispatches the values in HTML via PHP. You can see the comment_author and com_excerpt from SQL in that statement.

The result:

Results for the simple latest comment function

Results for the simple latest comment function

This is a good starting point and solid base to build a more versatile function, but the following areas can be improved:

  1. The function will display all the comments that have been approved regardless of them having been published on a password protected post or not;
  2. Pingback and comments are published indistinctively – ideally we only want the latter;
  3. Our own comments are added to the list – you may only want comments from your visitors to appear, as I do;
  4. On sites that use keywordLuv, like this one, the author’s name contains the @keywords I would prefer to remove;
  5. Comments are simply text, they are not linking anywhere at the moment (to the comment itself, the author’s website or both);

By tweaking the SQL query we can fix the first three issues.

Showing recent comments only from non password protected posts, without the pingbacks and without our own comments

SELECT DISTINCT ID, post_password, comment_post_ID, comment_author, comment_date_gmt, comment_approved, comment_type, comment_author_email, SUBSTRING(comment_content,1,100) AS com_excerpt 
FROM $wpdb->comments LEFT OUTER JOIN $wpdb->posts ON ($wpdb->comments.comment_post_ID = $wpdb->posts.ID) 
WHERE comment_approved = '1' AND comment_type = '' AND post_password = '' AND comment_author_email != 'you@yourdomain.com' 
ORDER BY comment_date_gmt DESC 
LIMIT 5";  
  • SELECT DISTINCT ID, post_password, comment_post_ID, comment_author, comment_date_gmt, comment_approved, comment_type, comment_author_email, SUBSTRING(comment_content,1,100) AS com_excerpt. We added ID, post_password, comment_type and comment_author_email to the original query.
    • ID and our old comment_post_ID are now going to join the comments table and posts table together to find out if the post was password protected or not, which post_password will hold the answer to;
    • comment_type will help distinguish between comments and pingbacks;
    • comment_author_email will allow us to remove our own content from the results;
  • FROM $wpdb->comments LEFT OUTER JOIN $wpdb->posts ON ($wpdb->comments.comment_post_ID = $wpdb->posts.ID). This is the joining of the posts table and comments table to be able to filter out comments that are on password protected posts.
  • WHERE comment_approved = ’1′ AND comment_type = ” AND post_password = ” AND comment_author_email != ‘you@yourdomain.com’
    • comment_type =” means normal comments (empty value in comment_type field), pingbacks have a pingback value in that field;
    • post_password = ” means from posts that do not have a password set;
    • comment_author_email != ‘you@yourdomain.com’ replace this with your own email address to remove your comments from the list. != means not equal or different from;

The result:

Results after removing own comments, pingbacks and password protected comments from list

Results after removing own comments, pingbacks and password protected comments from list

The noticeable difference is that my own comments are now gone. It’s the only difference in our example because there wasn’t any pingbacks or comments from password protected posts returned by the original function.

Let’s now look at how point 4 from the above list can be solved, ie. let’s take care of the commentLuv keywords in comment_author.

Removing the commentLuv keywords from comment_author in the list of latest comments

The SQL query from the previous function can be used as is, it’s simply a question of removing everything after the @ in the comment_author field we generate in the $output.

foreach ($comments as $comment) { 
$thisAuthor = explode('@',strip_tags($comment->comment_author));
$output .= '<li><strong>'. $thisAuthor[0] . ' said</strong> : "' . strip_tags($comment->com_excerpt). '..."</li>'; }
  • $thisAuthor = explode(‘@’,strip_tags($comment->comment_author)); splits comment_author into an array with key/values for each @ it encounters – usually only one – so there should be 2 array value: the name ($thisAuthor[0]) and the keyword ($thisAuthor[1]).
  • $output .= ‘<li><strong>’. $thisAuthor[0] . ‘ said</strong> : “‘ . strip_tags($comment->com_excerpt). ‘…”</li>’; } we used $thisAuthor[0] as it should be the author’s name; if they didn’t use the “name @ keywords” format, $thisAuthor[0] will be anything that they used (probably their keyword or nickname).

The result

keywordLuv @keywords removed from the list of recent comments

keywordLuv @keywords removed from the list of recent comments

The function so far:

function showLatestComments() {
  global $wpdb;  
  $sql = "
     SELECT DISTINCT ID, post_password, comment_post_ID, comment_author, comment_date_gmt, comment_approved, comment_type, comment_author_email, SUBSTRING(comment_content,1,100) AS com_excerpt 
     FROM $wpdb->comments LEFT OUTER JOIN $wpdb->posts ON ($wpdb->comments.comment_post_ID = $wpdb->posts.ID) 
     WHERE comment_approved = '1' AND comment_type = '' AND post_password = '' AND comment_author_email != 'you@yourdomain.com' 
     ORDER BY comment_date_gmt DESC 
     LIMIT 5";  
  $comments = $wpdb->get_results($sql);  
 $output .= '<h2>latest comments</h2><ul id="comm">';  
  foreach ($comments as $comment) { 
      $thisAuthor = explode('@',strip_tags($comment->comment_author));
      $output .= '<li><strong>'. $thisAuthor[0] . ' said</strong> : "' . strip_tags($comment->com_excerpt). '..."</li>';
   }
 $output .= '</ul>';  
 echo $output;  
}//end function

All we need to do now is put a call to that function <?php showLatestComments(); ?> where we want the list of latest comments to appear.

The final area to be improved – adding links – warrants its own blog post, so stay tuned for it (should be published this week).

This post shows example of code used on the netaccountant blog which were greatly inspired by Kyle Eslick of WP Hacks and his huge compilation of WordPress code.

subscribe

{ 2 comments… read them below or add one }

Redkathy March 8, 2011 at 7:05 pm

At first glance my thought was, “but what about comment luv?” I like the idea of no plugin or widget, especially since I have shared hosting and limited coding knowledge.

Thanks for the straightforward tutorial!
Redkathy also wrote Funny Friday – Women and Cigars

Reply

Adrienne March 11, 2011 at 10:00 pm

Hey Leo,

You definitely put a lot of thought into this post. Wow… I guess I’m pretty spoiled just using plug-ins but I have heard that over time it can slow your blog load time eventually. I must admit, I keep finding more and more plug-ins that I just really like. Way too convenient and easy I have to say.

Thanks for this. I believe I can figure this out because I’ve gotten so much better with the coding than ever before.

Adrienne
Adrienne also wrote 30 Ways To Promote Your Blog For Free

Reply

Leave a Comment. This site uses KeywordLuv. Enter YourName@YourKeywords in the Name field to take advantage.

CommentLuv badge

Previous post:

Next post:

©2014 NetAccountant.net - All rights reserved
Site Map | Legal Stuff