Reputation: 311
I currently have a single search field searching against multiple columns using this code:
$searchArray = explode(" ", $searchVal);
$query="SELECT * FROM users WHERE ";
$i=0;
foreach ($searchArray as $word) {
if ($i != 0) $query .= " OR ";
$query .= " MATCH (`first_name`, `last_name`, `email`) AGAINST ('".$word."*' IN BOOLEAN MODE)";
$i++;
}
Lets say I have these two rows in the table:
id | last_name | first_name | email
1 | Smith | John | [email protected]
2 | Smith | Bob | [email protected]
If I type in "John S", only the first result shows which is the desired behavior.
If I type in "John Smith", only the first result shows which is the desired behavior.
If I type "Smith J", both results show even though Bob is not a match.
If I type "Smith John", both results show even though Bob is not a match.
Lastly, if I type "Jo S", no results are returned despite the partial match on "Jo" and "S".
Can anyone help me fix my query to deal with the desired functionality of the order not being important and partial results matching? If it can be sorted by the best matches (i.e. the longest part of the word, starting from the first letter only not a section in the middle, in the highest number of columns), that would be a huge help also.
UPDATE:
Just wanted to post the final code that worked based on the solution. My loop creating multiple match statements was incorrect as was my ft_min_word_len.
My code is now:
$searchArray = explode(" ", $searchVal);
$query="SELECT * FROM users WHERE MATCH (`first_name`, `last_name`, `email`) AGAINST ('";
$i=0;
foreach ($searchArray as $word) {
$query .= "+".$word."* ";
}
$query .= "' IN BOOLEAN MODE)";
Upvotes: 10
Views: 11904
Reputation: 70460
In boolean mode, requiring strings to be present (instead of just scoring higher), is done with +
. prefix matching is done with an ending *
. This seems to be what you want, so search for:
+John* +S*
+John* +Smith*
+Smith* +J*
+Jo* +S*
Note that Full Text indexes cannot help you searching 'anywhere in a word'. so something like *mith*
is bound to fail: they're meant to match from character 1 in an index.
If you also want to order them by match values, and for instance, need John Smith
before Johnny Smithson
, you'd do this:
SELECT * FROM user
WHERE MATCH(..fields..) AGAINST ('match' IN BOOLEAN MODE)
ORDER BY MATCH(..fields..) AGAINST ('match' IN BOOLEAN MODE) DESC;
Which you will see will get you nowhere unless you add all the words >= ft_min_word_len
again separately:
+John* +S* John
+John* +Smith* John Smith
+Smith* +J* Smith
+Jo* +S*
For the last one, both are < the default 4 characters, so we can't add sorting params for that in default mysql, but you could set ft_min_world_len
differently is desired.
Upvotes: 11
Reputation: 12333
IN BOOLEAN MODE
you can use the +
-modifier to force AND
or the -
-modifier to force NOT
. No operator, your case, mean optional.
And you need to check the minimal word length in your mysql configuration to make the FULLTEXT INDEX index words smaller than a certain length.
I had to set
ft_min_word_len = 2
in my.cnf and had to rebuild the index to make this effective. By default it is 3.
To find out your min_word_len check (and upvote) this question
Upvotes: 2
Reputation: 6275
See http://dev.mysql.com/doc/refman/5.5/en//fulltext-boolean.html
You may place a "+", "-", or no operator before a word to make it search for "AND contains this word", "NOT contains this word", and no operator is "OR contains this word"
If I type in "John S", only the first result shows which is the desired behavior.
There's only one John, so this works, S is below the minimum word length and is discarded
If I type in "John Smith", only the first result shows which is the desired behavior.
There's only one John so this works
If I type "Smith J", both results show even though Bob is not a match.
J is below the minimum word length, so its only matching smith which is both rows
If I type "Smith John", both results show even though Bob is not a match.
Since you're in BOOLEAN MODE MySQL interprets this as Smith OR John... Smith matches both.
Lastly, if I type "Jo S", no results are returned despite the partial match on "Jo" and "S".
Jo and S are below the minimum word length - I believe MySQL treats this as searching for nothing
You'll want to add a "+" before your search parameters to turn them into an AND search... +Smith +John
Upvotes: 2