CODEWITHSUNDEEP
phpmysqlsqlchat
Beginner
Beginner

Reputation: 23

how to show only users who have previously chat with [PHP]

I have table user

**| username | password |**
     username    1234
     username1   1234
     username2   1234

and message

**| from_user_name | to_user_name | message |**
     username        username1      Hi      
     username        username2      Hi
     username1       username      reply

this is my SQL code

$query = "SELECT * FROM " .table_name. " WHERE to_user_name = '".$_SESSION['username']."' OR from_user_name = '".$_SESSION['username']."' GROUP BY from_user_name,to_user_name";

I login with (username) if there is a reply from (username1) it will show

username 1
username 1
username 2

I need to show like

username 1
username 2

Can you please advise me, Thank you all

Upvotes: 1

Views: 240

Answers (1)

Akina
Akina

Reputation: 42728

Use:

SELECT DISTINCT CASE WHEN to_user_name = $_SESSION['username']
                     THEN from_user_name
                     ELSE to_user_name
                     END AS responder
FROM message
WHERE $_SESSION['username'] IN (from_user_name, to_user_name);

or

SELECT to_user_name AS responder
FROM message
WHERE from_user_name = $_SESSION['username']
UNION 
SELECT from_user_name 
FROM message
WHERE to_user_name = $_SESSION['username']

Upvotes: 1

Related Questions