I'm trying to create a search function.
If the search input field is "foo bar", I split it into two keywords then do this query:
SELECT p.* FROM p_extra_fields as x INNER JOIN products as p ON x.product = p.id
WHERE x.type = "1"
AND
(
(x.key = "model" AND x.value LIKE "%foo%")
OR (x.key = "model" AND x.value LIKE "%bar%")
OR (x.key = "color" AND x.value LIKE "%foo%")
OR (x.key = "color" AND x.value LIKE "%bar%")
OR (x.key = "make" AND x.value LIKE "%foo%")
OR (x.key = "make" AND x.value LIKE "%bar%")
)
GROUP BY x.product LIMIT 0, 50
The number of keywords may be higher so I might need more "likes". Also the number of "key" can increase :)
Is there any way I could simplify this query? Can I do something like LIKE("%foo%", "%bar%")
?