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 LIMIT 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'
LIMIT 5

Edited Syntax: Changed LIMTI to LIMIT in MySQL Code

Answer 2

In most cases you will want to give different weights for matching columns. You can do this on this way:

SELECT 
    *, 
    ROUND(0 + 
               (MATCH(title) AGAINST('$CurrentPostTitle') * TITLE_WEIGHT) +
               (MATCH(body) AGAINST('$CurrentPostTitle') * BODY_WEIGHT)) AS score
FROM 
    articles 
HAVING 
    score > 0 
ORDER BY 
    score DESC 
LIMIT 5

Just replace TITLE_WEIGHT and BODY_WEIGHT by the weight as integer.

Working this way, you will need to add FULLTEXT to each column:

ALTER TABLE articles ADD FULLTEXT (title);
ALTER TABLE articles ADD FULLTEXT (body);

If is the case, don't forget to exclude the current post from results. You can do this with a WHERE condition before HAVING like this:

WHERE id <> $CurrentPostId