Topic awaiting preservation: PHP/MySQL query needed. :) (Page 1 of 1)

hello, i'm in the need of an SQL query to do the following:

imagine a common webshop environment with the following three tables involved with their important fields:

1. customers: list of customers, indexed by an id, fields: usual personal stuff

2. orders: list of all orders made, indexed by a order_id, fields: key to customer who made the order

3. individualorders: list of products contained in an order, fields: key to order that contains the product, product_id, quantity, price


example:
customer checks out with three times product A and twice product B in his shopping basket.
then there would be one entry in "orders" containing the order-data, and two entries in "individualorders" containing the two products, their quantities and prices.

ok, so far, i hope i could explain this understandable.

now, what i need is a query that gets me a ranking list of the customers who have purchased specific products, sorted by the summed price of these products and ordered from highest amount to lowest. imagine the products are donations and i want to see which customer donated the most.

if its possible, all that in one SQL-query.

the mySQL version is 4.0, which seems to be missing full support for "AS" and "IN"

i would be very grateful, if you could help me out, or even for the slightest hint.

Does something like this get you want you want?

SELECT customers.id, customers.name, sum( individual_orders.price * individual_orders.quantity ) AS total, individual_orders.product_id
FROM customers, orders, individual_orders
WHERE individual_orders.order_id = orders.id
AND orders.customer_id = customers.id
GROUP BY individual_orders.order_id
ORDER BY total DESC

Also, I don't know if it's too far along at this point in your project, but shouldn't there be a products table? You could store the product price there and not have the redundancy of storing it over and over again in the individual_orders table.

Hope this helps.

- Butcher -

you store the price in the individual_orders table because that's the price you sold it for.

That way, you can later on change the product price, without all previous orders going out of wack.

Cool, thanks TP.

I've not done any ecommerce stuff so I wasn't thinking that way.

- Butcher -

butcher, thank you very very much!
your query is already doing very well, only thing i still need is to select the purchases of certain products only. here is what i tried:

SELECT cubecart_customer.customer_id, cubecart_customer.*, SUM( cubecart_order_inv.price * cubecart_order_inv.quantity ) 
 AS total, cubecart_order_inv.productId 

FROM cubecart_customer, cubecart_order_sum, cubecart_order_inv 

WHERE cubecart_order_inv.cart_order_id = cubecart_order_sum.cart_order_id 
 AND cubecart_order_sum.customer_id = cubecart_customer.customer_id 
 AND ( 
 cubecart_order_inv.productId = 6 
  OR cubecart_order_inv.productId = 7 
  OR cubecart_order_inv.productId = 8 
  OR cubecart_order_inv.productId = 9 
 ) 

GROUP BY cubecart_order_inv.cart_order_id 

ORDER BY total DESC

6,7,8,9 are the ids of the products.

unfortunately this does not work. it doesnt give me any results at all.



(Edited by GRUMBLE on 02-10-2006 16:39)

quote:

---

GRUMBLE said:

AND ( cubecart_order_inv.productId = 6

---

You have your OR's in palce after this, but here you are telling mysql to only give results where the productId = 6.

Sounds like a job for JOIN...

ok thanks. i think i got this to work:

(i removed the price times quantity, cause i noticed that the order-table already holds the correct sum)

SELECT cubecart_order_sum.time, cubecart_customer.customer_id, cubecart_customer.*, cubecart_order_inv.price AS total, cubecart_order_inv.productId 

FROM cubecart_customer, cubecart_order_sum 

INNER JOIN cubecart_order_inv ON cubecart_order_inv.productId = 6 OR cubecart_order_inv.productId = 7 OR cubecart_order_inv.productId = 8 OR cubecart_order_inv.productId = 9 

WHERE cubecart_order_inv.cart_order_id = cubecart_order_sum.cart_order_id AND cubecart_order_sum.customer_id = cubecart_customer.customer_id 

GROUP BY cubecart_order_inv.cart_order_id 

ORDER BY total DESC

BUT: i just noticed another big problem. maybe i missed explaining this in my original questions, but the ranking list should be per user and not per purchase.

example: a user makes 2 purchases:
1. he buys a product for $100
2. he buys two products. one for $25 and one for $50

then his line in the ranking table should show:

username, $175

currently it shows:
username, $100
username, $25

(the remaining $50 are also missing? )


thank you everyone who is still following me. i am very grateful.



(Edited by GRUMBLE on 02-10-2006 17:50)

Looks like you want to change it to GROUP BY the customer ID instead of the order id.

thanks ok, that is grouping the customers, but how do i sum up, all prices of purchases they make?

Off the top of my head I'm not certain (and my brain is a bit fried after the day I've had at work...)

You are grabbing the price, AS 'total' but it is still only grabbing the price of each individual item and sending that as total.

You need a way of telling it at that point in the SQL to add all the prices together as 'total'.

Sorry I can't be more helpful with it at the moment...

I noticed the part of the statement with

SUM( cubecart_order_inv.price * cubecart_order_inv.quantity )

missing from your last version of the statement. Would that make the difference?

- Butcher -

thanks again guys!

of course i was missing the SUM. (dude, how obvious was that? guess i've embarrased me enough in this thread, by asking stupid questions)

in case you are interested here is the final query:

SELECT cubecart_order_sum.time, cubecart_customer.customer_id, cubecart_customer.*, SUM(cubecart_order_inv.price) AS total 

FROM cubecart_customer, cubecart_order_sum INNER JOIN cubecart_order_inv ON cubecart_order_inv.productId = 6 OR cubecart_order_inv.productId = 7 OR cubecart_order_inv.productId = 8 OR cubecart_order_inv.productId = 9 

WHERE cubecart_order_inv.cart_order_id = cubecart_order_sum.cart_order_id AND cubecart_order_sum.customer_id = cubecart_customer.customer_id 

GROUP BY cubecart_customer.customer_id 

ORDER BY total DESC

ill probably have another big query to do, but ill open a new thread for that.
thanks!