[thelist] MySQL table structures with PHP

Anthony Baratta Anthony at Baratta.com
Wed Jan 22 14:18:01 CST 2003


At 12:08 PM 1/22/2003, Andrew Maynes wrote:

>I have a table (Products) with ItemID and want to add a field to this
>table that
>then is associated with another tables records.
>
>The extra field needs to be colour and each item could have up to 8 different
>colours asscocited with it.. can someone point me in the right direction for
>this?

Why not just create a Colors x Items Cross Walk Table

cw_ColorxItem

         cw_ID int auto_increment
         ItemID int
         ColorID int

refColors

         ColorID int auto_increment
         ColorName varchar(25)

This way you can query the Products table and use the cross walk table to
get the associated colors:

select Products.ItemID, Products.Name, refColors.ColorName
         from Products
         join cw_ColorxItem on cw_ColorxItem.ItemID = Products.ItemID
         join refColors on refColors.ColorID = cw_ColorxItem.ColorID
where Products.ItemID = '$itemID'

Hope that helps.
--
Anthony Baratta
President
Keyboard Jockeys

"Conformity is the refuge of the unimaginative."




More information about the thelist mailing list