Using If in MYSQL query

Question

Hi I am currently coding something which requires an if before the results are retrieved from the database.

The case is that if the interacterID is not NULL then filter by it, see below

SELECT *
FROM status
WHERE type != "3"
AND userID = "'.$userID.'"
AND 
(
    interacterID IN ( #IF interacterID is null I want to skip this AND
        SELECT userID
        FROM userFriends
        WHERE (
            userFriends.userID = "'.$currentUserID.'"
            OR userFriends.interacterID = "'.$currentUserID.'"
        )
        AND status = "1"
    )
)
ORDER BY addedDate DESC
LIMIT 1

Thanks in advance for the help.

Brendan Bullen · Accepted Answer · 2012-10-22 10:47:28Z

2

If I'm reading this right, I'm not sure you need an IF here (which can be done). A simple IS NULL and OR can be used for the two cases:

SELECT *
FROM status
WHERE type != "3"
AND userID = "'.$userID.'"
AND 
(
    interacterID IS NULL OR interacterID IN (
        SELECT userID
        FROM userFriends
        WHERE (
            userFriends.userID = "'.$currentUserID.'"
            OR userFriends.interacterID = "'.$currentUserID.'"
        )
        AND status = "1"
    )
)
ORDER BY addedDate DESC
LIMIT 1

answered Oct 22, 2012 at 10:47

Brendan Bullen

11.8k1 gold badge33 silver badges40 bronze badges

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

1 Comment

André Figueira Over a year ago

Thank you, can't believe missed that, awesome working like I need it now, thanks for the help! When the 5 minutes is up will accept your answer.

SRIRAM · Accepted Answer · 2012-10-22 10:50:49Z

1

SELECT *
FROM status
WHERE type != "3"
AND userID = "'.$userID.'"
AND 
(
interacterID IS NOT NULL  (
    SELECT userID
    FROM userFriends
    WHERE (
        userFriends.userID = "'.$currentUserID.'"
        OR userFriends.interacterID = "'.$currentUserID.'"
    )
    AND status = "1"
)
  )
  ORDER BY addedDate DESC
  LIMIT 1

If interacterID is null then the code will not executed

answered Oct 22, 2012 at 10:50

SRIRAM

1,8982 gold badges17 silver badges18 bronze badges

Collectives™ on Stack Overflow

Using If in MYSQL query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related