SQL group results as a column array
- by Radek
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.
