PHP and MySQL many-to-many question

Question

Still wrapping my head around SQL and PHP, but hope someone can help with this:

I have the following tables:

1.

user table
- id
- name
- email

2.

user_group table
- user_id
- group_id

3.

group table
- id
- group_name

There is a many-to-many relationship between the user table and the group table. Now what I am trying to do build a browse users page which lists all the users in the system along with the groups that they belong to, so the page would look something like this:

Name: John Doe
Groups: football, tennis, swimming

Name: Jane Doe
Groups: hockey, basketball

Name: Jim Doe
Groups: hockey, football, rugby

etc. etc.

To accomplish this, I have the following SQL:

SELECT `user`.name, `group`.name 
FROM `user`, `user_group`, `group` 
WHERE `user`.id = `user_group`.user_id 
  AND `group`.id = `user_group`.group_id 
GROUP BY `user`.id, `group`.id

which returns results as follows:

1. John Doe | football
2. John Doe | tennis
3. John Doe | swimming
4. Jane Doe | hockey
5. Jane Doe | basketball
etc. etc.

As you can see, the results returned need to be manipulated in order to produce the comma separated groups shown earlier, as .

Is there a simple way to get the page to display the groups so that they are in a comma separated list for each user in MySQL? Or do I have to write PHP code to loop through the results looking for duplicate IDs and generating the comma-separated lists of groups on the page? Or am I doing something completely wrong in my approach?

Many thanks.

I can't think of a way to do it in one query atm, but I would fetch a list of all the users first, then loop through that list SELECTing the group.name WHERE user.name = $username — rcapote
– rcapote, Commented May 30, 2011 at 8:20

Wesley van Opdorp · Accepted Answer · 2011-05-30 08:19:36Z

2

There are a few options (in order of my personal preference).

Don't group by user id, and iterate trough your result and create an multi dimensional array using the user id as a key.
Use GROUP_CONCAT, which isn't pretty.
Use separate queries for selecting all groups + users, and iterate to create an multi dimensional array.

answered May 30, 2011 at 8:19

Wesley van Opdorp

15k4 gold badges43 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wesley van Opdorp Over a year ago

It can have unforeseen issues with exceeding it's maximum length, and you don't receive the grouped information together when you need more then just the name, and requires you to map them yourself anyway.

vbence · Accepted Answer · 2011-05-30 08:21:04Z

2

It makes no sense to have group_id in your user table if it is many-to-many and you already have a connecting table.

From PHP I guess you use MySQL, so you can use GROUP_CONCAT in situations like this.

Anyway, you are querying a hierarchical structure, which can not gracefully flatted to a single table, so you will always have to do some PHP coding to get the hierchical structure back.

answered May 30, 2011 at 8:21

vbence

20.4k10 gold badges74 silver badges119 bronze badges

1 Comment

user775925 Over a year ago

Thanks, the extra group_id field was a typo on my part. Removed now.

xkeshav · Accepted Answer · 2011-05-30 08:27:18Z

2

try with GROUP_CONCAT with INNER JOIN

SELECT user.name, GROUP_CONCAT(group.name) FROM user
INNER JOIN user_group ON user.id = user_group.user_id 
INNER JOIN group ON group.id = user_group.group_id 
GROUP BY user.name

answered May 30, 2011 at 8:27

xkeshav

54.2k47 gold badges183 silver badges255 bronze badges

Comments

John Hargrove · Accepted Answer · 2011-05-30 08:44:46Z

2

You're in luck. MySQL has a very handy aggregation operator group_concat which allows you to collapse the grouped results into a single row. In your case it would go something like this:

SELECT
  `user`.name,
  GROUP_CONCAT(`group`.group_name)
FROM `user`
INNER JOIN `user_group` ON (`user_group`.user_id = `user`.user_id)
INNER JOIN `group` ON (`group`.group_id = `user_group`.group_id)
GROUP BY `user`.name

edited May 30, 2011 at 8:44

answered May 30, 2011 at 8:20

John Hargrove

7016 silver badges23 bronze badges

Comments

Gabriel Spiteri · Accepted Answer · 2011-05-30 08:38:58Z

0

This will match your table structure :)

SELECT  `user`.name, GROUP_CONCAT(  `group`.group_name ) 
FROM  `user` 
INNER JOIN  `user_group` ON (  `user_group`.user_id =  `user`.id ) 
INNER JOIN  `group` ON (  `group`.id =  `user_group`.group_id ) 
GROUP BY  `user`.name

HTH :)

answered May 30, 2011 at 8:38

Gabriel Spiteri

4,98812 gold badges47 silver badges61 bronze badges

Collectives™ on Stack Overflow

PHP and MySQL many-to-many question

5 Answers 5

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related