SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can I make this a single query?

    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?
    Code MySQL:
    SELECT products.itemid, products.itemname, (SELECT fieldvalue FROM custom_fields WHERE fieldkey = COLORS) as colors
    FROM products
    where colors.itemid = products.itemid

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by bruin03 View Post
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,042
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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.

    Code SQL:
    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

  4. #4
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,200
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by PHPycho View Post
    Can anybody explain, what is EAV and it's importance.
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •