Query all orderids for productids, for each order select order and product details

[boognish]

I have a basic 3 table structure for an order system …

• orders… contains order_id (as well as customer details and payment status)
• orders_to_products… contains order_id, product_id
• products… contains product_id (as well as some product attributes… colour, size etc)

I want to query the following…

• Get all order_id’s from orders table
• For each order_id, get all product_id’s from orders_to_products
• Then for every order_id in db, display/output the following per order…
• select from products every product attibute from every product_id from every order_id
• select from orders customer details, payment status

I can’t quite get my head around how to do this with MYSQL & PHP, can anyone help?

[r937]

it’s a three-table inner join –

SELECT ... FROM orders INNER JOIN orders-to-product ON ... INNER JOIN products ON ...

[boognish]

Fantastic, that was just what I needed. Thanks so much!