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 ??
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
-- 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')