PHP/MYSQL - Need a better way to go about this FULLTEXT search query

Question

I am currently busy creating a search function that queries a user input string against certain row in a mysql table.

This is what the code looks like so far:

$space_separated = implode(" ", $keywords_array);

$dataQuery = "SELECT id, `desc`, price1, pack, measure, quantity
              FROM products
              WHERE MATCH (`desc`)
              AGAINST ('".$space_separated."' IN BOOLEAN MODE)";

It is not quite providing the desired functionality. I am aware that in boolean mode, i forfeight the use of scoring. But my problem is that this code does return partial matches on any set word.

An example of this would be if a user was searching for "chocolate" and they searched "olate", no match will be returned. How would I work around this?

@Mark

Were you suggesting this?

$dataQuery = "SELECT id, `desc`, price1, pack, measure, quantity
              FROM products
              WHERE MATCH (`desc`)
              AGAINST ('".$space_separated."' IN BOOLEAN MODE)
              OR MATCH (`desc`)
              AGAINST ('".$space_separated."')";

add ... or match(...) to do a natural language match as well (do the fulltext again, but without 'in boolean mode')? — Marc B
– Marc B, Commented Aug 26, 2011 at 19:55
@Mark Thanks for your input with this, please see the edit in my post. Is that what you were referring to? — Craig van Tonder
– Craig van Tonder, Commented Aug 26, 2011 at 20:19
yeah, pretty much. but as golez points out below, fulltext only matches on full words, unless you add wildcards. — Marc B
– Marc B, Commented Aug 26, 2011 at 20:21
@Mark Is it possible to use boolean functionality while being able to obtain result ranking using natural language search? (it didnt work btw, as you repointed out, GolezTrol has a point) — Craig van Tonder
– Craig van Tonder, Commented Aug 26, 2011 at 20:23

GolezTrol · Accepted Answer · 2011-08-26 19:57:23Z

1

FULLTEXT searched for whole words only. You can specify a wildcard * behind a part of the word, but not before. So choco* will match chocolate', but*olate` will not.

Also, default matching uses or for comparison. If you want to match all keywords, add a + before each keyword.

answered Aug 26, 2011 at 19:57

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Craig van Tonder Over a year ago

Thank you for the reply, I am searching for a work around or a better option with this function though. I did think of the wildcard approach, but came to the same conclusion. Any other ideas?

GolezTrol Over a year ago

You could use separate search utilities, like Sphynx. You could also write some code yourself. If you split a text in separate words, you can create an index for that. Then, you can also split up those words to index only parts of it. That way, you can do an exact match for the words or for parts of a word. It will be some work, though, so you might better try out some third party solutions first.

Craig van Tonder Over a year ago

Thanks for that, it makes sense. I will check out Sphynx. I want to add some kind of synonym function, which sounds sort of the same. I'll work on it :)

Collectives™ on Stack Overflow

PHP/MYSQL - Need a better way to go about this FULLTEXT search query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related