Mysql query and conditional where clause

Question

Using PHP and MySQL, I'm trying to get all the private chats involving the actual user. For that I have a table called "rooms" (id, id_initiate_user, id_target_user) and another one called "users" (id_pseudo, avatar). If the actual user is the initiating user of a chat room, I want to get the avatar and pseudo from the target user and if the actual user is the target user of the that room, I want to get the avatar and pseudo from the initiating user (whatever the case, I want the other member's avatar and pseudo).

I guess I need a condition in my query then but I really don't know how to manage it (googled all day long).

Here is my query (the logic is there but the syntax is obviously wrong:

$user_id=$_SESSION['user_id'];

$res = @mysql_query("
SELECT rooms.id, rooms.id_initiate_user, rooms.id_target_user, rooms.pv, users.id, users.pseudo, users.avatar 
FROM rooms, users 
WHERE 
(
    (rooms.id_initiate_user='$user_id' OR rooms.id_target_user='$user_id') 
    AND  
    (
        IF (rooms.id_initiate_user='$user_id')
        THEN rooms.id_target_user=users.id
        ELSE rooms.id_initiate_user=users.id;
        END IF;
    )
) ORDER BY rooms.id ASC");

I read plenty of questions around here regarding the "conditional where" but none of it solved my case. Thank you for your help.

Arion · Accepted Answer · 2012-04-06 13:40:01Z

3

Can't you do something like this:

WHERE 
(
    (
        rooms.id_initiate_user='$user_id'
        AND rooms.id_target_user=users.id
    )
    OR
    (
        rooms.id_target_user='$user_id'
        AND rooms.id_initiate_user=users.id
    )
)

answered Apr 6, 2012 at 13:40

Arion

31.3k10 gold badges74 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Baylock Over a year ago

Exactly. I'm on this query since yesterday and as soon as I posted my question here, I managed to find the same query as yours which seems to work perfectly. It makes a lot of sense but from a beginner's perspective it's not necessarily that obvious. Thank you Arion. PS: How can I vote for you to thank you?

Arion Over a year ago

Good that you found it yourself. We all have been beginners. Glad to help. Maybe you can accept the answer then?

Collectives™ on Stack Overflow

Mysql query and conditional where clause

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related