Question 163262 in PHP Question posted in
Official manual can be found here.

How can I use PHP to create a MySQL WHERE clause with multiple words that match at least one of two fields?

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    private function _build_where_str($query = NULL) {
    $tmp = explode(' ', str_replace(['[', ']'], '', $query));
    $t = [];
    foreach ($tmp AS $v) {
      if (strlen(trim($v)) > 0) {
        $t[] = trim($v);
      }
    }
    $r = '';
    foreach ($t AS $name) {
      $r .= ($r === '' ? '' : ' AND ') . ' (a.`name` LIKE "%' . $name . '%" OR b.`name` LIKE "%' . $name . '%")';
    }
    return $r === '' ? FALSE : '(' . $r . ')';
  }

  2. 0

    You have the key to the answer right there in your description, but you didn’t notice:

    each word needs to match either a.name or b.name

    Rather than trying to list all the possible combinations, just convert that directly into code:

    ( a.name LIKE '%word1%' OR b.name LIKE '%word1%' )
AND
( a.name LIKE '%word2%' OR b.name LIKE '%word2%' )

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search