Table:
+----+----------+-----------+---------+
| id | topic_id | from_user | to_user |
+----+----------+-----------+---------+
| 6 | 5 | 4 | 5 |
| 2 | 6 | 5 | 2 |
| 3 | 5 | 2 | 5 |
| 4 | 4 | 5 | 4 |
| 5 | 4 | 5 | 4 |
| 7 | 6 | 5 | 2 |
| 8 | 5 | 2 | 5 |
| 9 | 5 | 4 | 5 |
| 10 | 0 | 2 | 5 |
| 11 | 6 | 5 | 2 |
| 12 | 3 | 5 | 2 |
| 13 | 0 | 5 | 2 |
+----+----------+-----------+---------+
This is messages table ( something like private messages), from_user and to_user is self descriptive, topic_id is not important for this purpose
Now, I need to select list of messages which I will display in inbox of a user who is logged in currently. I will refer to this user with session variable $this_user = $_SESSION['id']
I have this query:
SELECT *
FROM messages
WHERE from_user = '$this_user' OR
to_user = '$this_user'
but this will take repeating messages like 4 - 5, 5 - 4, 5 - 4,
I tried with DISTINCT but isn't going to work either
Any help appriciated
Try this one,
select least(from_user, to_user) as x, greatest(from_user, to_user) as y
from tableName
WHERE from_user = '$this_user' OR to_user = '$this_user'
group by x, y
Not sure how you used DISTINCT (show the query), but alternatively this should work too:
SELECT * FROM messages
WHERE from_user = '$this_user' OR to_user = '$this_user'
GROUP BY `id`
Use UNION ALL
SELECT 'From You' type, * FROM messages m WHERE from_user = '$this_user'
UNION ALL
SELECT 'To You', * FROM messages m WHERE to_user = '$this_user'
Surely you'd need to make use of grouping on the topic_id column? Using a query like this:
SELECT * FROM messages
WHERE from_user = '$this_user' OR to_user = '$this_user'
GROUP BY `topic_id`
Gives me the results I believe you're looking for. See this SQLFiddle for an example.