Geek asks and answers

PHP and MySQL questions on many issues

Still wrapping my head in 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'm trying to create is a user browsing page that lists all the users of the system along with the groups to which they belong, so the page will 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 .. and others.

For 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 the 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 returned results must be processed to create the comma separated groups shown earlier, for example.

, , , MySQL? PHP- ? - ?

.

+3
5

( ).

+2

. MySQL group_concat, . :

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
+2

group_id , , .

PHP , MySQL, GROUP_CONCAT .

, , , PHP-, .

+2

GROUP_CONCAT 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
+2

:)

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:)

0

More articles:


All Articles