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

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?

it’s a three-table inner join –

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


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

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.