Igor Igor - 1 year ago 60
MySQL Question

Value check directly in the query using MySQL

I've the following table:


id | user1 | user2 | status
1 | Jorge | Alisson | 3
2 | Lucas | Jorge | 3

And to do a
, I do (the variable
refers to the user logged in at the time):

SELECT user1, user2, status
FROM friends
WHERE user1 = '$userLogged' OR user2 = '$userLogged'

So, in PHP:

foreach($query as $q) {
if($q["user1"] == $userLogged) {
$userFriend = $q["user2"];
} else {
$userFriend = $q["user1"];

Suppose the
is Jorge. I want to get the friend (
), that is, which is not
(Alisson and Lucas, in case). Currently I do this check in the manner shown above, with PHP. How can I do this directly in the query, thus saving this check in PHP? For ex.:
SELECT user1 AND user2 <> '$userLogged' AS userFriend

Answer Source
CASE WHEN user1 = '$userLogged' THEN user2
ELSE user1 END AS userFriend
FROM friends
WHERE user1 = '$userLogged' OR user2 = '$userLogged'