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
adding feilds to a DB to be displayed in a select list
Moderator: General Moderators
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: adding feilds to a DB to be displayed in a select list
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,
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 | 5Code: 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-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: adding feilds to a DB to be displayed in a select list
thanks i will look at this. dont mind extra work on the backend to get it working. will let the forum know
thanks again
thanks again