Selecting and joining troubles...

Hi,

I'm currently setting up a small shop using PHP & MySql and have been stuck all day on one small feature.


I basically want to display an order which has been placed in the database.

From this I need to take information from 2 tables, 'order_prod' and 'products'.

In 'products' I have product_id, product_name, product_desc and product_price

In 'order_prod' I have order_id, product_id, order_quan


One benefit is that when the user is on this page I've made it so they already have the order_id in the address bar, so this can be brought into the code using '$_GET['order_id']'.

My problem is just linking them so that I can show the product_name, product_desc, product_price from 'products' and order_quan from 'order_prod' which are relevant to the order_id


If anyone can help I appreciate it, this is making my brain turn inside out as the answer is probably simple

Thanks!

$sql = "SELECT P.product_id, P.product_name, P.product_desc, P.product_price, O.order_quan, P.product_price * O.order_quan AS subtotal "
. " FROM products AS P, order_prod AS O "
. " WHERE P.product_id = O.product_id "
. " AND O.order_id = " . $_GET["order_id"]
. " ORDER BY P.product_name";

... now execute that query and get records from it ...

The ORDER BY is arbitrary. You could ORDER BY whatever field you prefer.