Confused! Varchar(), Array or not?

[Spanito]

Could someone please steer me in the right direction.
I have a product line. Lets say:

productname, style, qty, colors, size

Now let's say that when inputting this data into the fields we know that we are going to type in data into the productname, style, and qty. So those columns I don't have a problem with.
I have a problem with colors, and size.
My problem is this, I know that I can have colors entered in a SET.,,,,, but not all colors are available to all products... so a SET does not work for colors. So I thought about it, and came up with just typing in the colors into a varchar(100), for example:
Green,Blue,Yellow,Pink <----- would be entered for a certain productname..... but,
the next product may have:
Orange,Black,White,Brown

Now, is this the way you folks would handle this?
And then what would I do or use to break up the colors to appear on a PHP page on a drop down menu since it has been entered in a Varchar(100)?

Is there not a way to store this info in an array?
or I'm I going about it the wrong way?
I've thought about putting the colors in another table?
But that just confuses the hell out of me more. LOL

Any hints on my problem would be great!
Should I use explode() ?

Thank you,
Spanito

[Dvector]

Why not have a column type SET with all available colors, and only select the colors for the particular product, using a multiple select box or something?

Unless you are going to have more then 64 colors, then a set should work. Each product may not have all colors available to it, but the set will hold only the colors you chose.

Otherwise, if you chose to do a varchar(100), when you do your select statement you will need to explode your result by commas (or whatever you delimit you colors by).

$colors = explode(",", $colors_available)

$colors[0] is the first color,
$colors[1] is the second,
etc . . .

[mrhappiness]

Why not using more than one table?

if you have one table for your products, one for the colors available and one for storing which color is availbale for which product you save space (don't have to write yellow a thousand times) and it's very easy to search for products available in yellow but not in black for example

if you store all colors available in a column of your product table you may have red,yellow,black,blue,green or red,black,yellow,bleu,green and so it's kind of difficult to find the products mentioned above

btw: you noticed the typo? bleu instead of blue
that's something you can avoid by having a table with all colors available and simply refernce them

//select data
$sql = 'SELECT
  p.product_id, p.product_name,
  c.color_id, c.color_name
FROM
  colors c
  INNER JOIN product_colors pc ON c.color_id = pc.color_id
    INNER JOIN products p ON pc.product_id = p.product_id
ORDER BY
  p.product_sort,
  c.color_name';
$r = mysql_query($sql);

//retrieve data
while ($products[] = mysql_fetch_assoc($r));
array_pop($products);

//create output data (use <ul>)
$former_product_id = -1;
$result = '<ul>';
foreach ($products as $product) {
  if ($product['product_id'] != $former_product_id) {
    if ($former_product_id != -1)
      $result .= '</ul></li>';
    $result .= '<li>'.$product['product_name'];
    $result .= '<ul>';
    $former_product_id = $product['product_id'];
  }
  $result .= '<li>'.$product['color_name'].'</li>';
}
$result .= '</ul></li>';

//output data
echo $result;

//output original data
echo '<pre>';
print_r($products);
echo '</pre>';

see attached file for table layout and content

[Sxooter]

what Mr. Happiness said ++