PHP MYSQL - Distinct multiple columns as one

Question

I've read a lot of questions and answers related to this topic, but I can't find anything that's working for me, so I could really use some help.

I am trying to make a simple private messaging system for my website. I have a table like this:

private messages: id, to_id, from_id, message, time_sent

I am now trying to show a list on my page with the different users a member interacted with, something like Facebook messages so every name should appear only once in the list.

This is what I have now which shows me every message that I as a user ($my_id) participate in:

SELECT * FROM private_messages WHERE (from_id = '$Sid' AND to_id = '$my_id') OR (from_id = '$my_id' AND to_id = '$Sid')  ORDER BY time_sent ASC

This query shows a distincted list, but only of the people that I received a message from:

SELECT DISTINCT(from_id) FROM private_messages WHERE to_id='$my_id' ORDER BY time_sent DESC

How should I rewrite this so it gives me a distinct list of every user that I interacted with (like a list of facebook messages)? I think I need to distinct on two columns, from_id and to_id, but how should I do it?

I know the questions sounds rather vague, but I really can't explain it any better. Thanks in advance!

Giorgos Betsos · Accepted Answer · 2016-04-04 19:27:40Z

3

Try this:

SELECT DISTINCT IF(from_id = '$my_id', to_id, from_id) AS other_id
FROM private_messages 
WHERE '$my_id' IN (to_id, from_id) 
ORDER BY time_sent DESC

edited Apr 4, 2016 at 19:27

answered Apr 4, 2016 at 19:20

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Tom Kuschel Over a year ago

Isn't there missing a closing bracket? : SELECT DISTINCT(IF(from_id = '$my_id', to_id, from_id) AS other_id) FROM....

Giorgos Betsos Over a year ago

@TomK. Thanks for the remark. This was a typo. DISTINCT does not need any parentheses as it is not a function.

Tom Kuschel Over a year ago

you're right, just saw that there was something missing ... you're welcome.

Senne Vandenputte Over a year ago

Thank you, this seems to give me two rows, which is what I need! Now I am trying to echo the rows based on the value of from_id and to_id. So after fetching the array, I do like this: if($row['from_id'] == $my_id) { echo xxxxx } elseif($row['to_id'] == $my_id) { echo xxxxxx }, but it's not working, it doesn't show any rows..

Senne Vandenputte Over a year ago

I tried that at first, but I was mistakenly using mysqli_fetch_array instead of mysqli_fetch_assoc. It works now, thanks for the help, you saved my day!

|

Collectives™ on Stack Overflow

PHP MYSQL - Distinct multiple columns as one

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related