CODEWITHSUNDEEP
mysqlsql
ReNiSh AR
ReNiSh AR

Reputation: 2852

WHERE condition within IF statement in SQL

I need to get result from an SQL which consists of where condition. But certain where conditions are executed if it satisfies some criteria.

I tried:

$query  = "SELECT m.id, m.message_type, m.message_url, m.user_id, c.type
            FROM gps_conversation_chat m left join gps_conversation c on m.conversation_id=c.id
            WHERE  m.conversation_id = $conv_id 

            IF c.type=1 THEN
                AND m.from_message_in != 2 AND m.to_message_in != 2
            END IF;

            ORDER BY  m.id DESC LIMIT $start, $limit ";

The above returns error, i need the IF condition to be processed, that is:

if c.type=1 then execute AND m.from_message_in != 2 AND m.to_message_in != 2 with the WHERE condition.

How to do that ?

Upvotes: 0

Views: 58

Answers (1)

Barranka
Barranka

Reputation: 21047

You can't use if like that. But you can define the conditions like this:

SELECT m.id, m.message_type, m.message_url, m.user_id, c.type
FROM gps_conversation_chat m left join gps_conversation c on m.conversation_id=c.id
WHERE m.conversation_id = $conv_id 
  AND (
         (c.type = 1 AND m.from_message_in != 2 AND m.to_message_in != 2)
         OR c.type != 1
      )
ORDER BY m.id DESC 
LIMIT $start, $limit;

Notice that by nesting the conditions you can get the results you want.

One more thing: Your query (the way it is written) can be vulnerable to SQL Injection Attacks. Please take a look here for a good explanation on the topic, and the way to prevent it.

Upvotes: 4

Related Questions