CODEWITHSUNDEEP
pythonsqlsqlite
EnergyBoy
EnergyBoy

Reputation: 625

How to ignore the condition that user did not pass in SQLITE?

I am doing my crm project with SQLITE+FLASK. And I need a feature is let user to input the condition to filer the result.

I hope that my SQL statement can ignore the WHERE condition if the parameter is space or null.

For example, My input is "NAME", "AGE", "GENDER" so my statement will be

SELECT *
FROM CUSTOMER
WHERE NAME = 'James' AND AGE = '25' AND GENDER = 'M'

But I hope that if user did not enter "NAME" my SQL statement can be something like the code below

SELECT *
FROM CUSTOMER
WHERE AGE = '25' AND GENDER = 'M'

I know maybe I can do this with string concat, but I hope I can do this by SQL statement.

Upvotes: 1

Views: 514

Answers (2)

forpas
forpas

Reputation: 164174

You can do it with the OR operator for each of the columns, by checking also if the parameter value that you pass is NULL or a string with spaces:

SELECT *
FROM CUSTOMER
WHERE (NAME = :name OR TRIM(COALESCE(:name, '')) = '')
  AND (AGE = :age OR TRIM(COALESCE(:age, '')) = '')
  AND (GENDER = :gender OR TRIM(COALESCE(:gender, '')) = '')

Upvotes: 1

Popeye
Popeye

Reputation: 35920

You can use null condition as follows:

SELECT *
FROM CUSTOMER
WHERE (NAME = :name_input or :name_input is null)
  AND (AGE = :age_input or :age_input is null)
  AND (GENDER = :gender_input or :gender_input is null)

Upvotes: 1

Related Questions