[RESOLVED] Accessing and using multiple id's in one MySQL field

jme

Hi Guys. I am currently working on a Self Assessment System and have to allow different privileges (as far as ability to edit) to thousands of users. We have decided to use a system of 'groups' which will dictate who can do what.

At the moment I have a group table which consists of:
ID (pk)
GrpTitle
GrpDesc
GrpMembers

I was hoping to have in the GrpMembers field a list of the UserID's of members assigned to that group. Maybe stored as a comer separated list (1,56,86,77,65,23, etc ...)

If I did this what would you suggest is the best way to authenticate this on the script?

The UID of the user is set as a $_SESSION['uid'] variable when the user logs on. Is it possible to search through a comer separated list and looking to see if that UID appears in it?

Any guidance or ideas on this would be great.

Thanks in advance.

Jamie

mwasif

use FIND_IN_SET() mysql function to search through a comma separate list. Or you can get all the user ids in PHP and use explode() function to make an array and then use in_array() function to find the user id.

laserlight

jme wrote:
I was hoping to have in the GrpMembers field a list of the UserID's of members assigned to that group. Maybe stored as a comer separated list (1,56,86,77,65,23, etc ...)

A comma separated list in a column is a multi-valued field, which is discouraged from use in relational databases since it makes it more difficult to get the desired data out of the database.

It seems that members can belong to more than one group, and of course a group can have more than one member. As such, you have two entities with a many to many relationship between them. What you should do instead is have three tables:
Group: id, title, description, ...
Member: id, name, ...
Group_Member: group_id, member_id

The Group_Member table maps each member to a group. group_id would be a foreign key to the Group id primary key, member_id would be a foreign key to the Member id primary key, and its own primary key would be the combination of group_id and member_id.

With such a setup, getting the groups that a member with id $SESSION['uid'] belongs to is as easy as executing the query:

SELECT Group.*
FROM Group, Group_Member
WHERE Group.id=Group_Member.group_id
    AND Group_Member.member_id=$_SESSION['uid'];

EDIT:

mwasif wrote:
use FIND_IN_SET() mysql function to search through a comma separate list. Or you can get all the user ids in PHP and use explode() function to make an array and then use in_array() function to find the user id.

Those would be workarounds for a poorly designed database. If you have the choice, choose the better, normalised, database design.

jme

Thanks for both your comments. As I have the choice over how the database is going to be I think that the second option would be better.

That's really helped.

Jamie 🙂