Thursday, July 9, 2009

Related Product by Tag words - Solution

I have got a nice solution for the tag keyword and related post problem.

Here I want to illustrate the problem first. Suppose you have few books to post with some keywords.

C++ in 7 days

Book, Programming, computer, C

Advance MySql

Book ,Programming, computer, database

Harry Potter

Novel, Fantasy,

Anatomy handbook

Book, Medical

Advance C

Programming, computer, C

Now whenever I want to view the related books of c++ in 7 days. The books will be as following as the tag weight

1. Advance C

2. Advance MYSql

3. Anatomy book.

But the problem I described at the previous post is I have to query each time for each tag. To solve this problem I used the ‘TOXI Solution’ Method.

I split the books and tags into two different db tables. And they have many to many relationships (m n).

Book: bookId(PK), Book Name

Tag: tagId(PK), tagWord

And to develop the relationship another relational table

TagMap: Id(PK), bookId(FK), tagId(FK)

Now the query will be

SELECT b.*,@p1 = ''

FROM TagMap bt, Book b, Tag t

WHERE bt.tagId = t.tagId

AND (t.tagId IN (SELECT @p1 := tagId FROM TagMap WHERE bookId = '1'))

AND = bt.bookId AND <> '1'


HAVING (COUNT( )<= count(@p1) AND COUNT( >= 1) LIMIT 0,3

1 comment:

Anonymous said...

This gives you the assurance that the supplement is safe and of high
quality. You can have far more power, far less passion of thirst,
and very best of the total number of - you will find out a noticeable fat loss much more rapid.
Next to shedding weight, here are additional effects essential to Phen375 fat burner like effectiveness of product, general safety and many more.

Feel free to surf to my weblog; diet pills that work