Can I make this a single query?

[bruin03]

Say I have a products table of the following values

itemid itemname
1 shoes
2 shirt
3 hat
4 socks
5 umbrella


and a table of custom fields for each item with these values.

itemid fieldkey fieldvalue
1 color blue
2 color orange
2 size large
3 color red
4 size small
4 color blue
1 size 12mens

Could I write a single mysql query to show custom field values as columns for each item?

So that I can have a list of products and their respective sizes and colors e.g.
itemid itemname color size
1 Shoes blue 12mens
1

something like this perhaps?

SELECT products.itemid, products.itemname, (SELECT fieldvalue FROM custom_fields WHERE fieldkey = COLORS) as colors
FROM products
where colors.itemid = products.itemid

[r937]

Could I write a single mysql query to show custom field values as columns for each item?

in a word: no

do a search for entity-attribute-value (EAV) and discover why this scheme is generally regarded as a very poor design -- for just the reason that you have discovered, that it is almost impossible to get information out of it in an easy, meaningful way

[oddz]

You should separate this into two tables for options and values.

options
-- options_id
-- products_id
-- name

values
-- values_id
-- options_id
-- value

A product has many options
A option has many values
A value belongs to a option (which indirectly belongs to a product)

Once you have done that here is a typical query to grab a products its options and all their values.

SELECT
     p.products_id
     ,o.name
     ,v.VALUE
  FROM
     products p
  LEFT
  JOIN
     options o
    ON
     p.products_id = o.products_id
  LEFT
  JOIN
     `values` v
    ON
     o.options_id = v.options_id

[PHPycho]

I would go with: oddz.

Can anybody explain, what is EAV and it's importance. I have heard that Magento has used this concept.

Thanks.

[r937]

Can anybody explain, what is EAV and it's importance.

• Dave's guide to the EAV
  
• OTLT and EAV: the two big design mistakes all beginners make