Prashant Valanda Prashant Valanda - 5 months ago 9x
SQL Question

Select data from table

I have table name chat as below

enter image description here

I am trying with below query I can get data

SELECT chat_detail_id,from_user_id,to_user_id,time FROM chat as c WHERE c.to_user_id='3' OR c.from_user_id='3'

But If I fetch data with user id = 3 (c.to_user_id='3' OR c.from_user_id='3') I want a one column name with other_id have value of from_user_id OR to_user_id other than fetch user id in my case is 3

Output as below highlighted in red this s other_id row:

enter image description here

Any help would be great appreciated


There can be two approaches to solve this issue.

You can solve it using PHP or by using SQL.

If you chose to use PHP,

You do the same query and then make an if statement after your fetch.

if($fetched['to_user_id'] == '3')
    $other_id = $fetched['from_user_id'];
    $other_id = $fetched['to_user_id'];

The other option is to do it with your SQL query.

SELECT chat_detail_id,from_user_id,to_user_id,CASE c.to_user_id WHEN '3' THEN (c.from_user_id) ELSE (c.to_user_id) END AS other_id FROM chat as c WHERE (c.to_user_id='3' OR c.from_user_id='3')

EDIT: Fixed, works now.