MYSQL get closest match with multiple WHERE

In my database, I have a table with products and another table that has tags associated to each of te products.

When the user looks at a product's page, I want to show the most "related" (or closest) product to the one he's looking at.

So let's say the product is tagged with 5 different tags. I would like to get the prodcuts that have the same 5 tags and then the ones that have 4 of the same 5 tags, and then the ones that have 3 of the same 5 tags, etc...

To do this, I'm guessing I would have to create one or more MYSQL query but I don't even know where to start.

Matching the same 5 tags is easy, I can just use WHERE tag='?' AND tag='?'... but how can I get the other (4/5, 3/5, 2/5, 1/5)?

Any help would be greatly appreciated!

Cheers Steven

EDITS

@Orbits: My tags are all on different rows... if not I could probably do a text match but this is the the case. A tag row consists of (id, tag, product_id)

@cusimar9: Different table, as stated in the post :P

@vbence: I believe it's as simple as possible.. here is is... but I don't have a connecting table

PRODUCTS :

CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  ...
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

TAGS :

CREATE 开发者_运维问答TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(70) NOT NULL,
  `product_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=116 ;

The string literals 'Tag1' and so has to be replaced with your tags of course.

SELECT products.id, COUNT(tags.id) AS match_level
FROM products
    LEFT JOIN tags ON tags.product_id = product.id
WHERE tags.type IN ('Tag1', 'Tag2', 'Tag3', 'Tag4', 'Tag5')
GROUP BY products.id
ORDER BY match_level DESC

If you don't have an index like this already, add it to your tags table. Without indexes you are wasting resources. Big time.

CREATE INDEX multimatch ON tags (type, product_id);

SELECT A.*
  FROM ( SELECT P.*,
                P.Tag LIKE '%a%' AS Tag1,
                P.Tag LIKE '%b%' AS Tag2,
                P.Tag LIKE '%c%' AS Tag3,
                P.Tag LIKE '%d%' AS Tag4,
                P.Tag LIKE '%e%' AS Tag5
           FROM `products` P
          WHERE P.Tag LIKE '%a%' 
             OR P.Tag LIKE '%b%' 
             OR P.Tag LIKE '%c%'
             OR P.Tag LIKE '%d%' 
             OR P.Tag LIKE '%e%'
       ) A
 ORDER BY A.Tag1+A.Tag2+A.Tag3+A.Tag4+A.Tag5 DESC

Simples:

SELECT related.id, count(*) as matches
FROM products related,
     tags rel_tags,
     tags this_tags
WHERE this_tags.product_id=$product_id_for_this_product
AND this_tags.type=rel_tags.type
AND rel_tags.product_id<>this_tags.product_id
AND rel_tags.product_id=related.id
GROUP BY related_id
ORDER BY count(*) DESC

select products.id, count(*) as matches
from products 
inner join tags on products.id = tags.product_id
inner join (
    select t.id
    from products p
    inner join tags t on p.id = t.product_id
    where p.id = $product_id
) product_tags on tags.id = product_tags.id
group by products.id
order by products.id desc