Popular posts in WordPress

The main idea was to select posts marked by a custom field “popular”. I didn’t want to install a massive plugin with writing a page statistics to the database. My WordPress site is big (or small) enough so manually editing post list isn’t any problem for me. With custom fields, you can add extra meta-data to the post. Next it’s only needed to fetch posts marked with “popular” meta-data.

First you will have to add custom field called “popular” (without the quotes) and assign a value. In my case I choose 7 posts and give them values: 100, 200, 300 … 700. With a such selected values, inserting a new post to the middle will be easy because post list is sorted by custom field values. If post has more then 1 category, category with smallest term_id will be used for permalink. Function executes SQL only first time and save result set to the cache. Any other function call within the page will return data set from the cache.

SQL query is optimised for permalinks with format /%category%/%postname%/. In case of different format, you will have to customize SQL and PHP code.

function get_popular_posts(){
	global $wpdb;          // enable access to the WordPress DB object
	global $popular_cache; // define cache variable
	// if popular_cache has value, then return previous fetched data
	if ($popular_cache) return $popular_cache;
	// define SQL
	$sql = "select concat('/', substring_index(min(concat(t.term_id+1000,':',t.slug)),':',-1),
								        '/', p.post_name, '/') as permalink,
					       trim(p.post_title)            as title,
					       trim(p.post_excerpt)          as excerpt,
								 count(distinct c.comment_id)  as comment_number
					from terms t, term_taxonomy tt, term_relationships tr, postmeta pm, 
							 posts p left join comments c on (p.id=c.comment_post_id and c.comment_approved=1)
					where	t.term_id           = tt.term_id          and
								tr.term_taxonomy_id = tt.term_taxonomy_id and
								tr.object_id        = p.id                and 
								p.id                = pm.post_id          and
								tt.taxonomy         = 'category'          and
								p.post_type         = 'post'              and
								p.post_status       = 'publish'           and
								pm.meta_key         = 'popular'
					group by pm.meta_value, p.post_name, p.post_title
					order by pm.meta_value";
	// execute SQL and save result set to the popular_cache variable
	$popular_cache = $wpdb->get_results($sql);
	// return result set as object
	return $popular_cache;

Example how to display popular posts in sidebar.php …

<!-- list popular posts -->
<li><h2>Popular posts</h2>
		$popular_posts = get_popular_posts();
	  foreach ($popular_posts as $popular_post){
	    $permalink = $popular_post->permalink;
	    $title     = $popular_post->title;
      print "<li><a title=\"$title\" href=\"$permalink\">$title</a></li>\n";

… or in index.php

<div id="content" class="narrowcolumn">
	$popular_posts = get_popular_posts();
  foreach ($popular_posts as $popular_post):
    $permalink      = $popular_post->permalink;
    $title          = $popular_post->title;
    $excerpt        = $popular_post->excerpt;
    $comment_number = $popular_post->comment_number; ?>
			<div class="post">
					<a class="page_title_fp" title="<?=$title?>" href="<?=$permalink?>" rel="bookmark">
				<div class="entry">
					<p class="index_post">
						<?= $excerpt ?>
						<p class="index_comments">Comments: <?= $comment_number ?></p>
 <?php endforeach ?>

I tried to keep it simple, but please comment if anything has been left out or is not clear. Cheers!

2 thoughts on “Popular posts in WordPress

  1. hi could you please elaborate the instruction?…
    i didn’t know where to put the get_popular_posts() function,..newbi here


  2. @rey – I will suggest to save get_popular_posts() to let’s say wp-util.php file on your site. Next you only need to include wp-util.php to the index.php or sidebar.php theme file. On the other hand, you can include wp-util.php to the header.php and get_popular_posts() will be visible to the whole WordPress theme. Here is an example of how to include wp-util.php with get_popular_posts() inside in /my directory:

    <? include($_SERVER[‘DOCUMENT_ROOT’] . ‘/my/wp-util.php’); ?>

    This should be the first line in header.php file. If you require any further information, feel free to contact me.
    Best regards.

Leave a Comment