Add a Next Random Post in WordPress Page Template

You might often get advices on improving SEO (Search Engine Optimisation) by adding links to your previous posts when you publish a new post. This helps to increase the traffic and it works similar to plugins like ‘Relative Posts’ but only simpler.

All wordpress posts are stored in table e.g. wp_posts and inside this table, you will find post_title, post_name and these columns are pretty straightforward. Depending on the configurations of your post URLs, you could have a various format of permalinks which can be configured at Settings – Permanlink section.

The following PHP code can be inserted into your page template, normally single.php that is the single post template or page.php for page template.

1
2
3
4
5
6
7
8
9

  global $wpdb; // include the $wpdb
  $query = "select `post_title` as `title`, `post_name` as `name` from `wp_posts` where `post_type`='post' and `post_status`='publish' order by rand() limit 1";
  $results = $wpdb->get_results($query); // run the query on the database
  if ($results) { 
    $domain = $_SERVER['SERVER_NAME'];
    foreach ($results as $toppost) {
        echo "<a href="http://$domain/".$toppost->name."/"><img title="Next Post: ".$toppost->title."" src="https://helloacm.com/static/nextpost2.png" alt="Next Post: ".$toppost->title."" /></a>";
    }
  }

  global $wpdb; // include the $wpdb
  $query = "select `post_title` as `title`, `post_name` as `name` from `wp_posts` where `post_type`='post' and `post_status`='publish' order by rand() limit 1";
  $results = $wpdb->get_results($query); // run the query on the database
  if ($results) { 
    $domain = $_SERVER['SERVER_NAME'];
  	foreach ($results as $toppost) {
  		echo "<a href="http://$domain/".$toppost->name."/"><img title="Next Post: ".$toppost->title."" src="https://helloacm.com/static/nextpost2.png" alt="Next Post: ".$toppost->title."" /></a>";
  	}
  }

Change wp_posts accordingly as this might vary a bit. The SQL query returns a random post where it excludes the pages, or menu_items, links etc. And the status of the post should be published which excludes the draft. The limit 1 returns only 1 post. So if you want a previous post, you might consider changing this to limit 2.

The permalink on my site is based on the post_name which is more SEO friendly. You might well customise this. The SQL query can be customised as well. For example, you might want to return relevant posts instead of random posts by matching the `title` value according to the current post title. Or you really want to return its previous or next post sorted by publish date, you might want to use the ‘post_id’ field.

Comments

Yu – “Cool, random is really nice. Also better make sure the random one is related to the current post. But this is usually not a big issue as long as the blog is for a single genre.

Also the order by RAND() work fine for thousands of records. (usually the case for a normal blog). But it will burn your database if you have millions of records in the same table that matches the search criteria.”

Yes, the SQL order by rand() is a bit tricky and slow. It needs to fetch all records and sorted randomly. It is considered low efficiency when the size of table is medium or large. You might want to use the following trick to obtain a random record in a faster manner by employing the rand() function in PHP.

// get total number of records
$query = "select count(1) from `table`";
$result = mysql_query($query) or die(mysql_error());
$total = mysql_result($result, 0, 0);

// get a random
$r = rand(0, $total - 1);
// return a number between 0 and $total - 1 inclusive using PHP
$query = "select * from `table` limit $r, 1";
$result = mysql_query($query) or die(mysql_error());

The SQL limit causes specifies the offset and the second parameter specifies the number of records to fetch.

Better Random Record

According the above, the order by rand() is extremely inefficient if you have a very big table because for the sorting (even if you just want one item), the rand() function will be invoked and that is what we don’t want. We can improve the above by using two queries, the first one returns the total number of posts, and we have a PHP random function and get the random record by using limit statement.

1
2
3
4
5
6
7

global $wpdb; // include the $wpdb
$query= "select count(1) from `wp_posts` where `post_type`='post' and `post_status`='publish'";
$cnt = $wpdb->get_var($query); // run the query on the database and return single variable
$rand = mt_rand(0, $cnt - 1);
$query= "select `post_title` as `title`, `post_name` as `name` from `wp_posts` where `post_type`='post' and `post_status`='publish' limit $rand,1";
$results = $wpdb->get_results($query); // run the query on the database
// the rest are the same

global $wpdb; // include the $wpdb
$query= "select count(1) from `wp_posts` where `post_type`='post' and `post_status`='publish'";
$cnt = $wpdb->get_var($query); // run the query on the database and return single variable
$rand = mt_rand(0, $cnt - 1);
$query= "select `post_title` as `title`, `post_name` as `name` from `wp_posts` where `post_type`='post' and `post_status`='publish' limit $rand,1";
$results = $wpdb->get_results($query); // run the query on the database
// the rest are the same

–EOF (The Ultimate Computing & Technology Blog) —