SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Returning record details from IDs listed in a multi-dimensional array

    I am trying to create a query to list product details about items in a shopping cart.

    The cart is based on a multi-dimensional array:
    $_SESSION['cart']['item_id']['quantity']

    How can I have the query return record details about each item (item_id) in the array?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Something like this?

    PHP Code:
    $items = array();
    foreach (
    $_SESSION['cart'] as $item_id => $item) {
      
    $items[] = $item_id;
    }
    $sql "SELECT * FROM table WHERE id IN (" implode(","$items) . ")"

  3. #3
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    Something like this?

    PHP Code:
    $items = array();
    foreach (
    $_SESSION['cart'] as $item_id => $item) {
      
    $items[] = $item_id;
    }
    $sql "SELECT * FROM table WHERE id IN (" implode(","$items) . ")"
    This looks good; should probably work. I have to wait a little bit before I can try it to be sure... but in the meantime I have one more small question:

    How can I also integrate the 'quantity' value of each item into the query so that I can return the total cost of each item (items.price x quantity)?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Morthian View Post
    How can I also integrate the 'quantity' value of each item into the query so that I can return the total cost of each item (items.price x quantity)?
    with a join to the other table

    i'd show you, but i don't know what your tables look like
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    with a join to the other table

    i'd show you, but i don't know what your tables look like
    Let's just say there is one table called 'inventory', with the following fields:
    'id' , 'name' , 'price'

    The array for the shopping cart is set up like this:
    $_SESSION['cart']['item_id']['quantity']

    So an example 'cart' array might look like:
    102 1
    104 3
    108 2

    For each 'item_id', we would want to get the value of 'inventory.price' multiplied by the corresponding 'quantity' to get the combined cost.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    wait a sec... the cart isn't actually a table?

    sorry, a join will not be possible

    loop through your cart, pick up the item_ids, and retrieve the inventory records like this --
    Code:
    SELECT id,price FROM inventory WHERE id IN ( list of ids )
    so basically just like dan said in post #2

    then do your array-matching application code voodoo to perform the multiplication
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    wait a sec... the cart isn't actually a table?

    sorry, a join will not be possible

    loop through your cart, pick up the item_ids, and retrieve the inventory records like this --
    Code:
    SELECT id,price FROM inventory WHERE id IN ( list of ids )
    so basically just like dan said in post #2

    then do your array-matching application code voodoo to perform the multiplication
    Hmm, I suppose I actually could use a table to keep track of everyone's carts if I just assign each visitor a unique key in a session variable.

    That would be much easier; I don't know why I didn't think of it earlier.

    Thanks for your help. =D

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    one of the advantages of storing the cart in a database table is that the user might have a connection failure (toddler yanks the internet cable out of the wall) or wish to do some other research (or visit the john) and the cart will still be available when she logs back in
    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
  •