WordPress: Getting Most Popular(Commented) Posts
After my post Getting Recent Posts In WordPress before, I am thinking about getting the most popular posts or most commented posts. Talking about most popular posts, are those most posts commented mean they are most popular. Not really, there may be some posts, they’ve been read many times, but people seldom leave comments, aren’t they popular? So at the first place, I thought popular posts should be calculated by both view count and comment count. But when I go deep to coding, I found that it’s much more difficult to get the view count than comment count, because WordPress saves comment count by default, we can get the number from the database. On the other hand, view count is not saved. Which means we need to count every posts from the every beginning by ourselves, and most of time, this is not worth to do. This reminds me the Popularity Contest plugin, it counts the views, comments, trackbacks and so on, to determine the popularity of posts. If you want a exactly perfect popular posts list, you may try out this plugin. But I prefer a light way to do it as it’s not a really important need, let focus on most commented posts.
The first solution I found is from bavotasan.com, it’s simple and clear. Here is the main code:
function popularPosts($num) {
global $wpdb;
$posts = $wpdb->get_results("SELECT comment_count, ID, post_title FROM $wpdb->posts ORDER BY comment_count DESC LIMIT 0 , $num");
foreach ($posts as $post) {
setup_postdata($post);
$id = $post->ID;
$title = $post->post_title;
$count = $post->comment_count;
if ($count != 0) {
$popular .= '<li>';
$popular .= '<a href="' . get_permalink($id) . '" title="' . $title . '">' . $title . '</a> ';
$popular .= '</li>';
}
}
return $popular;
}
But when I came to blogohblog.com, the blogger used a more complex way to do it, he thought about more details, like do not count unaproved comments, able to set duration and should the password protected posted be counted or not. To make this happen, he re-counted comments by the comment_post_ID, very clever, here is the codes:
<?php function most_popular_posts($no_posts = 5, $before = '<li>', $after = '</li>', $show_pass_post = false, $duration='') {
global $wpdb;
$request = "SELECT ID, post_title, COUNT($wpdb->comments.comment_post_ID) AS 'comment_count' FROM $wpdb->posts, $wpdb->comments";
$request .= " WHERE comment_approved = '1' AND $wpdb->posts.ID=$wpdb->comments.comment_post_ID AND post_status = 'publish'";
if(!$show_pass_post) $request .= " AND post_password =''";
if($duration !="") { $request .= " AND DATE_SUB(CURDATE(),INTERVAL ".$duration." DAY) < post_date ";
}
$request .= " GROUP BY $wpdb->comments.comment_post_ID ORDER BY comment_count DESC LIMIT $no_posts";
$posts = $wpdb->get_results($request);
$output = '';
if ($posts) {
foreach ($posts as $post) {
$post_title = stripslashes($post->post_title);
$comment_count = $post->comment_count;
$permalink = get_permalink($post->ID);
$output .= $before . '<a href="' . $permalink . '" title="' . $post_title.'">' . $post_title . '</a> (' . $comment_count.')' . $after;
}
} else {
$output .= $before . "None found" . $after;
}
echo $output;
} ?>
The second one is a better choice, more options and flexibility. You can display popular posts list by placing the following code to your theme, sidebar.php or index.php or else.
<?php most_popular_posts(); ?>
The original codes from blogohblog works fine, but WordPress requires to use $wpdb->prepare for DB queries, if you want to upload your theme to WordPress theme directory, you need to do some modification, here is my version which works fine with my theme, and accepted by WordPress theme directory.
function most_popular_posts($no_posts = 5, $before = '<li>', $after = '</li>', $show_pass_post = false)
{
global $wpdb;
$posts = $wpdb->get_results(
$wpdb->prepare("
SELECT ID, post_title, COUNT($wpdb->comments.comment_post_ID) AS 'comment_count'
FROM $wpdb->posts, $wpdb->comments
WHERE comment_approved = %s AND $wpdb->posts.ID=$wpdb->comments.comment_post_ID AND post_status = %s AND post_password = %s
GROUP BY $wpdb->comments.comment_post_ID ORDER BY comment_count DESC LIMIT $no_posts",
'1','publish','')
);
$output = '';
if ($posts) {
foreach ($posts as $post) {
$post_title = stripslashes($post->post_title);
$comment_count = $post->comment_count;
$permalink = get_permalink($post->ID);
$output .= $before . '<a href="' . $permalink . '" title="' . $post_title.'">' . $post_title . '</a> (' . $comment_count.')' . $after;
}
} else {
$output .= $before . "None found" . $after;
}
echo $output;
}
.
.
-
http://www.flexlearner.com flexlearner
-
http://twitter.com/oyonarteweb María Rey
