Front-end product variants in PHP+MySQL?

Question

I'm doing a webshop, and every product can have several variants.

All variants are actually their own product. The SKU is different per product, but the productnumber is the same for all variants of "one product".

The variant combinations is held in a seperate table called variantvalues. In there is the product SKU, fieldname and fieldvalue.

Let's say I have 3 products. SKUs: 0505002699/SB/M, 0505002699/SC/L, and 0505002699/SC/M.

All products have the same productnumber; 0505002699.

The products have the following fields: Color, Size.

These are the values for each product:

0505002699/SB/M

Color: Black

Size: Small

0505002699/SC/L

Color: Blue

Size: Medium

0505002699/SC/M

Color: Red

Size: Small

---

Anyone with a clue on how I can loop that front end? Final result should be something like: https://i.stack.imgur.com/rAxeG.png

The problem (for me) is to figure out how to list the possible "configurations".

Anyone?

Answer 1

Just a quick thought of implementation:

If the "configurations" are variable for each product:

Extra tables:

size
  - code
  - descr

fe:

S - Small
M - Medium

And then a table between product and size:

product_size
   - product_id
   - size_code

fe:

1 - S
1 - M
2 - S
...

For the color:

product_color
   - product_id
   - color

fe:

1 - #FFFFFF
1 - #000000
2 - #000000
...

Then you can select all options for each product by product_id.

fe:

SELECT color FROM colors WHERE product_id = $product_id;
SELECT size_code FROM product_size WHERE product_id = $product_id;

EDIT: wow i misread a part of your question, sorry

Answer 2

While Nathan's answer is workable, you might consider a more generic version (not strictly syntax checking here, just taking a stab):

create table configurations (
 id int auto_increment primary key,
 product_id int not null,
 name varchar(50) not null,
 value varchar(255) not null default "",
 FOREIGN KEY (product_id) REFERENCES product(id)
)

Then you can create new types of configurations (configurations.name) at a whim and deal with them in code.

select value from configurations where product_id=$product_id and name='color';
select value from configurations where product_id=$product_id and name='size';

Now, the downside of this approach is that inventory might be an issue, so the other way to deal with this is to have them as individual items in your item table (product table?) that are related to each other. Combine them in code for your view page, but the inventory for all the blue shirts can be maintained separately from the red shirts.