Need Help with formulating a Query

[vincej]

Hi - I'm struggling formulating a query:

 

I have purchase orders. Each purchase order can have several line items. My table holds line items by purchase order id . See sample below.

 

+ Options

orderid orderdate customerid prodid price quantity notes dellocation id name

JAC1297 1320251297 5 22 155.25 1 NULL varsity 22 Turkey Jumbo Pack

JAC1297 1320251297 5 20 120.35 1 NULL varsity 20 Chicken Variety Pack

JAC1297 1320251297 5 25 50.00 1 NULL varsity 25 50 piece bag of Scalops

JAC6251 1320176251 5 25 50.00 1 NULL varsity 25 50 piece bag of Scalops

JAC0209 1319750209 5 22 155.25 1 NULL varsity 22 Turkey Jumbo Pack

JAC0209 1319750209 5 23 20.00 1 NULL varsity 23 Whole Chicken

 

I'm struggling to build a query which aggregates all the line items for a given orderid so that I can do a php foreach and echo the orderid ONCE with the associated product / price quantity etc underneath.

 

The query I have built only grabs the first product for each orderid and leaves out the rest, see below:

 

SELECT orderid,  order.orderdate, order.prodid, order.price, order.quantity, products.name, sum( order.price * order.quantity ) AS 'Sales'
FROM `ORDER` , `products`
WHERE order.customerid = '5'
    AND order.prodID = products.id
AND order.Status = 'open'
   	GROUP BY orderid
ORDER BY order.orderdate DESC
LIMIT 0 , 30

 

 

 

MANY MANY Thanks to all those who might be able to offer any ideas !

 

 

Using MySQL 5.5

 

[Wiro Blangkon]

SELECT <fields you need> FROM ORDER
WHERE ORDER.id=<ORDER ID HERE>

 

or

 

SELECT <fields you need> FROM ORDER
LEFT JOIN ORDER.id=PRODUCT.OrderID

 

That would be the core of what you need. Furthermore, you might want to consider using INTs as your Order ID instead of Strings. INTs have handy things with them, such as AUTO_INCREMENT. Don't know if you can do indexing with Strings, I think not.

 

Good luck!

Wiro Blangkon.

[fenway]

That's becuase you have GROUP BY.

[Wiro Blangkon]

You can leave out the GROUP BY and do a simple SELECT with ORDER BY OrderID. Then PHP loop throught the records, printing out every OrderID once if it is different from the one in the previous record.

[vincej]

Thanks I'll give it a try !

[Wiro Blangkon]

Do drop a note to let us know about your progress.