SQL group results as a column array

Question

Hi guys, this is an SQL question and don't know which type of JOIN, GROUP BY etc. to use, it is for a chat program where messages are related to rooms and each day in a room is linked to a transcript etc.

Basically, when outputting my transcripts, I need to show which users have chatted on that transcript. At the moment I link them through the messages like so:

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 looks like this:

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 though for each element in the array to represent one transcript entry and for the users to be grouped in an array as the entry's element. So 'long' will be an array listing all the names. Can this be done?

At the moment I just append the names and when the transcript date and room changes I echo them retrospectively, but I will do the same for files and highlighted messages and it's messy.

Thanks.

Answer 1

You can not have an array as long field in your results, since each array element represents a row from a table. But you can have a concatened string instead, and split the string if it is required. The query will look like the following:

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