WHERE condition within IF statement in SQL

Question

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 ?

Barranka · Accepted Answer · 2014-09-19 04:57:44Z

4

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.

edited Sep 19, 2014 at 4:57

answered Sep 19, 2014 at 4:39

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

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

2 Comments

Suganthan Madhavan Pillai Over a year ago

One doubt about SQL Injection Attacks is, he used, $conv_id kinda of variable directly right?

Barranka Over a year ago

@Suganthan any variable used directly can be a point of access for a SQL injection attack... use prepared statements

Collectives™ on Stack Overflow

WHERE condition within IF statement in SQL

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