Wildcard Match Against Query causes score to = 0 ( php / sql )

Question

I am using a full text query with MySql version 5.0.45 and I am trying to optimize it for my needs. The score system was working fine, however because I have added a stemmer before the input now, I had to use a wildcard on the search term. The problem is that now a stemmed word will match but return a score of 0. (ie: "restriction" gets stemmed to "restrict" and will still get recognized as a match but with a score of 0)

Here's the query:

$escaped_string = mysql_real_escape_string($string);
$query = "SELECT DISTINCT A1.item_ID, item, 
              4.0 * (match (`item_1`) against ('". $escaped_string."*'))
              + 3.5 * (match (`item_2`) against ('".$escaped_string."*'))
              + 3.0 * (match (`item_3`) against ('".$escaped_string."*')) 
              + 2.5 * (match (`item_4`) against ('".$escaped_string."*'))
              + 1.5 * (match (`item_5`) against ('".$escaped_string."*'))
            as score
          FROM Items A1 LEFT OUTER JOIN Inventory A2 ON A1.item_ID=A2.item_ID
          WHERE MATCH(`item_1`, `item_2`,`item_3`,`item_4`,`item_5`) AGAINST ('".$escaped_string."*' IN BOOLEAN MODE)
          ORDER BY score DESC
          LIMIT 200";

The score calculates perfectly before when it's ('".$escaped_string."')) but not when you add the wildcard *. In both cases the matching works fine, the problem is that the score does not calculate if there is a wildcard.

Any help on this would be appreciated! (I hope I'm doing this mostly right)

Bite the bullet and learn to use [parameters for inquiries](http://stackoverflow.com/questions/1894026/examples-of-parameterized-queries) — xQbert, Apr 15 '12 at 19:20
@EugenRieck I have tried that and in the match against it only works with * and as a suffix. — Corada, Apr 15 '12 at 22:57
@xQbert That is an interesting way of doing things that I have not seen before but I don't see how that would solve this issue. It's not an injection issue that I'm having... — Corada, Apr 15 '12 at 22:58
yes but you are having to escape the strings which might be an issue since I don't know what values are in each escaped_String. — xQbert, Apr 16 '12 at 11:54
You must use BOOLEAN MODE for wildcards . Check the manual entry http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html . Hope it helps. — georgepsarakis, May 13 '12 at 06:27

score 0 · Answer 1 · answered Nov 08 '12 at 20:51

To me it seems like you forgot to add IN BOOLEAN MODE to the score-calculation as search-operators are exclusive to this mode; if I understand http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html correctly.

The following should work in my opinion:

$escaped_string = mysql_real_escape_string($string);
$query = "SELECT DISTINCT A1.item_ID, item, 
          4.0 * (match (`item_1`) against ('". $escaped_string."*' IN BOOLEAN MODE))
          + 3.5 * (match (`item_2`) against ('".$escaped_string."*' IN BOOLEAN MODE))
          + 3.0 * (match (`item_3`) against ('".$escaped_string."*' IN BOOLEAN MODE)) 
          + 2.5 * (match (`item_4`) against ('".$escaped_string."*' IN BOOLEAN MODE))
          + 1.5 * (match (`item_5`) against ('".$escaped_string."*' IN BOOLEAN MODE))
        as score
      FROM Items A1 LEFT OUTER JOIN Inventory A2 ON A1.item_ID=A2.item_ID
      WHERE MATCH(`item_1`, `item_2`,`item_3`,`item_4`,`item_5`) AGAINST ('".$escaped_string."*' IN BOOLEAN MODE)
      ORDER BY score DESC
      LIMIT 200";

Wildcard Match Against Query causes score to = 0 ( php / sql )

1 Answers1