[SOLVED] order by ... 2 queries involved..

[immanuelx2]

In the following code, i am doing 2 different queries:

1 to loop through the group names

1 to loop through which members belong to the current group, and finding out how many using mysql_num_rows...

 

However, if you look in the TH i want to be able to ORDER BY the group query by how many members are part of that group, but I'm finding this hard because I can only find out the number of members AFTER I pull the group query... Any ideas?

 

echo "<table><tr><th>Group Name</th><th><a href="?sort=members"># of Members</a></th></tr>";

$group_query = mysql_query("SELECT name, id FROM groups");

while ($group = mysql_fetch_assoc($group_query))
{

     $member_query = mysql_query("SELECT * FROM members AS M, groups AS G WHERE M.group_id = '$group[id]'");

     $member_count = mysql_num_rows($member_query);
     $group_name = $group['name'];

     echo "<tr><td>".$group_name."</td><td>".$member_count."</td></tr>";
}

echo "</table>";

[immanuelx2]

bump, anybody?

[btherl]

You can store the data in an array in php and sort it before displaying.

 

As for doing it in mysql, you can do this (untested, also I had to guess your column name for G.group_id):

 

SELECT name, id, count(*) AS member_count FROM groups G JOIN members M ON (G.group_id = M.group_id) GROUP BY name, id ORDER BY count(*) DESC

[immanuelx2]

hmmmm, im a little confused with the query you gave me...

 

how do i know which name, id, count you are taking? (G or M)?

also where would I put that query? for the while loop, or inside the while loop?

 

thanks in advance

[bubblegum.anarchy]

SELECT groups.id, groups.name, count(members.id) AS member_count
FROM groups
LEFT JOIN members ON groups.id = members.group_id
GROUP BY groups.id
ORDER BY member_count DESC

[immanuelx2]

thanks bubblegum, that worked like a charm