Geek asks and answers

The wildcard against the query evaluates to = 0 (php / sql)

I am using a full text query with MySql version 5.0.45 and I am trying to optimize it for my needs. The point system worked fine, however, since I added the stem before entering now, I had to use a wildcard in the search expression. The problem is that now the word with the runoff will match, but it will return the score 0. (i.e., "Constraint" gets "constraint" and will still be recognized as a match, but with the score 0)

Here's the request:

$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 is perfectly calculated before it is ('".$escaped_string."')), but not when adding a wildcard *. In both cases, the match works fine, the problem is that the score is not calculated if there is a wildcard.

! ( , )

+3
1

, IN BOOLEAN MODE , ; http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html.

, :

$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";
0

More articles:


All Articles