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]
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.
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?
so if you have 3 members, a,b,c and 4 groups w,x,y,z then your entries for membership would be:
just as example, mind you.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)