Skip to main content
JBWebWorks
JBWebWorks
Inspiring
May 14, 2009
Question

Need help on mysql table setup

  • May 14, 2009
  • 1 reply
  • 1169 views

building a shopping cart for a company which provides replacement parts for screen rooms (aluminum panels, gutters, screws, etc).

in mysql i setup table for items, item category, orders, order detail, etc. (most items are available in 9 colors, though some only have 2-3 colors)

The part i need help on is setting up an optiongroup table for colors, option table of colors, how to write the sql JOIN and how to populate the list/menu choice of color to show on the catalog pages so a shopper can pick the color.

as you can see, i am not sure even if  i am asking the question properly?

thanks for any help,

jim balthrop

This topic has been closed for replies.

1 reply

JBWebWorks
JBWebWorksAuthor
Inspiring
May 15, 2009

I need help with the database setup?

Now in my item table i have a column, itemCOLORID and a table, 'item color' but that is not right. In the table, item color, it has two columns, itemCOLORID and itemcolorname.

1 = white 2 = bronze 3= royal brown and so forth

here is my sql query-

SELECT items.itemName, items.itemShortDesc, items.itemLongDesc, items.itemPrice, items.itemThumb, items.itemID, items.itemCatID, items.itemCOLORID, items.itemSKU, itemcolor.itemcolorname, itemcategory.itemCatName FROM items, itemcolor, itemcategory WHERE items.itemCatID = 3 ORDER BY items.itemName

i know i need some WHERE statement with a color group, but not sure how?

David_Powers
David_Powers
Inspiring
May 15, 2009
SELECT items.itemName, items.itemShortDesc, items.itemLongDesc,
items.itemPrice, items.itemThumb, items.itemID, items.itemCatID,
items.itemSKU, itemcolor.itemcolorname,
itemcategory.itemCatName
FROM items, itemcolor, itemcategory
WHERE items.itemCOLORID = itemcolor.itemcolorname
AND items.itemCatID = 3
ORDER BY items.itemName
JBWebWorks
JBWebWorksAuthor
Inspiring
May 15, 2009

David,

thanks for your response

what i am trying to do is use the WHERE itemCATID = 3 to take a shopper to a products page listing all the category 3 items

when they get to that page i want a list/menu for each item with the colors that item has available.

on the items table i have a column itemCOLORID.

my problem is setting up a table of color choices so the list/menu of an item would have only the color choices of that item

i am not sure how to identify the itemCOLORID; would it be number 1 corresponds to the color table and itemCOLORID number 1 has 9 columns with White, Bronze, Royal Brown, etc

itemCOLORID number 2 has 5 columns of colors and so on?

if that is on the right track, i am not clear on the dynamic populating of the list/menu?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices