[SOLVED] 2 table, need best way to avoid too much queries

[asmith]

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

 

 

[fenway]

Try:

 

select * from groups as g inner join members as m on ( m.group_field = g.name )

 

[mrmitch]

Try and play with something like this to see if you can get the exact result set you're looking for.

 

select a.name. a.site, count(b.member_id), group_concat(b.member_name SEPARATOR ',') from groups a left outer join members b on b.group_id = a.id group by a.id