Shopping cart, displaying product options

ade234uk

I am creating a small shopping and so far I have gone way beyond my own capabilites. Its been a struggle but is a great learning curve and something that I can keep for later reference.

I am stuck on the following.

When a product is added to the cart the following information is inserted in to the cart table

cart_id (This is a session id)
product_name (XBOX 360)
product_price (£23.00)
product_qty (1)
product_option_code1 (65376)
product_option_code2 (32211)

The product_option_code fields contain a 5 digit number that links to the products_options table.

So for example if a user selects the
Colour: Red from the select menu on the product page and also
Size: Small from the select menu on the product page,

These codes get inserted in to the cart.

DISPLAYING PRODUCTS FROM THE CART
I have managed to display all the products by the session_id that are in the cart.

I can display the product name and other information from the cart table. I am having a problem linking to the products_options by using the numbers in fields product_option_code1 & product_option_code2

Basically my output should look like this
XBOX 360 Quantity:1 Price: £23.00 [This information taken from cart]
Colour: Red (65376) [This information taken from products_options where code=65376]
Size: Small (32211) [This information taken from products_options where code=32211]

PHPBuilder TSHIRT Quantity:1 Price: £12.00 [This information taken from cart]
Size: Large (76321) [This information taken from products_options where code=76321]

Thank you for your help

Piranha

You have this problem because you haven't built a normalized database. And it will remain (and possibily be worse) until you have normalized the database. As an example, how would you handle a product that have 3 options? As it is now you have to rebuild the database and lots of code.

Instead put the options in another table with a reference to the product. I suggest that you search for "database normalization" to understand this and other normalization rules better.

ade234uk

Piranha wrote:
You have this problem because you haven't built a normalized database. And it will remain (and possibily be worse) until you have normalized the database. As an example, how would you handle a product that have 3 options? As it is now you have to rebuild the database and lots of code.

Instead put the options in another table with a reference to the product. I suggest that you search for "database normalization" to understand this and other normalization rules better.

Yes you are right. I really need to look in to this properly.