How to implement related posts feature without tagging each post?

Question

We have 2 text fields ('post_text' & 'post_slug') in our database. Let's say, post_text = "Hello World!", so its post_slug = "hello-world". How to implement related posts feature without tagging each posts operating only existing fields? (PHP, MySQL)

p.s. the database contains a lot of posts.

Answer 1

Just a note: you definitely do not want to compute your related posts on the fly every time you display a page because on a large database that would be somewhere between too much work and impossible.

You would normally periodically run whatever algorithm you are using and save the information in a separate table referencing your original table. Displaying a post would then be just a simple join.

Answer 2

check out the similar_text function http://jp2.php.net/manual/en/function.similar-text.php

or maybe split each word by spaces and caculate the relation with your own algorithm.

and if your able to add a table to mysql, you should create a table that holds the calculated relation of each post.

CREATE TABLE blog_table.`posts_relation` (
`post_id` INT UNSIGNED NOT NULL ,
`related_post_id` INT UNSIGNED NOT NULL ,
`relation` FLOAT UNSIGNED NOT NULL ,
INDEX ( `post_id` , `related_post_id` ) 
)

update each time you add a post, or maybe once a day.

and grab your results with something like

SELECT posts.* FROM posts, posts_relation WHERE posts_relation.post_id = {$post_id} AND posts.post_id = posts_relation.related_post_id ORDER BY posts_relation.relation DESC LIMIT 5