Need help writing sql query

[JBWebWorks]

i am trying to write sql query for a single recordset.

I have an items table with all the standard item info and an item_colorID.

i have a color_lookup table with 2 columns, item_colorID and color_ID

i have a colors table with 2 columns, color_ID and color

i want to join the tables and filter it so that a repeat region shows dynamic data of item name, description, thumb, price

and also a list/menu dynamically populated by color

filtered so that each item shows in the list/menu only the colors that the item has available.

i have tried different variations of this sql

SELECT * FROM items INNER JOIN color_lookup ON color_lookup.item_colorID = items.item_colorID INNER JOIN colors ON colors.color_ID = color_lookup.color_ID WHERE items.itemCatID = 3 ORDER BY items.itemName

but the list/menu shows every color choice multiplied by the number of items in that color

ie  White will show 80+ times.

thanks for your help,

jim balthrop

[bregent]

Hi Jim, it's hard to say for sure since your post is somewhat confusing. First of all, the query you listed doesn't seem appropriate for a color list menu since it only returns columns from the item table, not the color table.

I would also guess that you have a database design problem. It appears that color_lookup table is not a lookup table at all, but an association table ( aka link or junction table). Its purpose is to create a many to many relationship between items and colors. If this is the case, then the item table should not have a item_colorID column at all. The association table should contain foreign key columns for the itemCatID and the ColorID and should contain a row for each combination. If this analysis is not correct, then please include more info about the schema and provide sample data.

[JBWebWorks]

thanks for your response

the items table has 6 columns -

item_ID, itemcatID(which is just the category of items),item_colorID,item_description,item_price,item_thumb

the color_lookup table has 2 columns which are joint primary indexes

color_ID and item_colorID

the colors table has 2 columns

color_ID and colors

colors table (total of 10 colors)

color_ID          colors

1                    white

2                    bronze

3                    royal brown

4                    forest green

5                    almond

6                    ivory

7                    clay

8                    linen

9                    colonial gray

10                  charcoal

color_lookup table

color_ID          item_colorID

1                    1

1                    2

1                    3

2                    1

2                    2

3                    1

4                    1

5                    1

6                    1

7                    1

8                    1

9                    1

items table

item_ID          item_colorID

1                    1

2                    1

3                    2

4                    3   

so item 1 comes in 9 colors

item 2 comes in 9 colors

item 3 comes in 2 colors (white and bronze)

item 4 comes in 1 color (white)

[bregent]

JB, sorry I got busy over the weekend and couldn't get back to you. Based on your reply I see that your design is fine and your are using ColorID to represent a set of colors that several items could have. I don't know if I would have done it this way, but again I don't know anything about the products either

I'll take a closer look at the query later today if you are still having problems.

[JBWebWorks]

bregent,

thanks for your help.

I am building a shopping cart and i have a recordset to list the items and a repeat region for that recordset

i have a second recordset for the colors joined to the item_colorID nested inside the repeat region.

the shopping cart software has a 'lookup from recordset' choice for the add to cart servior behavior

and then i bind to the columns on the cart page.

it produces this code

if (isset($totalRows_rs_itemscat3) && $totalRows_rs_itemscat3 > 0)    {
    $row_rs_itemscat3 = WAEC_findRecordMySQL($rs_itemscat3, "item_ID", $ATC_itemID);
    if ($row_rs_itemscat3)    {
      $ATC_itemName = "".$row_rs_itemscat3['itemName']  ."";// column binding
      $ATC_itemDescription = "".$row_rs_itemscat3['itemShortDesc']  ."";// column binding
      $ATC_itemWeight = floatval("".$row_rs_itemscat3['itemWeight']  ."");// column binding
      $ATC_itemQuantity = "".$_POST["Farrington_1_Quantity_Add"]  ."";// column binding
      $ATC_itemPrice = floatval("".$row_rs_itemscat3['itemPrice']  ."");// column binding
      $ATC_itemThumbnail = "".$row_rs_itemscat3['itemThumb']  ."";// column binding
      $ATC_itemcolorchoice = "".$row_rs_colors['color']  ."";// column binding
      mysql_data_seek($rs_itemscat3, 0);
      $row_rs_itemscat3 = mysql_fetch_assoc($rs_itemscat3);
    }
  }

the column binding for the colors is from a different recordset and when redirecting to the cart page the color info will not show.

So my thinking is if i could get the color list/menu to populate from the same recordset as the item listing, it would solve my add to cart server behavior.

Is it possible to do this with only one recordset?

the products page and the cart page can be seen

http://www.farrington-enterprises.com/rain-gutters.php

add an item to the cart with any color choice and the color info does not carry to the cart.

[bregent · Accepted solution]

Hi JB, I think I understand the situation but of course I am not familiar with the interface of the cart software you are using. It seems that you will not be able to use the 'lookup from recordset' choice because the color is from a different recordset. You can't really include the color in the main recordset because that would cause the main repeat region to display the item once for each available color, which is not what you want.

I see also that the color drop down is in its own form. If you placed that dropdown within the main form, would the behavior recoginize it and treat it differently? In other words, does the cart accept options from input outside of the main form element it is using:

<input type="text" name="Farrington_1_Quantity_Add" value="1" size="4" maxlength="3">

I'm sure the cart software has other methods to add to cart which you may be forced to use.

What cart software are you using? Do they have a forum which you could get a more targeted assistance?

[JBWebWorks]

bregent

thanks for your help

i placed the select colors inside the add to cart form and it worked by binding the select to the color choice column on the cart page with this

<?php echo((isset($_POST['colors']))?$_POST["colors"]:""); ?>

http://www.farrington-enterprises.com/rain-gutters.php

[bregent]

Excellent!