Posted by David CZ on 05/31/07 12:41

This is just going a little over my head. I'm using a database
containing products, which, among other data, can have associated
tags, any number of them. Until now I used a serialize()'d PHP array
which I stored in a simple VARCHAR field with each product. Since I
want to be able to select products which have certain tags via SQL
though that'll need to change. My idea was to make a DB like this:

products [id, name, ...]
tags [id, name]
product_tag [product_id, tag_id]

The product_tag table would look like this:

product_id | tag_id
12 1
12 5
42 1
42 21
42 9
....

So any product can have any number of tags. Pretty basic db design as
far as I've heard. ;o)
I just can't figure out how to best SELECT my products now. I'm trying
this for example:

SELECT products.id, products.name, tags.id
FROM products INNER JOIN (tags, product_tag)
ON (product_tag.product_id = products.id AND product_tag.tag_id =
tags.id)
WHERE products.id = 12;

Which gives me two lines of results:

12 Product1 1
12 Product1 5

I would like a result like this though:

12 Product1 1,5

How can I summarise everything into a single result line with the tags
collapsed into a single field, something I can unserialize() or
explode() or something similar.

Thanks a lot, Cheers,
Dav

[Back to original message]