Display user messages

1

I have two tables, one of utilizadores where I save username and id , and I have another chat where I save the sender and reciever fields, which are the id's from whom you have received and sent a message. Now, how can I show that the user logged in with id=6 has messages exchanged with id=11 and id=17 .

The user can have 10 messages exchanged with user id=11 and a message only exchanged with id=17 . I just want two lines to appear saying that id=6 has messages exchanged, in this case with two users.

In this way I get all the messages that the user logged in with id=6 has with other users

$sql ="SELECT * FROM chat WHERE reciever = $id
UNION
SELECT * FROM chat WHERE sender = $id";

If the user is id=6 I get 10 messages that this user exchanged with the user id=11 and a message that he exchanged with the user id=17

    
asked by anonymous 22.01.2016 / 13:33

2 answers

2

You can use INNER JOIN to make this query.

SELECT
    chat.id,
    IF(chat.sender = 6, utilizadores_reciever.username,  utilizadores_sender.username) as username
FROM
    chat
    INNER JOIN utilizadores as utilizadores_sender ON (chat.sender = utilizadores_sender.id)
    INNER JOIN utilizadores as utilizadores_reciever ON (chat.reciever = utilizadores_reciever.id)
WHERE
    chat.sender = 6 OR chat.reciever = 6
GROUP BY 
    IF(chat.sender = 6, utilizadores_reciever.id,  utilizadores_sender.id)
    
22.01.2016 / 13:51
0

When user 6 exchanges messages, he may be in the position of sender or receiver , so we need to make a query that covers both situations:

SELECT sender FROM chat WHERE receiver = 6
UNION
SELECT receiver FROM chat WHERE sender = 6

When user 6 is sender , the other user will be receiver and vice versa. The result of the above query will show all users who have exchanged messages with User 6.

    
22.01.2016 / 13:40