Displaying Related Entries on your Movable Type Site

From time to time, I am asked how to display related entries on a Movable Type-powered site (and I frequently have to look it up myself), so I figured that I’d document it here.

While there are plugins that do the work, they typically depend on another value (usually categories, keywords or tags – sometimes a combination of those), and the problem with those is that they depend on humans to first enter that value. If you forget to do so, then it means the related entries won’t work until you go back and enter it. If you enter the wrong value, you get the wrong results. There has to be a better way. Luckily, there is.

Some time ago (actually, it was nearly six years ago), Adam Kalsey wrote about his Related Entries plugin. It used keywords and categories for providing results. The maintenance on the plugin has since been taken over by Tim Appnel, but not too long after penning this entry, Adam put together another, even better way of providing results. It outlined a solution that let something else do the work: MySQL.

In Related Entries Revisited (relatively new – just a hair under five years old!), we find a much better way of doing related entries. Since all of our data is stored in a MySQL database, we’ll just let MySQL perform the really heavy lifting, and do the indexing of the data. While there is still a need for a plugin, it’s only to get the data out in a format that we can use it. The actual indexing part is handled for us.

Setting up the Fulltext Index

This is the tricky part. Actually, it’s the first part of the tricky part. Whatever you plan to index must be in the index. Not so confusing, is it? In the original entry, this index was keywords, title and excerpt. There’s only one problem for me – I don’t use keywords or excerpt. So that means the index is built on the entry title, which is a bit lame. Instead, I built mine on the entry title (likely the most useful part, after all), the entry text and the extended entry. In other words, the entry_title, entry_text and entry_text_more fields. You can always delete it and start over, but if you get it right the first time, you don’t need to do so. Just remember that whatever you put in this index will be used later – so make sure you know what’s in there! You’ll then run a command to create the index:

ALTER TABLE mt_entry ADD FULLTEXT ( entry_title, entry_text, entry_text_more );

Keeping in mind that you’ll want to use the name of the fields you use inside the last part of the command. Make sense?

Get the Lastest Version of the SQL Plugin

While the original version of the SQL plugin may work, if you’re using an old version of Movable Type, chances are you’ll want the new version of SQL instead, because it’s compatible with MT4. It also has an interface (accessible through the configuration panel) where you can access additional databases, meaning you could potentially use this process to access information in other applications!

Add the Template Tags

The last step is to insert the template tags. This is somewhat similar to setting up standard entries – you just need to set up a container for MTSQLEntries to get the data from the database instead, and give it a query to run. That’s the hard part.

The query needs to be run with the fields you specified earlier, in the same order you provided them. It should look something like this:

<MTSQLEntries query="SELECT entry_id, MATCH (entry_title, entry_text, entry_more) AGAINST ('[MTEntryTitle encode_php='q'] [MTEntryBody encode_php='q'] [MTEntryMore encode_php='q']') AS score FROM mt_entry WHERE MATCH (entry_title, entry_text, entry_text_more) AGAINST ('[MTEntryTitle encode_php='q'] [MTEntryBody encode_php='q'] [MTEntryMore encode_php='q']') AND entry_id != '[MTEntryID]' AND entry_blog_id = [MTBlogID] ORDER BY score DESC LIMIT 0 , 6">

That looks intimidating, but it really isn’t bad. Just take it slow.

First off, you may be able to copy it just like it is (if you use the same fields I do). If you don’t, you’ll need to change the field names – entry title, entry text and entry more. However, you’ll need to be careful here, because you’re talking about Movable Type template tags and database field names. For instance, MTEntryMore and entry_text_more represent the same field. Just make sure you get the correct values. You can almost always ignore the encode_php values – I use them because I use PHP code in my pages. But if you don’t, it won’t hurt you. Finally, look at the LIMIT near the end. I like to see 6 entries. This is important.

When this command runs, it will pull out a number of entries from your database. MySQL is going to rank every entry in the database, from most relevant to least. So you’ll want to put something here. I like six entries. You might want one, ten or fifty. But keep in mind you can use a thousand. It will just look a little strange, because the further you get down the list, the less relevant your entries will get to the content on display. You might want to start with a larger number, and adjust it down to see how relevant things are to your content. Everyone is different, and it might be that you can display fifty entries per page without losing relevance. Play with it to see what happens.

Once you decide what to get on the page, insert your entry code just like normal – permalinks, titles, what-have-you. This might be a list, this could be full-blown entries. But when you are done, do not forget to close the MTSQLEntries look with one more tag: </MTSQLEntries> (with the slash). That closes the block, and then you are done. That is all you need, and you can now see related entries on the page.

This will not work on dynamic pages, because the SQL plugin isn’t dynamic. You can probably do some MySQL magic with PHP to make it work, but this won’t do that. If you need any help, let me know how I can help.