PHP/SQL Selecting all records with the id's from a comma seperated list.

[stefan2]

I have a table with groups:
groups(id,name)

And i have a table with members:
members(id,name,groups)

Each member can join multiple groups. So for example a member could be in the "php experts"-group, the "sql-experts"-group aswell as the "html-experts"-group at the same time.

What i did was that i put the id's of all the groups that a member is in into a comma seperated list and saved it in the 'groups' field of the 'members' table.

My problem is that when, for example, i want to display each member in the database together with the groups that they are in i cant use a query to retrieve all the group names because the group id's are saved in a comma seperated list.

I would have to use PHP to make an array of the comma seperated list and then i could make a SQL-query that returns all the group names of each array item value.

This might work but i think its highly in-efficient. Esspecially if i were to post a list of lets say 50 members with their groups. 50 queries would need to be executed in order to make that list.

Maybe a comma seperated list isnt good for this situation. The problem is that i dont know how many groups each member will be added too so i cant use a seperate column for each group per member.

Anyway, do you have any tips to help me?

thanks in advance.

[PS] i posted this on another forum but after 24 hours i got no reply so now im posting the same here.[/PS]

[chazzy]

That's a bad idea. You can't search it like that. Make a separate table group_membership with pairs of id's: user_id's and group_id's.

[stefan2]

Ahh stupid i didnt think of that.

But you mean that if a member is in 3 groups you have 3 records for that member in that table right?

[chazzy]

yes.

so if you have 3 members, a,b,c and 4 groups w,x,y,z then your entries for membership would be:
a,x
a,y
b,z
b,x
c,w
c,y

just as example, mind you.