Display user messages

Asked

Viewed 54 times

1

I have two tables, one of utilizadores where I keep the usernameand the id, and I have another chat where I keep the fields sender and the reciever, which are the user id of the user who received and sent the message. Now, how can I display that the user logged in with the id=6 have messages exchanged with id=11 and the id=17.

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

This way I receive all 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=6i receive 10 messages that this user exchanged with the user id=11 and a message he exchanged with the user id=17

php sql

  • Give an example so we can help.

2 answers

2


You can use INNER JOIN to make this consultation.

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)

  • thanks for the reply, that was it. In this consultation, how can I apply the following: INNER JOIN photos AS p ON (user.photo_p_id=p.id ) I mean, I have a table photos with the countryside id and in the table of user I save the id corresponding to the table photos. I wanted to show the photo of the user, but I have to photo_p_id on the table utilizador match table photo id photos

0

When user 6 exchanges messages, he may be in the position of sender or receiver, then we need to make a consultation that contemplates the two situations: 

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

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

  • 1

    Pedro, when posting code other than in a line, use markdown or simply give control+k with the selected code.

  • 1

    thanks for the tip (Y)

Browser other questions tagged php sql

You are not signed in. Login or sign up in order to post.