info in 2 different table, less queries

[asmith20002]

Hey guys

I want to echo tabular data on an html with while loop. (mysql_fetch_array)

2 of the fields in this html table, are the tab information in another mysql table. for example :

html table :
name site members_num special_members

ocean site1 7 john
sea site2 4 alex, tom


there's a table : groups which contains the name field and site field.
there's a table : members which contains those names and a group field which by that I find out in which group that members is .

the code I try to avoid is this : (because of making too much queries)

$query = mysql_query ("select * from GROUPS");
while ($the_group = mysql_fetch_array($query))
{
$res = mysql_query("select * from MEMBERS where group_field= $the_group[name]");

mysql_num_rows($res) --> for html table members_num column.

}

my point is . if the first query returns 100 rows, then in my while loop it does 100 queries and that's a lot.

Any idea?

Thanks

[chazzy]

use inner join


select g.*,m.* from groups g inner join members m on m.group_field=g.name;

[asmith20002]

Thanks for the reply.

How do you count the numbers of the members in each group ? (for the html members_num field, mysql_num_rows part in my code)

I mean with mysql , I can write script with php to count that. is there a direct sql function for that?

[chazzy]

counting the # of members in this case is better to do in php, rather than sql. once you convert all of the entities to objects, it should be clear enough to count the # of members in the members array.

[asmith20002]

Ok,

Thanks a lot man , It helped me a lot :)