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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

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

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

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

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