Automatic price update with size choice

This is maybe not really an eCart question, but it is important for our online shop.

I understand how to built a dynamic catalog page and a detail page, which are linked. Now our products are available in several versions, and each version in several sizes. On the detail page I would like the customer to choose the size and version from a menu, and whenever he makes a choice the price and the item number automatically update.

With the detail page I have right now this won't work, because it represents only one record. I guess in order to make the choices the detail page must represent all possible versions and sizes of one item, and probably the link on the catalog page to the detail page must somehow link to a number of records. But this is just a guess, and I don't know how to implement it anyway.

Would someone have the patience to help me out?

I did something similar to this recently, so I will try and explain how I did it as best I can.
Apologies for any errors / omissions, so please post back for clarification...

If your products are priced based upon size, then you could create 2 new tables for your database, I'll call them tblsize and tblprice, to allow you to create a many to many relationship between items and prices

tblsize is essentially a reference table and would have the following fields...

SizeID
Description

tblprice, which would contain all of the item prices could have the following fields...

ItemID
SizeID
Price

Where ItemID is the same as the itemID in your items table and SizeID is as in tblsize.

When populated, this table will give you several sizes and prices per product.

On your item detail page, you can query your price look up table which is filtered using sizeid and itemid variables posted from the add to cart form.

Then you can pass the price returned from the query into the cart array.

Hope this helps

Hi Craig,

Thank you for your help.

I spent the last two hours trying to make sense of your post. When it comes to databases I feel like a toddler.

So here is what I understand:

tblitem
ItemID | Name __ |SizeID
1 ____ | Product a | ?

tblsize
SizeID | Description
1 ____ | M
2 ____ | L

tblprize
ItemID | SizeID | Price
1 ____ | 1 ____ | 10.00
1 ____ | 2 ____ | 12.00 (or does the ItemID in this table have to be unique?)


I don't know what to put into the SizeID field marked with the question mark. This is where the customer should make the choice. If I predetermine a value like "1", wouldn't that be just the same as putting the actual size "M" in there?

And to make it a little more difficult our products come in different sizes and versions. Here is an example:
- Product A, Size 1, version a, price=10.00
- Product A, Size 2, version b, price=22.00
- Product A, Size 1, version b, price=12.00
- Product A, Size 2, version a, price=20.00

So, for the table tblprice I could not enter a price, because it would still depend on the version the customer picks.

And here you lost me completely
"On your item detail page, you can query your price look up table which is filtered using sizeid and itemid variables posted from the add to cart form. Then you can pass the price returned from the query into the cart array."

Unless I can click on the lightening bolt I am lost. I have not the faintest knowledge of PHP.

Do I need to hire a specialist? I mean, is there any chance for a beginner to pull this off?

You don't need sizeid in the item table, if you think about it, each item can have more than one size, so this field is redundant.

tblsize is fine, as is tblprice.

sizeid in tblsize has to be unique

itemid in tblprice cannot be unique as you need multiple entries for each item.

If it makes things clearer, the combination of itemid and sizeid (a compound key) is unique for each row, but don't worry about that too much.

As you mentioned, your situation is slightly more complex, but not much.

Imagine you have a version table as well as a size table, it works in the same way.

tblprice now needs an additional field, versionid.

so for an item with 2 versions and 2 sizes, there would be 4 rows in tblprice for the same itemid, does this make sense ?

If you wish, you can pm me or send me an email (look at my profile or go to my website) if you want to discuss this in detail.

As far as the lookup query is concerned, this does take a little bit of hand coding, but I or the support guys at Webassist can help you with that.


good luck

Craig,

it makes a lot more sense now. It will take some time though to fully grasp the concept. So I probably won't post again before tomorrow or very late tonight :)

Thank you for the offer to help me further. I talked to our internet provider, and he will set up an external test server. Maybe it will make things even easier, if we can actually look at the pages.

Here is my summary

tblitem
ItemID | Name __ | ItemNo
1 ____ | Product a | 1234

So the tblitem contains all the info that does not change like a part of the item no., contents, thumb and detail jpg, laundry sign ...

tblsize
SizeID | Description
1 ____ | M
2 ____ | L

tblversion
VersionID | Description
1 ____ | x
2 ____ | y

tblprize
ItemID | SizeID | VersionID | Price | completeItemNo
1 ____ | 1 ____ | 1 ______ |10.00| 1234-x-M
1 ____ | 2 ____ | 1 ______ |20.00| 1234-x-L
1 ____ | 1 ____ | 2 ______ |12.00| 1234-y-M
1 ____ | 2 ____ | 2 ______ |22.00| 1234-y-L

tblprize would also contain other information like a code number unique to that item.

I will try to create the test tables tomorrow morning. I hope I don't bother you too much.

I ended up with 16 tables, and I find data entry is very complicated. I essentially created a table for every column where there could be a duplicate entry.

I know I need the extra table for version and size. But from an expert's view, do I need all the other tables (like pictures etc.), or should I make data entry easier and have certain entries repeat row after row?

It is OK to have some duplication in your tables so In my opinion a new table for something like picture is not needed.

A new table for the price was needed to make you application work, I think that is the best way to determine if you really need to create the new table or not creating a new table for the picture makes the application more cumbersome, and does not effect how the application performs, so it is unnecessary.

i can help you with database structure etc.

difficult to advise without seeing it

Hi Craig,

I am still trying to find the best way to set the tables.

I think we can all together skip tblversion and tblsize, because that info could easily be entered directly into the table tblprice. Would make data entry much easier.

So far I would end up with two tables.

The shop is also planned in four languages. So while some of the info stays the same, like itemNo, price, size, version etc, some of the info will change with language, like itemName and contents.

tblproduct
itemID, itemName, productCatagory, LaundrySign
1____, productA_, 1____________, 95.jpg

tblprice
itemID, itemNo__, price_, size, version, contents, jpg _, procuctCode
1____, 1234-M-x , 10.00, M__, x_____, abc ___, a.jpg, 5678
1____, 1234-L-x_, 12.00, L __, x_____, abc ___, a.jpg, 6789
1____, 1234-M-y , 14.00, M__, y_____, abf ___, b.jpg, 8901
1____, 1234-L-y_, 16.00, L __, y_____, abf ___, b.jpg, 9012

I can't quite wrap my head around, how to solve the language problem. I know there will have to be a language table.

tblLanguage
itemID, LanguageID
1____ , English
2____ , German
3____ , French
4____ , Spanish

The categories affected by the different languages are itemName and contents in this example.