finding related items using mysql?

Question

im trying to figure out how to find related topics items using mysql & php, this is my table for topics

topics{id, topic, founder,vote_up, vote_down date}

for instance say i was on topic page of php

topic {3,php,getaway, 6, 8, 20.06.2009 ect]

how would i find all the related topics to this topic:

if you get what i mean:)) thanks, and if thier is any php classes ect that do this kind of stuff.

Answer 1

That depends on your definition of related. One way would be to do a full-text search looking for the topic's title and display the top results using either mysql's functions or better, Sphinx or Lucene.

Answer 2

You need to define what relativity means for your domain. A naive way would be to tokenize the title of the topic in question into words (in this case "php") and do full text searches to find other topics with similar articles. Then combine and present.

Answer 3

I often use REGEXP searches in MySQL for wildcard-type searches. But it's certainly not a magic bullet for what sounds like a fairly complicated system of providing related items.

Often, a categorization system is built in the database for comparison purposes. Since computers aren't magic, they need to be given information to compare against. In this case, you might define categories or keywords and then use queries to bring up the items that contain those keyword or category definitions. Then the challenge becomes how to seed those categories?

Answer 4

I don't know why I can't comment on other people's answers so forgive this "answer"

"i forgot to add that each topic, has a little bio about them, like 200 character about paragraph saying what the topic is about, would that be good way to relate stuff to each other?"

This is a very unreliable way to relate anything. If this is the case, pretty much everything would relate as common words appear all over every topic. Words like "the" or "and" would relate everything in something like a full text search, depending on how you orchestrate your comparison.

In your case, you might want to clarify HOW you plan on comparing. You already mention you are using php and mysql, so we all assume you are writing a query to find the relations. Why then are you avoiding a relational table? A keyword table? It's basic web development at this point and seems very strange that you'd be interested in a complicated work around for a very simple concept.

blog{id, title body, date} topic{id, title} blog_topic{id, blog_id, topic_id}

$sql = "SELECT b.*, t.* FROM `blogs` b LEFT JOIN `blog_topic` r ON r.`blog_id` = b.`id` LEFT JOIN `topic` t ON t.`id` = r.`topic_id` WHERE t.`id` = ".$whatever_topic_id_you_pass;

Seems simple enough to me. Much more reliable than some weird full text search.