CODEWITHSUNDEEP
phpmysql
Guy in the chair
Guy in the chair

Reputation: 1065

Full Text searching functionality not working as expected

I've used full text search functionality, however it's not working up to the expectation. I've following code in search.php file:

$kws = $_POST['kw'];
$kws = mysql_real_escape_string($kws); 
$query = "SELECT * FROM products WHERE MATCH (product_name,brand,short_desc)
          AGAINST ('*".$kws."*' IN BOOLEAN MODE) limit 14" ;
$res_old = mysql_query($query);

'kw' is something what I type in search box. Now for an example, if I search for 'Dove Intense', it places Dove Antihairfall on top because that's on top in database. I understand I'm searching the full text functionality over two separate columns i.e. brand & product_name, this situation can occur. However is there anyway I can have it the other way round so that it actually ranks the search higher if it matches against both the columns. Basically what user types in, I need that thing ranks higher in search result. Anyone can give some idea how to achieve that?

Upvotes: 1

Views: 63

Answers (1)

laurent
laurent

Reputation: 418

You (or others) may still be interested in the answer:

From Mysql documentation for match against using boolean mode : "They do not automatically sort rows in order of decreasing relevance. You can see this from the preceding query result:..." CheckThis

You should use boolean operations to achieve what you expect, so a search for 'Dove Intense' will return rows that contain at least one of the two words (ie. rows with Dove only + rows with Intense only + rows with both dove, intense (in any order) ), simply because having no operation between the two words indicates an OR operation !

This may not be the result you expect, since you may be interested to make an "and" operation between the two words, but what about the order?

If you don't mind the order (ie. any row containing both words will be included in the results ex. 'Intense whatever whatever dove ...') this means that you should match against:

'+Dove +Intense' in this search, rows containing only one of the two worlds will not be included in result.

if you are trying to implement a strict search ie. only rows containing this phrase "Dove Intense" you should match against '"Dove Intense"'

Now about ranking:

If you want to obtain all results having at least "Dove" but rank rows higher if they also contain "Intense", then you should match against '+Dove Intense'

hope this was useful for what you are trying to implement.

Upvotes: 1

Related Questions