SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update a shopping basket table

    hello
    i have a table that receives entries from a shopping basket something like this

    item1
    item2
    item3
    price1
    price2
    price3
    quantity1
    quantity2
    quantity3

    Now when a user checks out, the values of his basket are inserted into the above table.

    NOw after payment is verified, i want to be able to put the value each into a unique table, showing each[B] item he purchased.

    PROBLEM

    e.g. If there are 2 items in the shopping basket. then item3 and related tables woudl be empty

    So i would want something like this
    case items_purchased =
    select count(item1), count(item2), count(item3) from inserted
    for each item <> 0
    insert into purchased_item (item, quantity, price)
    values (item1, quantiy, price)

    next

    how do i achieve this correctly

    TAKING NOTE, That the values inserted item1 would change for each loop. ie values (item1, quantity1, price1)

    next
    (item2, quantity2, price2)

    etc...
    for each non null value

    thanks
    Afrika

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    insert into itemspurchased (item, price, quantity)
    select item1, price1, quantity1 
    from basket where quantity1 is not null
    union all
    select item2, price2, quantity2 
    from basket where quantity2 is not null
    union all
    select item3, price3, quantity3 
    from basket where quantity3 is not null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey Rudy,
    You are one great nice "friend"
    thanks once again.

    Have a nice day

    Afrika


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
  •