Sorting a MySQL query based on another table...

listenmirndt

So I have two tables,

ORDERS and PRODUCTS

In the PRODUCTS table I have a field called CATEGORY.

I would like to sort the ORDERS results based on the CATEGORY field in the PRODUCTS table...

$SQL = "SELECT * from ORDERS";
$result = @( $SQL ); while( $row = @mysql_fetch_array( $result ) ) {

bradgrafelman

So how does ORDERS relate to PRODUCTS?

Could you perhaps show us a CREATE TABLE syntax for the tables involved?

listenmirndt

ORDERS has a field called PRODUCT which has the unique ID number of the product from the PRODUCTS table.

PRODUCTS has a field called CATEGORY which uses the unique ID number of the CATEGORY from the CATEGORIES table.

johanafm

SELECT o.field1, o.field2, c.catname, p.prodname
FROM order AS o
INNER JOIN product AS p ON o.product = product.id
INNER JOIN category ON p.category = c.id
ORDER BY c.catname

If your orders can (or ever will be able to) contain more than one product, you need to restructure your relations. Order should contain whatever goes for the entire order, such as billing and shipping adresses, order id etc.
orderrow, orderproduct or whatever you'd like to call it should contain everything that concerns one single item in the order such as product, quantity, price (at the time of purchase; no guarantee that price lookup in product table will be unchanged) etc.