order by.. different query.. ?

[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, 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># of Members</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>";

[pocobueno1388]

EDIT: Sorry, I just read your question again, and the code I posted wouldn't have worked. Reading through your question again, I did find an error in your code.

 

You are missing a parenthesis at the end of this line:

while ($group = mysql_fetch_assoc($group_query)

 

Just change it to:

while ($group = mysql_fetch_assoc($group_query))

 

You might want to post this in the MySQL forum.

[B34ST]

I dont fully understand what you are trying to do could u explain again please? however i did notice that yr query is called $group_query and you only used $group in the second query:

 

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

 

could possibly be

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

 

but im not sure if u have another var called $group

 

[immanuelx2]

let me re-explain...

 

I'm going to link the TH "# of Members" to "page.php?sort=members" so that i can sort by # of members...

 

the problem is... I can only find out how many members AFTER i pull the groups query, because the # of members is not a value in the groups query, but rather a relationship between the id of the group and the member group_id