|
|
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
Navigation:
[Reply to this message]
|