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 b.id = bt.bookId AND b.id <> '1'
GROUP BY b.id
HAVING (COUNT( b.id )<= count(@p1) AND COUNT(b.id) >= 1) LIMIT 0,3