Creating a related or similar posts using PHP & MySQL.

Question

I was looking around SO and was wondering how would I go about in creating a related or similar posts to display on my web site using PHP & MySQL? What is a basic example of how to do this?

Answer 1

Using the MySQL Full Text search MATCH (col1,col2,...) AGAINST (expr [search_modifier]) thing.

Let's say your table is articles and you need to find related posts about a title of current post. Do it like this:

SELECT *, MATCH(title, body) AGAINST('$CurrentPostTitle') AS score
FROM articles 
WHERE MATCH(title, body) AGAINST('$CurrentPostTitle') 
ORDER BY score DESC LIMTI 5

This will give you top 5 related posts.

But first remember to enabled Full Text search for that table's columns, by running this query:

ALTER TABLE articles ADD FULLTEXT (title, body);

[EDIT]: Why to not use LIKE: Clarification to OP:

Because it will not give correct results. Let's say you current title is "Music of 1980" and you want related posts on that. Now, if you use LIKE then only the posts containing EXACTLY the sequence of words "Music of 1980" will appear. However, if you use MATCH ... AGAINST, then posts that contain Music OR 1980 will appear. Also, the posts that contain both Music and 1980 will appear on Top because it gives a SCORE to each results and we are sorting by that score.I hope that's clear.

[EDIT]: 2:

If you have categories, you can add AND Category = '$CurrentCategory' in the SQL query where clause to get more specific results.

[EDIT]: 3: OP can't use Full text:

If you can not use Full Text (for some reason), you can just show 5 random posts from the same category. As they are in same category, they are somehow related at least:

SELECT *
FROM articles 
WHERE Category = '$CurrentCategory'
LIMTI 5