PHP/SQL/AJAX - Update options in a dropdown menu based on previous option selected

Question

I am creating an ecommerce site.

At present I have a table called 'attribute_titles' like so:

ID  NAME
1   size
2   colour

I then have a table called 'attribute_values' like so:

ID  ATTRIBUTE_ID    VALUE
1   1               small
2   1               medium
3   1               large
4   2               red
5   2               green
6   2               blue

Then my products table will include an array of attributes that apply to the product in question so size and colour would be ['1','2']. I then have a pricing table which has a price for each variation of the product with a multidimensional array for the attribute. So a small red product would have ['1'=>'1','2'=>'4'] as the attribute.

I have a drop down for size and a drop down for colour on my product page, but not all combinations might exist so for example we only have a green product in size medium and large.

Therefore if someone chooses green as the colour, then I want to update the dropdown so the size small isn't shown.

At present they can just select any colour or any size and then I check to see if that combination exists in my pricing table and if not I just have a warning, but I don't want them to be able to choose an option that doesn't exist.

Any ideas or suggestions on how I can achieve this would be greatly appreciated.

you can handle the change event of the dropdown list using JavaScript and then update the content of the other dropdown to suit what was selected. (To get the right data you could either make an AJAX call to your server, or could have pre-loaded the lookup data into your JavaScript when the page was created). — ADyson
– ADyson, Commented Sep 25, 2018 at 15:54
Possible duplicate of update dropdown values based on another dropdown - jQuery — user2399035
– user2399035, Commented Sep 25, 2018 at 16:05

The Impaler · Accepted Answer · 2018-09-25 15:56:30Z

1

I worked in e-commerce before.

Your modelling is incomplete. What you are lacking at this point is the SKU entity (stock keeping unit). This entity models the existence of the concepts you outlined before. It usually also manages stock availability (that's where its name is derived from) for each combination of a product.

The e-commerce site usually shows "Products", not SKUs (directly). For example, a "V-neck Safari T-Shirt". In the back office you actually have multiple SKUs for that single product. You may have:

create table sku (
  product_id int foreign key product (id),
  color_id int foreign key color (id),
  size_id int foreign key size (id),
  units_left int
);

select * from sku;

color  size  units_left
-----  ----  ----------
Blue   M     10
Blue   L     6
Red    L     7
Red    XL    0 -- no stock of this one today!
Green  S     2

You also need to decide if you'll show SKUs that you don't really have stock of (Red, XL in the example above).

When more stock is available you just add those values to the SKU table. You may need to add extra rows if a new combination arrives (Green, M for example).

answered Sep 25, 2018 at 15:56

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paddy Hallihan Over a year ago

Thanks for your answer. Our site is a little different as in we don't have stock. We are like a reseller so we just take orders and generate purchase orders for the items. Also Colour and Size were just examples so we can have any number of attributes, we have nearly 100 in our store at present so creating a table like this will not work for me

Collectives™ on Stack Overflow

PHP/SQL/AJAX - Update options in a dropdown menu based on previous option selected

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related