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

[Morthian]

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?

[Dan Grossman]

Something like this?

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

[Morthian]

Something like this?

$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)?

[r937]

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

[Morthian]

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.

[r937]

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 --

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

[Morthian]

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 --

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

[r937]

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