match any or all parts of an array to mysql

I have an exploded search string, and I need to see if any or all parts of it match an entry in a mysql DB table.

If user searches for something like "pour house greensboro", then "pour house" would match an entry in the table. But the search could also be "$1 beer pour house greensboro, or anything else like that, and in any order, so I need to put each part of the search string together and check to see if it matches what's in the DB

so, if the parts of the exploded string are just numbers, then I need to check it like this:
0 + 1 + 2 + 3 + 4
0 + 1 + 2 + 3
0 + 1 + 2
0 + 1
0
1 + 2 + 3 + 4
1 + 2 + 3
1 + 2
1
2 + 3 + 4
2 + 3
2
3 + 4
3
4

there could be more or less than 5 parts of the string

is there any way to do this easily?

2012-04-04 21:20
by Justin White

The solution for such a problem is mysql %LIKE% query, which you can use for matching strings/substrings to database intries - NoName 2012-04-04 21:26

If it should only match a suffix, search for WHERE myColumn LIKE '%lastWord', using only the last of your words. In your example any tuple that matches %4 also matches 3 + 4 2 + 3 + 4 and so on, so just search for %4 - Basti 2012-04-04 21:29

LIKE %string% kinda works, but not completely. it will only compare each part of the string individually. I need to actually put the parts of the string back together in all the various combinations, then run the SQ - Justin White 2012-04-04 22:29

I figured out how to do this, didn't think to update this until now...

(for $i=0; $i < count($search_string); $i++){
    $output = '';
    (for $a=$i; $a < count($search_string); $a++){
        $output = $output." ".$search_string[$a];
    }
}

2012-04-29 19:55
by Justin White

+1 for letting us know - Mathias Lykkegaard Lorenzen 2013-03-19 15:14

While %LIKE% will work, it's going to be quite slow. You probably want to use MySQL's fulltext search features instead. Just Google for some tutorials :)

2012-04-04 21:28
by Daan

well you can always put the exploded string inside an array and do a query search in the database for each array value. i.e select * from table where data = array[$x];

2012-04-04 21:25
by magicianiam

Use '%LIKE%':

SELECT *
  FROM MyTable
 WHERE MyField LIKE '%SearchKeyword1%'
    OR MyField LIKE '%SearchKeyword2%'
    OR MyField LIKE '%SearchKeyword3%'
...

2012-04-04 21:27
by mellamokb