[RESOLVED] Shopping cart project - product options

ade234uk

I am creating my own shopping cart again, to see how much I know about PHP. This is a project I come back to every 6 months or so, but when I start again I then realise what a task it is going to be.

One thing that is stopping me moving forward is the option to create options for the product and the best way of doing it. I think the way I am doing it is wrong.

I have shoes for sale
The user can select the size

I have T-Shirts for sale
The user can select the size
The user can also select the colour

Now I can can do this part. I basically check to see if the product_options_group_id field in the products table has some id numbers.

In this example the T-Shirts product have the following numbers in the product_options_group_id field, The number 1,2, relate to the T-Shirt size and T-Shirt colour.

To display the drop down menus, if the numbers exist they are put into a foreach loop, and each number queries the product_options table and pulls out the information and creates an option value. The mysql query groups the product_options_group_id numbers together, which is how it create the drop down menus underneath each product.

I hope the above makes some sort of sense. If you have created your own cart, what did you do for product options and what was the best way forward. I feel that I am going around this the wrong way, so your help would be appreciated.

djjjozsi

In this example the T-Shirts product have the following numbers in the product_options_group_id field, The number 1,2, relate to the T-Shirt size and T-Shirt colour.

In you database design you store options ID's in one field separated with commas? If yes, i suggest , change it.

you should build a connection table to make option and product ID pairs.

Then you don't need to hardcode the options - products lists.

Read a bit about database normalisation: http://databases.about.com/od/specificproducts/a/normalization.htm

Lets get familiar with JOINS in SQL language.

Like:

$product_id=1;
$sql="SELECT products.products_id , products.product_name, options.type_name , options.value 
FROM  option_connector 
INNER JOIN options ON option_connector.option_id = options.option_id 
INNER JOIN products ON option_connector.product_id = roducts.products_id
WHERE products.products_id = $product_id";

ade234uk

Thank you for this information. I guessed I was going wrong somewhere.

Krik

While that is a good suggestion I am wondering why one wouldn't just associate the options by storing the product id in with each option. And then just look up each option based on whether it is associated with that item or not.

$product_num
$sql="SELECT *
FROM  item_options
WHERE item_options.assoc_product_id = $product_num";

The main reason I suggest this isn't because of which is easier to look up but for the ease of adding a new option. If for every option you have to edit the product table you increase the risk of something getting messed up. Were if you just make the new option, and select the product it goes with, and then just store the option no change it needed for the product.

ade234uk

Thank you for the feedback also. If you look around on the web, you will find plenty of examples of a basic cart but no one approaches the subject of product options.

This product options has always been a sticking point.

This is how I do it.

Products_table

product_id - 1
product_name - PHP Builder T-SHIRT
product_price - 10.00
product_options_group_id - 1,2,

product_id - 2
product_name - 1GB Memory Stick
product_price - 15.00
product_options -

So we have the products in our products table above and, then below I have the product_options table

Product Options

product_options_unique_id - 2000
product_options_group_id - 1
product_options_grounp_name - T SHIRT SIZE
product_options_group_text - Small
product_options_group_price - 1.50

product_options_unique_id - 2001
product_options_group_id - 1
product_options_grounp_name - T SHIRT SIZE
product_options_group_text - Large
product_options_group_price - 1.75

product_options_unique_id - 2002
product_options_group_id - 2
product_options_grounp_name - Colour
product_options_group_text - Blue
product_options_group_price - 3.00

product_options_unique_id - 2003
product_options_group_id - 2
product_options_grounp_name - Colour
product_options_group_text - Orange
product_options_group_price - 3.25

1) So I query the products table, to check if the product_options_group_id field has a value in it.

2) If it does exist, then I use a foreach loop and take each number from the product_options_group_id field and use this to query the product_options table grouped by product_options_group_id which enables me to generate the drop down menu underneath each product.

Now is this the correct way?.

This way when the user adds a product with an option, the product_options_unique_id is added to the cart table. Later on this is used to identify what option was selected.

djjjozsi

@: Krik

I am wondering why one wouldn't just associate the options by storing the product id in with each option.

If i'm not mistaken , Your method looks like this:

product:

id , name
1 , table
2 , chair

options:

product_id , option_name, value
1, material , wood
2, material , wood
1, color , green
2 , color , green

my suggest was:

product:

id , name
1 , table
2 , chair

options:

option_id , option_name, value
1, material , wood
2, color , green
3, weight , heavy

connector_table:

product_id,option_id
1,1
1,2
1,3
2,1
2,2

Now adding a new product will not cause duplication in options table.
Its simple to create a dropdown from this options table, using the options table as pickup table.

In you version, how would you store the un-used options?

If i add new product, then i select from the dropdown list, or add new ones.
If i delete a product, its easy to delete from the connector table.

Hello, jjozsi.

ade234uk

Yes I have got it now. Thank you.

Krik

Sorry about that, my misunderstanding.

In you database design you store options ID's in one field separated with commas?

That statement made me think you were putting them in with product row. Probably didn't help any I recent had to fix a database were that had been done and it had made a mess.

ade234uk

Thank you very much for you all your help. I shall implement this idea and create a new stable structure to include a connector table. Much cleaner than putting values in one field/