php - How to make a multiple column mysql fulltext search where partial words are matched -

i have single search field searching against multiple columns using code:

$searcharray = explode(" ", $searchval); $query="select * users "; $i=0; foreach ($searcharray $word) {     if ($i != 0) $query .= " or ";     $query .= " match (`first_name`, `last_name`, `email`) against ('".$word."*'  in boolean mode)";     $i++; } 

lets have these 2 rows in table:

id | last_name | first_name | email 1  | smith     | john       | john_smith@js.com 2  | smith     | bob        | bob_smith@js.com 

if type in "john s", first result shows desired behavior.

if type in "john smith", first result shows desired behavior.

if type "smith j", both results show though bob not match.

if type "smith john", both results show though bob not match.

lastly, if type "jo s", no results returned despite partial match on "jo" , "s".

can me fix query deal desired functionality of order not being important , partial results matching? if can sorted best matches (i.e. longest part of word, starting first letter not section in middle, in highest number of columns), huge also.

update:

just wanted post final code worked based on solution. loop creating multiple match statements incorrect ft_min_word_len.

my code now:

$searcharray = explode(" ", $searchval); $query="select * users  match (`first_name`, `last_name`, `email`) against ('"; $i=0; foreach ($searcharray $word) {     $query .= "+".$word."* "; } $query .= "' in boolean mode)"; 

in boolean mode, requiring strings present (instead of scoring higher), done +. prefix matching done ending *. seems want, search for:

+john* +s* +john* +smith* +smith* +j* +jo* +s* 

note full text indexes cannot searching 'anywhere in word'. *mith* bound fail: they're meant match character 1 in index.

if want order them match values, , instance, need john smith before johnny smithson, you'd this:

 select * user   match(..fields..) against ('match' in boolean mode)  order match(..fields..) against ('match' in boolean mode) desc; 

which see unless add words >= ft_min_word_len again separately:

+john* +s* john +john* +smith* john smith +smith* +j* smith +jo* +s* 

for last one, both < default 4 characters, can't add sorting params in default mysql, set ft_min_world_len differently desired.