PHP/SQL Selecting all records with the id's from a comma seperated list.
I have a table with groups:
And i have a table with members:
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]