How do I implement related messages without putting them in?
We have 2 text fields in our database ('post_text' and 'post_slug'). Let's say post_text = "Hello World!", So its post_slug = "hello-world". How do I implement related posts without putting tags that only work on existing fields? (PHP, MySQL)
ps the database contains many messages.
a source to share
check out similar function http://jp2.php.net/manual/en/function.similar-text.php
or perhaps splitting each word with spaces and digging out a link with your own algorithm.
and if you can add a table to mysql you must create a table that contains the calculated ratio of each message.
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` )
)
updated every time you add a post, or maybe once a day.
and take the 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
a source to share
Just note: you definitely don't want to call your related messages on the go every time you show the page, because in a large database that would be somewhere between too much work and impossible.
Typically, you periodically run whatever algorithm you use and store the information in a separate table referencing the original table. Displaying a message would be just a simple connection.
a source to share