[thelist] reworded MySQL query insert and back again

[rudy]

hi andrew

> I have a table that holds all the records for items.
> Each item has a colour and a size
> and could have more than one colour and size.

can an item have a colour but not a size?  size but not colour?  neither?

> So tables have been created to keep records for each item's
> colour and size.
>   ref_colours ref_size  hold the names and
>   item_colour item_size hold item - colour item -size relationship.

cool

> When an item is displayed its respective colours and sizes
> are pulled using ...
>    select ref_size.* from item_size ,ref_size
>        where ref_size.size_id=item_size.size_id
>           and item_size.item_id=$item_id

you seem to have the sql down pat

> when this item is selected by the visitor the item is held in a
> temporary table.

i don't understand why, but then, i don't understand a lot

> The colour and size are held in this table as
> size and colour but as the value not as the name, so when
> called back from the temp file I am getting the value
> not the name ie: 1 instead of blue.

if i understand you (and my ability to understand is open to question at the
best of times), you are not doing something right, but i can't -- yet -- see
what it is

> I guess I can do one f two things: I create a query to convert
> the value back to the name or the insert instread of being the
> value is the actual colour and size, unfortunately my head is
> starting to spin and will explode at any minute now !

please, please grant me permission to quote your words in an article i am
planning on database design -- i will probably not give your name (unless
you want me to)

your database design looks okay, but i'd like to confirm something first

no, you would not want to "create a query to convert the value back"

;o)

> oh this is the insert value if some can just spot an obvious error
> so that I can inmsert the name instead of the value?
>
>    echo "<SELECT name=\"cName\" value=\"$cName\">";
>    showColor($II);
>    echo "</SELECT> ";

actually, that looks okay


do you have two queries, one for the item's size names, one for the item's
colour names?

you said "The colour and size are held in this [temporary] table"

what exactly did you mean by this?

if i were displaying an item, i would not need a temporary table

  -- get item row out of item table
  -- display item fields
  -- get item sizes using item id
  -- display item sizes
  -- get item colours using item id
  -- display item colours

you said "ref_colours ref_size  hold the names and item_colour item_size
hold item - colour item -size relationship"

could you give me some sample rows?

let's say we have

items
10 widget
11 kumquat
12 wabbit

ref_size
210  small
221  medium
222  large
399  wascawwy

ref_colour
101  grey
102  gray
110  even grayer

could you please illustrate your item_colour item_size relationships for the
following

 -- widgets are small gray, medium gray, and large even grayer
 -- kumquats are small gray and small grey
 -- wabbits are only wascawwy, no colour


thanks and please don't forget that permission


rudy