www.webdeveloper.com
Results 1 to 4 of 4

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

  1. #1
    Join Date
    Jun 2005
    Posts
    11

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

    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]

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  3. #3
    Join Date
    Jun 2005
    Posts
    11
    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?

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles