[thelist] reworded MySQL query insert and back again

rudy r937 at interlog.com
Mon Feb 10 16:14:01 CST 2003

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.


> 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"


> 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

10 widget
11 kumquat
12 wabbit

210  small
221  medium
222  large
399  wascawwy

101  grey
102  gray
110  even grayer

could you please illustrate your item_colour item_size relationships for the

 -- 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


More information about the thelist mailing list