CODEWITHSUNDEEP
mysqlmysql-workbench
Ruchi yadav
Ruchi yadav

Reputation: 243

Multiple conditions in mySQL

I want to filter the products on the basis of price, brand, and category. But none of them is required. So the user can filter the products on the basis of only price or price and brand. How to write this in mySQL using queries only. I have searched a lot but found the solution with the help of Stored Procedures.

If I write this query select * from product where brandid = 1 AND price = 10 and categoryid = 5. it will fetch 2 products which satisfy the where clause.

But if user doesn't want to filter the product on the basis of brand (lets say), then what will b the query? select * from product where brandid = null AND price = 10 and categoryid = 5... this will not work as I dont want to search products with brandid null. What I want is to remove that particular clause from where condition. So what my expected query is select * from product where price = 10 and categoryid = 5

Upvotes: 0

Views: 76

Answers (1)

Amadan
Amadan

Reputation: 198436

Construct the query incrementally. Here it is in Ruby (since you didn't tag a programming language), but the logic is quite language-independent.

query = "SELECT * FROM products"
filters = []
params = []

if price_min
  filters << "price >= ?"
  params << price_min
end

if price_max
  filters << "price <= ?"
  params << price_max
end

if brand
  filters << "brand = ?"
  params << brand
end

# ...

unless filters.empty?
  query += " WHERE " + filters.join(' AND ')
end

Upvotes: 1

Related Questions