Page 1 of 1

adding feilds to a DB to be displayed in a select list

Posted: Mon Sep 10, 2012 11:58 am
by jonnyfortis
Hello
I am getting a bit stressed on this one.

I have an add product form that will be sent to a php MYsql DB.

i need to add sizes and colours of the product them this needs to be displayed in a select list for the end user, ( i can do this bit )

but i need to know the nest way of adding the sizes / colours. the only way i can think of is to manually add example 10 feilds to the product table then have text fields in the form to name the colours / sizes then they can be shown in a select list for the end user.

the trouble with this is the end user can add any more colors if need be and if there are less colors than 10 the select list will show blank lines.

I really need a push in the right direction

thanks in advance

Re: adding feilds to a DB to be displayed in a select list

Posted: Mon Sep 10, 2012 5:23 pm
by requinix
Create three tables: one for possible attributes (size and color), one for the variations (small, medium, large, and red, blue, green, etc.), and one for associating an attribute with a product (product #1 (a sock) has attribute variation #5 (color blue)). This is the "correct" way but requires a bit more effort up-front to get working, but it pays off in that you can create as many attributes and variations as you want without having to change anything.

To add a new attribute add a row to the attributes table and add rows in the variations table. To query the attributes on a product do a JOIN across all three tables.

Using my naming style,

Code: Select all

products

id | ...
---+-------
1  | a sock

attributes

id | name
---+-------
1  | Size
2  | Colour

attributevariations

id | attribute | variation
---+-----------+----------
1  | 1         | Small
2  | 1         | Medium
3  | 1         | Large
4  | 2         | Red
5  | 2         | Blue
6  | 2         | Green

products_attributevariations

product | variation
--------+----------
1       | 5

Code: Select all

SELECT a.name AS attribute, av.variation
FROM products_attributevariations pav
JOIN attributevariations av ON pav.variation = av.id
JOIN attributes a ON av.attribute = a.id
WHERE pav.product = 1

attribute | variation
----------+----------
Colour    | Blue

Re: adding feilds to a DB to be displayed in a select list

Posted: Mon Sep 10, 2012 5:35 pm
by jonnyfortis
thanks i will look at this. dont mind extra work on the backend to get it working. will let the forum know

thanks again