SQL group results as an array of columns

Question

this is a SQL question and don't know what type of JOIN, GROUP BY etc. use, this is for a chat program where messages are associated with rooms, and every day in the room is associated with decryption, etc.

In principle, when displaying my transcripts, I need to show which users chatted over this transcript. At the moment I am linking them through these messages:

SELECT rooms.id, rooms.name, niceDate, room_transcripts.date, long 
 FROM room_transcripts 
  JOIN rooms ON room_transcripts.room=rooms.id 
  JOIN transcript_users ON transcript_users.room=rooms.id AND transcript_users.date=room_transcripts.date 
  JOIN users ON transcript_users.user=users.id 
 WHERE room_transcripts.deleted=0 AND rooms.id IN (1,2) 
 ORDER BY room_transcripts.id DESC, long ASC

The result set is as follows:

Array
(
    [0] => Array
        (
            [id] => 2
            [name] => Room 2
            [niceDate] => Wednesday, April 14
            [date] => 2010-04-14
            [long] => Jerry Seinfeld
        )

    [1] => Array
        (
            [id] => 1
            [name] => Room 1
            [niceDate] => Wednesday, April 14
            [date] => 2010-04-14
            [long] => Jerry Seinfeld
        )

    [2] => Array
        (
            [id] => 1
            [name] => Room 1
            [niceDate] => Wednesday, April 14
            [date] => 2010-04-14
            [long] => Test Users
        )

)

I would like for each element of the array to represent one record in the transcript for users who were grouped into an array as an input element. Thus, "long" will be an array listing all the names. It can be done?

, , , , .

.

Answer 1

long, . , . :

SELECT rooms.id, rooms.name, niceDate, room_transcripts.date, group_concat(long) as long
 FROM room_transcripts
  JOIN rooms ON room_transcripts.room=rooms.id 
  JOIN transcript_users ON transcript_users.room=rooms.id AND transcript_users.date=room_transcripts.date 
  JOIN users ON transcript_users.user=users.id 
 WHERE room_transcripts.deleted=0 AND rooms.id IN (1,2)
 GROUP BY room_transcripts.id
 ORDER BY room_transcripts.id DESC