CODEWITHSUNDEEP
phpmysql
Josh Potter
Josh Potter

Reputation: 1719

Combining rows then grouping them?

I have recently began working on a facebook like chat messenger. I'm trying to organize it to where I can show each user their recent contacts. In order to do this, I need to set up my table a certain way. Here is the following way my query is right now:

$query = "SELECT uid1, uid2, message, timestamp FROM messages WHERE (uid1 = '$user[id]' OR uid2 = '$user[id]') ORDER by timestamp DESC LIMIT 10";

Here is the way my chat client is set up. Lets say we have UserB and UserC. If UserB sends UserC a message, UserB will be uid1 and UserC will be uid2. If UserC sends UserB a message, UserC will be uid1 and UserB will be uid2.

That way, it is clear anyone who is uid1 is the one who sends the message, and uid2 is the one receiving it. However, in my situation, i'm trying to display recently contacted users. By doing this, I need to grab the most recently contacted users. I need to grab both UID1 and UID2 results where the users username is located AND on top of that I need to make it to where it doesn't show the user duplicated rows. That way, when displaying recent contacts it doesn't show me the same user 5 times because we had 5 messages going back and forth. This way it'll only show me it once, and it'll show other users I have recently contacted as well (with a Limit of 10).

I began looking for a way to do this but it seemed pretty difficult. I need to use the "GROUP BY" or "DISTINCT" key to make sure the results don't show duplicated rows. However, this is rather difficult because UID1 and UID2 can be exchanged. So I thought, maybe I can group by UID1 and UID2. Here was my query:

$query = "SELECT uid1, uid2, message, timestamp FROM messages WHERE (uid1 = '$user[id]' OR uid2 = '$user[id]') GROUP BY uid1, uid2 ORDER by timestamp DESC LIMIT 10";

I'm not sure why, but this does not show me the most recently contacted. So it occured to me that maybe I need to combine UID1 and UID2 results somehow, and then add in a DISTINCT or a GROUP BY. Does anyone know how to make this work?

Upvotes: 1

Views: 61

Answers (1)

Ken Cheung
Ken Cheung

Reputation: 1808

Despite the performance issue:

SELECT DISTINCT IF(uid1<uid2,uid1,uid2) as uidA, IF(uid1>uid2,uid1,uid2) as uidB 
FROM messages 
WHERE (uid1 = ? OR uid2 = ?)
ORDER BY timestamp DESC LIMIT 10;

Upvotes: 1

Related Questions