Creating a group of users in PHP and MySQL

Question

I'm wondering how I can use JOIN or LEFT JOIN to group users.

users table:

id (primary)
user (varchar)
pass (varchar)
email (varchar)
website (varchar)
bio (text)
avatar (varchar)
code (varchar)
active (varchar)
admin (varchar)
group_1 (int) <--- I have a feeling that this is the wrong way to go about it
group_2 (int) <---
group_3 (int) <---

groups table:

group_id (primary key)
group_name (varchar)
group_bio (text)
group_website (varchar)

I was wondering how I could let a member be part of several groups and to show a list of members of a certain group..

Something like this:

Group Name
Users: <a href='profile'>Name</a>, <a href='profile'>Name</a>, <a href='profile'>Name</a>, <a href='profile'>Name</a>

I have absolutely no idea how to go about this. Please help.

Solution 1

You want a standard many-to-many relationship.

This is usually implemented by having three tables.

1. Users
2. Groups
3. Memberships

The memberships table will have two foreign keys, which map on to the other two tables.

Solution 2

You have to create a new table MEMBER_GROUPS with two fields: user_id and group_id.
Every row is a membership of a user...
In this way you can easily query user membership in a group...

Solution 3

This is indeed the wrong approach, as it restricts a user to three group memberships. This is a classic many-to-many relationship, and you can model it using a bridge table:

user_group
----------
user_id
group_id

The primary key will be a composite of user_id and group_id, enforcing uniqueness. Then you would do a double-join through this table. For example, to get all of the members of all of the groups:

SELECT group.*, user.*
FROM group
LEFT OUTER JOIN user_group
ON group.group_id = user_group.group_id
LEFT OUTER JOIN user
ON user_group.user_id = user.user_id

Or, if you are after a specific group:

SELECT user.*
FROM user_group
INNER JOIN user
ON user_group.user_id = user.user_id
WHERE user_group.group_id = ?

You could even get a list of all groups, with membership count:

SELECT group.*, COUNT(user_group.user_id) AS group_membership_count
FROM group
LEFT OUTER JOIN user_group
ON user_group.group_id = group.group_id
GROUP BY group.group_id

Comments

[Admin]

Could you please explain what foreign keys are and how I could go about doing this? You're saying that the Membership table would basically say User is member of Group, that kind of thing..?

[Quentin]

A foreign key is a column in a database table that contains the same value as the primary key of a row in another table. A decent database (i.e. not MySQL with a MyISAM storage engine) will enforce keys pointing to real rows. There is a good example at the bottom of the page I linked to in the first paragraph of my answer.

[Admin]

So, would I have more than 1 row for each user? user_id, group_id 1, 1 1, 2 1, 3 etc?

[Marco]

@Hugo: exactly! One row for every group each user is in