I have a table 'messages' in my table where all the messages is being stored, with all the information necessary.
Here's a quick example with me as ID (23):
------------------------------------------------------------------
| id | to_id | from_id | message | answer | answered | anonymous |
------------------------------------------------------------------
| 1 | 23 | 18 | msg | ans | 1 | 0 |
------------------------------------------------------------------
| 2 | 23 | 2 | msg | NULL | 0 | 0 |
------------------------------------------------------------------
| 3 | 18 | 23 | msg | ans | 1 | 1 |
------------------------------------------------------------------
| 4 | 2 | 23 | msg | NULL | 0 | 1 |
------------------------------------------------------------------
| 5 | 23 | 18 | msg | NULL | 0 | 1 |
------------------------------------------------------------------
first row: I have received a message from userID (18) and have answered.
second row: I have received a message from userID (2) and not answered.
third row: I have sent a message to userID (18) with answer but as anonymous.
forth row: I have sent a message to userID (2) without answer and as anonymous.
fifth row: I have received a message but from an anonymous user.
What I would like to group each user that I have sent or received message from and if a message is unanswered in with that user, give me 0. And for each anonymous, don't group them as users. And show them one by one.
This is my code so far, but where to go next?
SELECT m.fuuid, m.tuuid, m.anonymous, m.answered, u.uuid, u.username
FROM messages m, user u
WHERE
CASE
WHEN m.fuuid = '$uuid' THEN m.tuuid = u.uuid
WHEN m.tuuid = '$uuid' THEN m.fuuid = u.uuid
END
GROUP BY u.username
This won't give me each anonymous received message as single and the satus if the chat has an unaswered message.
From the table above this is what I want to get:
id: 1 & 3 will be[user: 18, username: foo, answered: 1]
id: 2 & 4 will be[user: 2, username: bar, answered: 0]
id: 5 will be[user: 18, username: NULL, anonymous: 1, answered: 0]
if id 5 is answered I won't see it.
Any help is much appreciated