3

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?

Community
  • 1
  • 1
Félix Adriyel Gagnon-Grenier
  • 8,422
  • 10
  • 52
  • 65

2 Answers2

6

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 . '%'; }));
dave
  • 62,300
  • 5
  • 72
  • 93
  • 1
    +1 I would just comment on the over complex check. Very nice solution with the lambda function. – VMai Aug 04 '14 at 18:42
0

Explode said string into array:

$array = explode(" ", $string);

After that you can make a string for the query like:

$query = "SELECT * FROM table WHERE column LIKE (";

Then add the variables like so:

$query .= "" . $array[0] . " OR "; //Or there for multiple strings.

Then end the string queue with:

$query .= ")";

Probably not the most effective way but will get the job done. If I need to elaborate more please comment.

Joe Kasavage
  • 503
  • 5
  • 13