MySql Query value can be null

Question

I have this query

SELECT * 
  FROM `Products` 
 WHERE brand REGEXP '$brandFilter' 
   AND categories REGEXP '$categoryFilter' 
   AND color REGEXP '$colorFilter' 
   AND price BETWEEN '$priceMin' AND '$priceMax' 
   AND size REGEXP '$sizeFilter'

I work with PHP and every value obtained can be empty It is possible to check in SQL if the value is empty? for example: if '$brandFilter' is not null

SELECT * 
  FROM Products
 WHERE brand REGEXP '$brandFilter'

It is possible to do this in code but it is very long because it is possible to get 3 values null

thanks for your help!

See about sql injection and the importance of prepared and bound queries. — Strawberry, Nov 22 '20 at 10:23

score 0 · Accepted Answer · answered Nov 22 '20 at 09:22

From SQL perspective, you need OR conditions:

SELECT * 
FROM Products 
WHERE (brand      REGEXP :brandFilter    OR :brandFilter    IS NULL)
  AND (categories REGEXP :categoryFilter OR :categoryFilter IS NULL)
  AND (color      REGEXP :colorFilter    OR :colorFilter    IS NULL)
  ...

You might want to dynamically build the where clause in your application, which would probably make for more efficient SQL code.

Note that this uses bind parameters rather that concatenating variables in the query string. Recommended reading: How can I prevent SQL injection in PHP?.

MySql Query value can be null

1 Answers1