SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Liverpool
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Updatintg Junction Tables when Raising an Order

    If we have a web order form as follows:

    Customer:
    Order Date:
    Reference:

    Item 1:
    Item 2:
    Item 3:

    The first 3 fields are entered into an order table. However, because a single order can have multiple items associated with it, the order items are listed in a junction table (order_id, product_id). However, the order (and hence order_id) does not exist when the webform asks for the order items.

    Therefore how does one go about administering INSERTS into the junction table (order_id, product_id) from this form when we can not know the order_id until a record has been added to the order table.

    I guess I am asking how conceptually a shopping basket type order system works by adding products to an order before the actual order record is created.

    I have developed a selection of simple applications on my company intranet but I am taking on a more ambitious sales order processing solution, where multiple tables need to be updated under a single transaction to satisfy several many-to-many relations. I am slightly surprised that magazines, tutorials, forums and web information is filled with simple web app ideas but none talk about the requirements of updating multiple tables when dealing with this sort of application.

    Any weblinks or ideas would be helpful. I was also considering taking a look under the bonnet of a simple PHP e-commerce app to see how it manages multiple table updates.

    I may also be attempting something far too ambitious which is why there isn't much info but my instinct tells me that it shouldn't be that difficult really.

    So far I can only think to process the initial order details first and then allow the user to add products once the order is created. But I would rather tie it into a single form not to mention avoid the creation of orders with no items associated with them...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Merlincraft
    Therefore how does one go about administering INSERTS into the junction table (order_id, product_id) from this form when we can not know the order_id until a record has been added to the order table.
    step 1: add a row for the new order to the orders table

    step 1a: obtain the auto_increment id that was assigned in step 1

    step 2: insert three rows into the junction table using the value from step 1a
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Liverpool
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Followup

    Is the best way to administer the transaction through a web single form to:

    1) Create an Order Record as soon as a user selects New Order to get a useable primary key.

    2) Performing an UPDATE through the remainder of the transaction to fill in the order details, including order items.

    3) If user cancels the order, initiate a DELETE on the order_id.

    Question: How do 'shopping baskets' typically do it, do they create a temporary table for the order, and then commit the order to a main order table upon final order submit?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no, i wouldn't do it that way

    even if you don't take abandoned orders into consideration, you've got too many database accesses

    besides, how do you know when the user abandons the order? remember, the web is stateless

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •