PHP MySQL show related items using tags

I want to show similar items using tags.

I have items table with itemid, itemname and itemtagids as columns. itemtagids has CSV tag ids.

Say in itemid 1, I have 3 tag ids 1,2,3 and in itemid 2 I have 4 tag ids 3,4,5,6 in itemid 3 I have 2 tag ids 2,3 and so on..

I want that for itemid 1 in similar items 3rd itemid should be shown first (because it has 2 matching) then itemid 2 and so on..

开发者_StackOverflow中文版

What i have used is:

SELECT itemid 
FROM items 
WHERE MATCH (itemtagids ) AGAINST ('2823' IN BOOLEAN MODE)

it does return results only in the case when there is one itemtagid in itemtags ids column.

sequence is something lie: 234,546,2823,342,5643 So, here 2823 is not an individual word but a complete string from 234,54...43

what should be done in this case where tags are in the form of ids and in CSV format.

---

It seems a FULLTEXT isn't the right search for your case. Try using regular expressions instead.

SELECT `itemid`
FROM `items`
WHERE `itemtagids` REGEXP '^2823$' 
OE `itemtagids` REGEXP '^2823,'
OR `itemtagids` REGEXP ',2823$'
OR `itemtagids` REGEXP ',2823,';

EDIT: or perhaps you might do with a single regular expression, like:

SELECT `itemid`
FROM `items`
WHERE `itemtagids` REGEXP '[[:<:]]2823[[:>:]]';

Hope it helps!