How can I dynamically build mysql where clause based on number of words in an array to have all words match at least 1 of the 2 fields?
I need all the words to be used; meaning that each word needs to match either a.name or b.name.
here is how How I can do it sloppy up to 3 words
$r = '(';
if (count($t) === 2) {
$r .= ' OR (a.`name` LIKE "%' . $t[0] . '%" AND a.`name` LIKE "%' . $t[1] . '%")';
$r .= ' OR (a.`name` LIKE "%' . $t[0] . '%" AND b.`name` LIKE "%' . $t[1] . '%")';
$r .= ' OR (b.`name` LIKE "%' . $t[0] . '%" AND a.`name` LIKE "%' . $t[1] . '%")';
$r .= ' OR (b.`name` LIKE "%' . $t[0] . '%" AND b.`name` LIKE "%' . $t[1] . '%")';
} else if (count($t) === 3) {
$r .= ' OR (a.`name` LIKE "%' . $t[0] . '%" AND a.`name` LIKE "%' . $t[1] . '%" AND a.`name` LIKE "%' . $t[2] . '%")';
$r .= ' OR (a.`name` LIKE "%' . $t[0] . '%" AND b.`name` LIKE "%' . $t[1] . '%" AND a.`name` LIKE "%' . $t[2] . '%")';
$r .= ' OR (b.`name` LIKE "%' . $t[0] . '%" AND a.`name` LIKE "%' . $t[1] . '%" AND a.`name` LIKE "%' . $t[2] . '%")';
$r .= ' OR (b.`name` LIKE "%' . $t[0] . '%" AND b.`name` LIKE "%' . $t[1] . '%" AND a.`name` LIKE "%' . $t[2] . '%")';
$r .= ' OR (a.`name` LIKE "%' . $t[0] . '%" AND a.`name` LIKE "%' . $t[1] . '%" AND b.`name` LIKE "%' . $t[2] . '%")';
$r .= ' OR (a.`name` LIKE "%' . $t[0] . '%" AND b.`name` LIKE "%' . $t[1] . '%" AND b.`name` LIKE "%' . $t[2] . '%")';
$r .= ' OR (b.`name` LIKE "%' . $t[0] . '%" AND a.`name` LIKE "%' . $t[1] . '%" AND b.`name` LIKE "%' . $t[2] . '%")';
$r .= ' OR (b.`name` LIKE "%' . $t[0] . '%" AND b.`name` LIKE "%' . $t[1] . '%" AND b.`name` LIKE "%' . $t[2] . '%")';
}
$r .= ')';
I have not been able to dynamically build this for unknown size of $t
2
Answers
You have the key to the answer right there in your description, but you didn’t notice:
Rather than trying to list all the possible combinations, just convert that directly into code:
That covers all four combinations for two words in two lines rather than four. More importantly, it scales linearly with the number of words: you can just keep adding one line for each word, using a
foreach
loop.