[SOLVED] Problem using ORDER and GROUP

[Buchead]

Hello all,

 

I'm having some trouble obtaining a order a result based on a count. I have 2 tables - customers (id, name) and bookings (bookid, custID, date)  - and want to order the output depending upon a request. The problem lies in that not all the customers may have placed a booking, yet they are still required to be shown with a count of 0.

 

I can get the query working via phpmyadmin using the command:

 

SELECT count(b.date) AS theCount, c.* FROM customers AS c LEFT JOIN booking AS b ON b.custID = c.id GROUP BY b.custID ORDER BY theCount ASC  (or changing this to DESC as required).

 

Yet when I put it into the php it doesn't work. Clearly I'm missing something obvious but can't see it myself.

 

In php I've tried the following just in case (in fact tried swapping line around a lot) but it also (unsurprisingly) fails:

 

SELECT c.* FROM customers AS c LEFT JOIN booking AS b ON b.custID = c.id GROUP BY b.custID ORDER BY COUNT(b.date) ASC

 

 

Thanks for any pointers,

 

Clive.

[Barand]

I'd group by c.id. Does that help.

 

<?php
$sql = "SELECT count(b.date) AS theCount, c.* 
        FROM customers AS c 
        LEFT JOIN booking AS b ON b.custID = c.id 
        GROUP BY c.id 
        ORDER BY theCount ASC";
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res))
{
    echo $row['id'], ':', $row['theCount'], '<br/>';
}
?>

[Buchead]

Thanks!