将相同的ID显示为单个ID

Question

I have selected data from database and here is the result:

+--------+------------+
|  room  |    name    |
+--------+------------+
|   12   | John       |
|   13   | Marry      |
|   14   | Camilla    |
|   14   | Beatrix    |
+--------+------------+

And how can I print to my page by php code like this :

12 : John
13 : Marry
14 : Camilla, Beatrix

thanks for helping :)

Answer 1

You may want to use Concat and Group_concat to meet your exact need

select
concat(`room`, ' : ',group_concat(`name` separator ',')) as `Name`
FROM test
group by room;

Check Demo Fiddle Here