[SOLVED] Selecting columns from two different tables

[FETNU]

Hey Guys (and girls),

I just started learning PHP and MySQL and I've used this site for so much help!  Now, I finally came to a point where I needed to ask a question.

 

I'm creating a web application where I have a master table (ITEM_DETAILS) that contains EVERY item and all the necessary data associated with that item (item_id, item_price, category, cost, size, etc...).  I have another table (SHOPPING_BASKET_TEMP) that stores the items that have been added to the shopping cart.  This table contains the item_id, item_qty, category, price, etc...

 

What I need to do is... when the page opens, select ALL the items from ITEM_DETAILS for a certain category.  Second, I need to look at SHOPPING_BASKET_TEMP and if the user has that item in their cart (based on their session_id, item_category and item_id), then I need to select the item_qty for that item, else, I need to return a item_qty of 0.

 

I've tried joins, unions, various selects and I've had absolutely NO luck.  Can somebody PLEASE help to send me in the right direction?

 

Any help would be GREATLY appreciated!

 

    ~FETNU

[fanfavorite]

From what I get from your description, why would you select all items from Item_details (mysql_query("select * FROM ITEM_DETAILS")).  Basically this is what I understand:

 

- Select the users information entered into the shopping cart

$q = mysql_query("select * FROM SHOPPING_BACKET_TEMP WHERE session_id = '$session_id' AND item_category = '$itemcategory' AND item_id = '$itemid'")

 

- get all records and set quantity in array

while($f=mysql_fetch_array($q)) {

$qty[$f[item_category]] =  $f[item_qty];

}

 

- then when displaying quantity, if there isn't a quantity for the item, then put 0

if ($qty[$itemcategory]) {

echo $qty[$itemcategory];

} else {

echo "0";

}

[Wildbug]

SELECT * FROM items_table AS a LEFT JOIN shopping_cart_table AS b ON a.id=b.item_id WHERE b.session_id='whatever' AND a.category = 'something'

 

Quantity will either be an integer or NULL if the item isn't in the shopping cart.

[FETNU]

I ended up doing it like this:

 

$query = "SELECT a.*
     , COALESCE(b.ITEM_QTY,0) as ITEM_QTY 
  FROM ITEM_SELECTION a
LEFT OUTER
  JOIN SHOPPING_BASKET_TEMP b 
    ON b.ITEM_ID = a.ITEM_ID
   AND b.BASKET_SESSION_ID = '$sid'
WHERE a.ITEM_CATEGORY in ('TOYS') 
   AND a.ACTIVE_IND = 'Y'
ORDER BY a.ITEM_NAME, a.ITEM_SIZE ASC";

$result = mysql_query($query);

 

Thanks for the responses!