algorithm for related articles

algorithm, related, search

[kkonline]

Hi everyone,
I am writing an article manager. At the end of each article page I want to display links of related articles (on basis of common tag search)

For example
If article A has tags: love, life, success, article B has tags:love and life, article C has tags: life and dreams

then at the end or article A, I want to give a link of Article B and Article C

However i am getting links of Article A, Article B and Article C for tag "love" and then again links of Article A, Article B and Article C for tag "life"

I would just like suggestions from you all how to resolve this issue?
The code is too big and would take lot of time to give it's details, but if you want i'll explain the code i have witten

[turbosaab]

This is how the Wordpress Related Posts plugin does it. As you can see they are simply excluding the current post as part of the WHERE clause. Hope this helps.

$sql = "SELECT ID, post_title, post_content,"
  . "MATCH (post_name, post_content) "
  . "AGAINST ('$terms') AS score "
  . "FROM $wpdb->posts WHERE "
  . "MATCH (post_name, post_content) "
  . "AGAINST ('$terms') "
 . "AND post_date <= '$now' "
  . "AND (post_status IN ( 'publish', 'static' ) && ID != '$post->ID') ";
 if ($show_pass_post=='false') { $sql .= "AND post_password ='' "; }
 $sql .= "ORDER BY score DESC LIMIT $limit";

[kkonline]

hi.. that was bit helpful , however i cannot figure out how should i write the query.

I use 3 tables: article, tag, and article_tag_xref. Obviously articles go in the article table with a unique id for each one. I then have a tag table in which I store each unique tag with an id. The article_tag_xref table is a simple many-to-many cross reference table storing an article id and a tag id. Eg:

Line number On/Off ¦ Expand/Contract
Article table:

Article id Title
1 Article Number One
2 Article Number Two

Tag table

Tag id Article id wordid
1 34 3
2 43 3
3 21 2
4 23 1

Word id wordlist
1 love
2 life
3 success

Article_tag_xref table

THIS IS HOW I LINK ARTICLES AND TAGS,
First i check the wordid then the articleid in tags table and then correspond it to a article

So how do i perform the processes you told with respect to the above structure?

[Habtom]

You can use the following as a base:

SELECT A.title FROM article A, word W WHERE W.worldist IN ($words) AND W.wordid = T.wordid AND T.articleid = A.id