Need help on mysql table setup

[JBWebWorks]

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

[JBWebWorks]

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]

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]

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?

[David_Powers]

What you need to do is to create a SET field for the colours. The MySQL documentation describes how to use the SET data type here: http://dev.mysql.com/doc/refman/5.0/en/set.html.

Basically, when creating the table, you define the colours that are valid members of the SET, e.g., black,brown,red,orange. When inserting records, you insert the available colours as a comma-separated string:

INSERT INTO myTable (description, colours)
VALUES ('t-shirt', 'black,orange')

To search for SET values, use FIND_IN_SET(), as described on the page linked to earlier.

The alternative is to create a lookup table for the available colours. A lookup table normally consists of two columns, which are declared as a joint primary key.

colour_id	colour
1	black
2	white

item_id	item
1	t-shirt
2	polo shirt

colour_id	item_id
1	1
1	2
2	2

That tells you that t-shirts are available in black only, but polo shirts are in black and white.

(Edited to get the colours the right way round.)

[JBWebWorks]

David,

thank you for your help.

using your example, i have column, itemOptionGroup, in table 'items'
have a table, OptionGroup, with columns 'GroupID' and 'GroupName'
have a table, Options, with columns 'OptionID' 'OptionGroupID' and 'OptionName'

as a test,
populating items.itemOptionGroup with value '1'
populating OptionGroup.GroupID with value '1' and OptionGroup.GroupName with value '1'
populating Options.OptionID and Options.OptionGroupID with value of '1'
populating Options.OptionName with value of 'white,bronze,royal brown'

clicking test in the Dreamweaver recordset returns the values of colors correctly.  'white,bronze,royal brown'

i can't seem to get the list/menu to populate correctly using dynamic option.

[JBWebWorks]

David

I figured out the SET values

now i have an Options Table with Row of OptionID , OptionGroupID, and OptionName

OptionGroupID '1' has four rows or colors, White, Bronze,Royal Brown and Forest Green

OptionGroupID '2' has two rows of colors, Bronze and Clay

my sql is this

SELECT * FROM items INNER JOIN OptionGroup ON  items.itemOptionGroup = OptionGroup.GroupID INNER JOIN Options ON Options.OptionGroupID = OptionGroup.GroupID WHERE items.itemCatID = 3

on the Dreamweaver list/menu

i am using

options from recordset rs_raingutters

values:  OptionName

labels:   OptionName

Select Value Equal to: itemOptionGroup //I thought this would filter the list/menu to show only the OptionName for the Items OptionGroup, but it shows every OptionName?  First Item is from OptionGroup '1' but it shows the colors from both '1' and '2'?

thanks for your help,

jim balthrop

[JBWebWorks]

David

In my items table i have a field 'itemOptionGroup' it is an index and an item would have a value of 1, 2, or 3

This is Options table

OptionID  itemOptionGroup  OptionName

1                  1                     White

2                  1                     Bronze

3                  1                     Royal Brown

4                  1                     Forest Green

5                  2                     Bronze

6                  2                     Almond

7                  3                     White

8                  3                     Bronze

my sql is

$query_rs_raingutters = "SELECT * FROM items, Options WHERE items.itemOptionGroup = Options.itemOptionGroup AND items.itemCatID = 3";

i have a repeat region to show 10 results

In the region is 4 dynamic text and 1 list/menu

In a browser it shows the same item 30 or more times and times out?

As a test, I removed the list/menu and the browser shows without timeing out. An item that has 4 colors, shows 4 times, item with 3 colors, shows 3 times, and so on?

Don't know if the problem is in my sql or the way i have my tables set up?

thanks for any help

jim balthrop

[JBWebWorks]

David

i have set up tables using your 'lookup table' as an example

items table with item_ID as primary key

colors table with color_ID as primary key

color_lookup table with color_ID and item_ID as joint primary key

Here is my sql

$query_rs_raingutters = "SELECT * FROM colors INNER JOIN color_lookup ON colors.color_ID = color_lookup.color_ID INNER JOIN items ON items.item_ID = color_lookup.item_ID ";

I have tried this in reverse order SELECT * FROM items INNER JOIN ........

when i click test in the Dreamweaver recordset, i get multiple records of a single item with the number of records depending on the number of colors the item comes in.

What i am trying to do is have a list of items displayed from the recordset and a repeat region showing each item listing with a menu of the color choices of that item only.