Mysql : sql select query if condition parameter is null then ignore the parameter

Question

I have an Mysql query

SELECT *
FROM EMPLOYEE
WHERE DEPARTMENT = ?
    AND DESIGNATION = ?
    AND DISTRICT = ?
    AND CIRCLE = ?

And quite possible any 1 or 2 or 3 of the parameter (?) can be empty or null.

so what should I do so that the empty parameters are totally Ignore in the where clause and only search for the non-empty parameter in the table.

How can I achieve this

Please help.. The query must be compatible mysql. Thanks

GolezTrol · Accepted Answer · 2017-10-25 20:44:31Z

4

You could add the input parameters twice and make the query like this:

SELECT *
FROM EMPLOYEE
WHERE (DEPARTMENT = ? OR ? IS NULL)
    AND (DESIGNATION = ? OR ? IS NULL)
    AND (DISTRICT = ? OR ? IS NULL)
    AND (CIRCLE = ? OR ? IS NULL)

So on the first and second '?' you bind the same value.

Alternatively (and arguably better), you can build the SQL dynamically, and leave out certain parts of the where clause if it doesn't apply. You can concat the strings to get the basic SQL and still use bind parameters.

answered Oct 25, 2017 at 20:44

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

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

Collectives™ on Stack Overflow

Mysql : sql select query if condition parameter is null then ignore the parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related