immanuelx2 Posted June 11, 2007 Share Posted June 11, 2007 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>"; Quote Link to comment Share on other sites More sharing options...
immanuelx2 Posted June 12, 2007 Author Share Posted June 12, 2007 bump, anybody? Quote Link to comment Share on other sites More sharing options...
btherl Posted June 12, 2007 Share Posted June 12, 2007 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 Quote Link to comment Share on other sites More sharing options...
immanuelx2 Posted June 12, 2007 Author Share Posted June 12, 2007 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 Quote Link to comment Share on other sites More sharing options...
bubblegum.anarchy Posted June 12, 2007 Share Posted June 12, 2007 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 Quote Link to comment Share on other sites More sharing options...
immanuelx2 Posted June 12, 2007 Author Share Posted June 12, 2007 thanks bubblegum, that worked like a charm Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.