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.

Answer 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.