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>
	<ul>
	<?php 
		$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";
	  }
	?>
	</ul>
</li>

… or in index.php

<div id="content" class="narrowcolumn">
<?php 
	$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">
				<h2>
					<a class="page_title_fp" title="<?=$title?>" href="<?=$permalink?>" rel="bookmark">
						<?=$title?>
					</a>
				</h2>
				<div class="entry">
					<p class="index_post">
						<?= $excerpt ?>
						<p class="index_comments">Comments: <?= $comment_number ?></p>
					</p>
				</div>
			</div>
 <?php endforeach ?>
</div>

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