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:

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.

<li><a href="http://zacklive.com/new-black-and-white-wordpress-theme/300/" title="New Black and White WordPress Theme (Updated:05/Nov/09)">New Black and White WordPress Theme (Updated:05/Nov/09)</a> (112)</li><li><a href="http://zacklive.com/my-first-wordpress-theme-gray-lines/168/" title="My First WordPress Theme: Gray Lines (Updated:05/Nov/09)">My First WordPress Theme: Gray Lines (Updated:05/Nov/09)</a> (22)</li><li><a href="http://zacklive.com/itech-theme-free-wordpress-theme-for-gadgets-and-tech-blogs/690/" title="iTech Theme: Free WordPress Theme for Gadgets and Tech Blogs">iTech Theme: Free WordPress Theme for Gadgets and Tech Blogs</a> (17)</li><li><a href="http://zacklive.com/lockerz-offers-free-gifts-for-playing-games/614/" title="LockerZ Offers Free Gifts for Playing Games">LockerZ Offers Free Gifts for Playing Games</a> (12)</li><li><a href="http://zacklive.com/how-to-write-wordpress-robotstxt-seo/18/" title="How to Write WordPress robots.txt for SEO">How to Write WordPress robots.txt for SEO</a> (11)</li>

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;
	}

Feel free to visit our Forum, subscribe our RRS Feed for news updates, and follow us on Twitter.