CODEWITHSUNDEEP
phpmysql
Hafiz Usman aftab
Hafiz Usman aftab

Reputation: 310

How to retrieve a chat msg with sender and receiver username in mysql

chat Table
-------------------------------------------------------
msg_id  msg(description of msg)   sender(id)   receiver(id)
-------------------------------------------------------
1       hello                      2             1
1       how are you                1             2
----------------------------------------------------------

user table
-----------------------
u_id   username
-----------------------
1       usman
2       Ali
---------------------

here you see two tables one is for chat and one table is for user i make a relationship between these two table but i feel difficulty how to extract data from these table.

what i want..

i want to extract each sender msg with its username and each reciver name with its username like facebook chat so i make this query

i put this query in database 

select u.user_name UserName,msg.msg
from user u,messages msg 
where (msg.sender or msg.receiver)=(select u_id from user where u_id= 1 )
    and msg.sender=1
order by msg.msg_id desc

but this query repeat sender message with receive name and receiver msg with sender name

i want to show sender and reciver msg show separably with their name tell me any method how i fix it.

Upvotes: 2

Views: 3884

Answers (2)

Peter Chaula
Peter Chaula

Reputation: 3711

  select users.name, messages.msg ....          Etc from messages inner join users on       msgs.userid = user.id where                             (sender = :sndr and receiver = :rcvr)         or (sender = :rcvr and receiver= :sndr)  order by date desc;        
                take note of the named parameters there.  That would return the conversation between the two users

Upvotes: 0

Marc B
Marc B

Reputation: 360762

What you have would never work. the or comparison will return a boolean true/false value, which you then compare against the value of u_id. And since that u_id query is hardcoded to return only 1, you're effectively doing where true/false = 1.

You need to join the user table twice (once for receiver, once for sender):

SELECT sender.user_name, receiver.user_name, msg.msg
FROM msg
LEFT JOIN user AS sender ON msg.sender_id = sender.u_id
LEFT JOIN user AS receiver ON msg.receiver_id = receiver.u_id
ORDER BY msg.msg_id

Upvotes: 3

Related Questions