PLEASE HELP: Add Session Data to Database on Checkout

[designrandom]

Hi guys,

 

I really need your help - not so much with code, just need an idea on how to do the following:

 

I am building an ecommerce site selling cricket bats. For each cricket bat you can have a number of options including different sizes, widths, handle size, colour etc... (The list of choices is huge).

 

I have created the cricket bat table in the database, which stores the product information (Bat ID, Bat name, price, image).

 

The options table has seperate columns for Bat Size, Bat Weight, Bat Face Material, Bat Color etc...

 

I have an orders table, a customers table and an order contents table. The order contents table will store the product ID's for each item in the order.

 

When the user adds a cricket bat to their shopping cart, I store this info in a SESSION Array, with a unique ID.

I'm not sure how to get this information into the database when the user checks out... here's why:

 

When the user submits their order, I thought I should enter the customer details into the customer table first, then the OPTIONS next. So for each product in the users shopping cart (let's pretend the user is ordering 3 cricket bats, with 3 different sets of options (different sizes, colours etc))  - For each bat, I need to create a record in the options table.

 

Now I have optionID's 1, 2 and 3.

 

I now add the product ID's to the "product" column in the orderContents table. How do I tie each produt to the set of options it needs to go with?

 

If any one can give me so much as an idea on how to do this, I would really appreciate it.

 

Thanks.

 

 

[cags]

Not sure I fully followed you, but it sounds like its a many to many relationship. In order to normalise the structure you would have another table called something like product_options, that would simply contain product_id, options_id and possibly it's own id field just for the fun of it. As I don't fully understand the structure of your cricket example, let me explain in a format you should understand (I'd hope, since you sell cricket bats).

 

Lets say I wanted to organise a database for tracking a cricket league. The league will obviously consist of teams and it's not much of a team unless it's got players. Now as with most league's a player can only play for one team at a time, but they can be transfered. So our tables would be something like...

 

Teams

team_id, team_name, venue, etc..

 

Players

player_id, player_name, player_speciallty (position), etc..

 

Team_Players

id, player_id, team_id, join_date, exit_date, etc..

 

Little off track, but hopefully you can take something from it...