#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    459
    Rep Power
    80

    query using string items


    I have two tables. One stores the colors of an item and the other stores the associations. For the example below product 999 comes in red, green and yellow.
    How can I query these to output each color description for a product? Put it in one variable to change from "3,2,1" to "Red Green Yellow".

    attributes
    id, pid, att_color
    1 999 3,2,1

    colors table
    id descr
    1 Red
    2 Green
    3 Yellow
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,465
    Rep Power
    653
    Simply load the color table into an array with the index corresponding to the appropriate color. Display the color using att_color as the index.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,862
    Rep Power
    9646
    Better would be to fix your table. Storing comma-separated values like that only causes problems. The table should be normalized: attributes is just an id and pid, then there's an "attributes_colors" sort of table which has multiple rows of attribute ID and color ID.
    Code:
    attributes
    
    id | pid
    ---+----
    1  | 999
    
    
    attributes_colors
    
    aid | color
    ----+------
      1 |     3
      1 |     2
      1 |     1
    With that, a single query to retrieve everything is trivial.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    459
    Rep Power
    80
    Originally Posted by requinix
    Better would be to fix your table. Storing comma-separated values like that only causes problems. The table should be normalized: attributes is just an id and pid, then there's an "attributes_colors" sort of table which has multiple rows of attribute ID and color ID.
    Code:
    attributes
    
    id | pid
    ---+----
    1  | 999
    
    
    attributes_colors
    
    aid | color
    ----+------
      1 |     3
      1 |     2
      1 |     1
    With that, a single query to retrieve everything is trivial.
    That makes sense. So this way I would need a attributes_colors table, attributes_sizes table ect for each attribute?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,465
    Rep Power
    653
    Yes, in addition it looks to me like should be using join tables (many-to-many) for those attributes. That way each attribute can be associated with multiple items. This is really a MySQL issue rather than PHP. If you set up your schema properly the PHP part becomes simple.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    459
    Rep Power
    80
    Originally Posted by gw1500se
    Yes, in addition it looks to me like should be using join tables (many-to-many) for those attributes. That way each attribute can be associated with multiple items. This is really a MySQL issue rather than PHP. If you set up your schema properly the PHP part becomes simple.
    Thanks. I have it now where I save each attribute in its own table. size, color ect. Not all items will have a size or attribute assigned.
    Code:
    colors table
    id descr
    1 Red
    2 Green
    3 Yellow 
    
    sizes table
    id desc
    1 Small
    2 Medim
    3 Large 
    
    attributes_colors
    asscid, pid, att_color
    1        999         1
    2        999         2
    3        999         3
    4        998         1
    
    
    attributes_size
    asscid, pid, att_size
    1        999         2
    2        998         1
  12. #7
  13. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,862
    Rep Power
    9646
    Are all your attributes going to be ID and description? Or whatever fields as long as they all look the same? You could combine them.
    Code:
    table of attribute types
    
    id | type
    ---+-----
     1 | color
     2 | size
    
    
    table of attribute values
    
    id | type | desc
    ---+------+-------
     1 |    1 | Red
     2 |    1 | Green
     3 |    1 | Yellow
     4 |    2 | Small
     5 |    2 | Medium
     6 |    2 | Large
    
    
    table of pids and their attributes
    
    id | pid | value
    ---+-----+------
     1 | 999 |     1
     2 | 999 |     2
     3 | 999 |     3
     4 | 999 |     5
     5 | 998 |     1
     6 | 998 |     4

IMN logo majestic logo threadwatch logo seochat tools logo