Help with a query

[graham23s]

Hi Guys,

 

i currently have this query:

 

<?php
   // GET THE NUMBER OF ORDERS
   $qO = "SELECT DISTINCT(`CART_ID`) FROM `fcp_orders_master_log` WHERE `PROCESSED`='Y' AND `CUSTOMER_ID`='$cusID'";
   $rO = mysql_query($qO);
   $nO = mysql_num_rows($rO);
?>

 

This displays the number of orders each person has made bit in no particular order:

 

Graham 0 orders

David 2 order

Greg 1 order

 

i was tryiong to put a clause in so the customer swith the most orders are at the top, but i'm not sure what the clause would be lol

 

any help would be appreciated

 

cheers

 

Graham

[Mark Baker]

Please don't tell me you're retrieving a list of customers using one SQL query, then looping through each of those customers executing an individual query for each to retrieve the number of orders.

 

You should use just one query to retrieve both, with a GROUP BY CUSTOMER_ID. Then you can order by your CART_ID.

 

[jonsamwell]

 

SELECT CUSTOMER_ID, COUNT(CART_ID)

FROM `fcp_orders_master_log`

WHERE `PROCESSED`='Y'

GROUP BY CUSTOMER_ID

ORDER BY COUNT(CART_ID) [DESC], CUSTOMER_ID [DESC];

 

This will count the number of processed orders with the most orders at the top then further sorted by customer id descending

 

Regards

 

[graham23s]

Thanks guys, i have never been great with the old queries lol

 

cheers

 

Graham