Table joins output - best practice

DeSoto

Hi guys (and girls),
I've been thinking a bit about how to handle result sets when you join tables in one-to-many relationships.

Let's say we have a users-table, and a messages-table. The users-table has a one-to-many relationship with the messages-table. When you join them, you get something like this as a result:

+--------+----------+-----------+---------+
| UserID | UserName | MessageID | Message |
+--------+----------+-----------+---------+
| 1      | Bob      | 1         | Hi!     |
| 1      | Bob      | 2         | Hello!  |
| 1      | Bob      | 3         | Wow!    |
| 2      | David    | 4         | Hey!    |
| 2      | David    | 5         | Hey you |
+--------+----------+-----------+---------+

Now, if I want to display this, and only show the username one time, what is the best way of doing this? Should I store the user id in a temp variable, and check in the loop if it has changed, or is there a more convenient way of doing this?

laserlight

Have you considered a GROUP BY UserName or a GROUP BY UserID?

DeSoto

Well, wouldn't that mean only showing one of the messages sent by the users?

laserlight

wouldn't that mean only showing one of the messages sent by the users?

Yes, it would. Basically, you want to display all the messages, but only display each sender name once? I think you just have to ORDER BY the sender name then, and then do the checking in PHP.

DeSoto

Yeah, that's what I thought. It just makes me feel a bit uncomfortable because it feels very ugly hack.

Weedpacket

Not really; since you do want each record, and you do want them to be associated with the right users, you will need to retrieve thata data anyway. Questions of how you want to display the resulting dataset should be irrelevant at this point.

pppppp

first order by user id and then chek each user id with the next userid if it is same then dont display the next userid..