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

Answer 1

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.