...

View Full Version : Selecting and joining troubles...



PapaB
05-13-2011, 06:50 PM
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!

Old Pedant
05-13-2011, 07:32 PM
$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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum