Fulltext Search?

[ukcat98]

Hi everyone. I haven't posted in months, not since the new forum which looks great, btw.

 

I need my search query to return results by a search phrase, not just each individual word in a query. Chapter 6 discusses FULLTEXT searches using WHERE...MATCH but my query returns everything instead of just what I want. For example, searching for Bonn Forceps returns all products by Bonn, and all forceps by every doctor.

 

http://stephensinst.com/products.php

 

Here is my query:

 

$q = "SELECT * FROM products, doctors, types, subtypes WHERE doctors.doc_id = products.doc_id AND types.type_id = products.type_id AND subtypes.sub_id = products.sub_id AND MATCH (description) AGAINST ('%$find%') ORDER BY prod ASC";

 

I added FULLTEXT index to the products, doctors, types, and subtypes tables. Can someone tell me what I'm doing wrong?

 

Thanks!

 

Chris

[Larry]

Thanks for the nice words on the forum (and for posting the pertinent details). I suspect the problem is because you haven't added a FULLTEXT index to the description column. Also, I could totally be wrong about this, but I don't think the % is used in FULLTEXT searches (that's for LIKE).

[Stuart]

I've been working with fulltext searches a lot recently and they can be frustrating at times! If you wanted an exact phrase match for Bonn Forceps then you'd need to run your query in BOOLEAN mode and use literal text matching. Its just like searching in Google and wrapping your search phrase in "".

 

So you query might become something like:

 

$q = "SELECT * FROM products, doctors, types, subtypes WHERE doctors.doc_id = products.doc_id AND types.type_id = products.type_id AND subtypes.sub_id = products.sub_id AND MATCH (description) AGAINST ('\"$find\"' IN BOOLEAN MODE) ORDER BY prod ASC";

 

Searching in boolean mode does have a few drawbacks mainly that they are not automatically ordered by relevance (although there are workarounds for this). Encouraging users to enter the boolean operators is a waste of time (we tried it) so just add them yourself before generating the query.

[ukcat98]

Stuart, thank you!!! That's just what I needed. Can you give any insight into the relevance issue, i.e. matching two search terms without being an "exact" match?

 

Chris

[Stuart]

So the literal match "" returns exact word combinations, a softer approach might be to use the + operator. So by prefixing your search term with a '+' you ensure that the word will be present in any row returned. By prefixing all search terms with a '+' is ensures they are present but not necessarily side by side. To get round the relevance issue you need to do something like:

 

$q = "SELECT *, MATCH(description) AGAINST ('$find') AS relevance FROM products, doctors, types, subtypes WHERE doctors.doc_id = products.doc_id AND types.type_id = products.type_id AND subtypes.sub_id = products.sub_id AND MATCH (description) AGAINST ('$find' IN BOOLEAN MODE) ORDER BY relevance DESC";

 

You might also want to add a HAVING clause to ensure only items with a relevance above a certain threshold (e.g. 50%) are returned. Hope that all makes sense. An easy way to prepend all your search terms with the + might be something like:

 

$safe = mysqli_real_escape_string($dbc, trim($_GET['terms']));
$find = '+' . str_replace(' ', ' +', $safe);

 

Or if you wanted to be precise regarding spacing etc... you could use a REGEX.