create LIKE friendly string from space separated sentence

Question

I want to allow my users to search values in the database using partial words in any order, so that this searched string :

nan mu

would return any string where both nan and mu are contained, in any order. with only two bits, it is rather easy with array_filter() and the solution in this answer to create the strings

%nan%mu%
%mu%nan%

so that mysql will search for those. Now I have a problem when there are more then two bits, such as nan mu te. the purpose is to obtain those strings:

$string1 = %nan%mu%te%
$string2 = %nan%te%mu%
$string3 = %mu%nan%te%
$string4 = %mu%te%nan%
$string5 = %te%nan%mu%
$string6 = %te%mu%nan%

and make 6 mysql LIKE condition WHERE Field LIKE $string1 AND Field LIKE $string2 ... so that any possibility of those three bits in any order will be contained in the result

for those about to complain about sql injection, thanks, but no thanks, I use PDO and parameterized queries.

my actual try with string nan mu te:

class EstDefini
 {private $STR;
  function __construct($vSTR)
   {$this->STR = $vSTR;}
  function Verifier($vSTR)
    {return !($vSTR == $this->STR);}}

$vString = 'nan mu te';

$aBits = explode(' ',$vString);
$vNb = count($aBits);

for ($i=0;$i<$vNb;$i++)
 {$vAppend = implode('%',array_filter($aBits,array(new EstDefini($aBits[$i]),'Verifier')));
  $aLiterals[$i] = '%' . $aBits[$i] . '%' . $vAppend;}

returns only 3 strings, because my initial count is only three (there are three bits from my exploded string): (this is var_dump($aLiterals))

array(3) {
  [0]=>
  string(10) "%nan%mu%te"
  [1]=>
  string(10) "%mu%nan%te"
  [2]=>
  string(10) "%te%nan%mu"
}

my initial tought is to modify the count:

$vNb = $vNb * max($vNb-1,1);

and try and shuffle the other bits to obtain the different LIKE friendly strings but then since my initial $aBits array is still of size 3, this class creation call new EstDefini($aBits[$i]) will try and access undefined bits.

How to create LIKE friendly strings from space separated words with the purpose of finding all those words in any order with MySQL?

Answer 1

I think the approach is a little wrong - why do you need to check if it's field LIKE '%a%b%' OR field LIKE '%b%a%' when you could instead just check if it's field LIKE '%a%' AND field LIKE '%b%' and cover both.

$words = explode(' ', $string);
$params = array_fill(0, count($words), '(?)');
$search = implode(' AND field LIKE ', $params);
$db->prepare("SELECT * FROM table WHERE field LIKE $search");

$query->execute(array_map($words, function($word) { return '%' . $word . '%'; }));