Building a query programatically

I have three variables $color, $size and $category. Each oh them can be a specific value or may refer to all entries.I want to fetch all products that are $color = “red”, $size = “Medium” and any category OR any color, any size and $category = “Tshirts”. I can do this using if/elseif or switch and treat all cases but this means 12 cases + the one with any color, any size and any category, so 13 total cases.

Is this even possible in mySql ??

Sample query

SELECT
*
WHERE color = $color AND size = $size AND category = $category

It seems you want to keep you PHP code simple and make the SQL code more complicated, while it should be otherwise.

Example: lets say that
– user A chooses: “color = 'red', size = 'Medium', category = any“
– user B chooses “color = any, size = any, category = 'Tshirts'“

The conditions could be respectively as simple as:

-- userA
WHERE color = $color AND size = $size 
-- or the equivalent (if columns are NOT NULL):  
WHERE color = $color AND size = $size AND category = category

and for

-- userB
WHERE category = $category
-- or the equivalent (if columns are NOT NULL):
WHERE color = color  AND size = size  AND category = $category

I don’t think it’s so difficult to write PHP code that produces those conditions when a user selects some “any”, no matter how that “any” is coded in your PHP/web form.

But if you really want to pass that to MySQL, you can do something like this. Pass the “any” as a fixed value (say 'any') and use:

WHERE (color    = $color    OR $color    = 'any')
  AND (size     = $size     OR $size     = 'any')
  AND (category = $category OR $category = 'any')