CODEWITHSUNDEEP
mysqlmatchsql-order-by
South Coast Web
South Coast Web

Reputation: 440

Sorting by relevance with MySQL match against multiple words in 2 tables

I have a search bar on a website which I was doing a wildcard search against a word across a 2 tables and 4 columns. I want to do a multiple word search so I have swapped the coding over to a match..against statement.

The issue I have is ordering the relevance. At the moment if I search for 3 words I would like it to display the products with those 3 words in first but it seems to display the 3rd word in priority over the 2nd then the 3rd. Not sure if this is due to such a wide search (column-wise) or whether I need to associate the relevance per column.

I have added boolean mode and changed Spaces in the search string with the greater than sign to try and add priority..

I am using prepared statement with PDO for maximum security..(i Hope)

My current code..

$search = filter_var($_GET['s'], FILTER_SANITIZE_STRING);
$searchfor = str_replace(' ',' >',$searchfor);
$itemResults = $conn->prepare("SELECT a.*
                            FROM item_info_short a 
                            LEFT JOIN item_information b 
                            ON a.id = b.id  
                            WHERE MATCH(a.title,a.manufacturer,a.item_code,a.colour,b.shortdescription) 
                            AGAINST(:searchfor IN BOOLEAN MODE)     
                            LIMIT $offset,$itemsperpage");

    $itemResults->bindValue(':searchfor',$searchfor,PDO::PARAM_STR);
    $itemResults->execute();

Upvotes: 2

Views: 2274

Answers (1)

GolezTrol
GolezTrol

Reputation: 116110

Normally you would always need ORDER BY to ensure a certain ordering, but MySQL adds some magic to the natural language matching, that causes rows to always be ordered by relevance. Unfortunately for you, this doesn't apply to boolean searches.

http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html says:

Boolean full-text searches have these characteristics:

  • They do not automatically sort rows in order of decreasing relevance. You can see this from the preceding query result: The row with the highest relevance is the one that contains “MySQL” twice, but it is listed last, not first.

So instead of using a boolean search, you could do a natural language search, or use that other search to order the results by:

SELECT a.*
FROM item_info_short a 
LEFT JOIN item_information b ON a.id = b.id  
WHERE 
  MATCH(a.title,a.manufacturer,a.item_code,a.colour,b.shortdescription) 
    AGAINST(:searchfor IN BOOLEAN MODE)     
ORDER BY 
  MATCH(a.title,a.manufacturer,a.item_code,a.colour,b.shortdescription) 
    AGAINST(:searchfor IN NATURAL LANGUAGE MODE) DESC    
LIMIT $offset,$itemsperpage");

Or, possibly better/faster:

SELECT a.*
FROM item_info_short a 
LEFT JOIN item_information b ON a.id = b.id  
WHERE 
  MATCH(a.title,a.manufacturer,a.item_code,a.colour,b.shortdescription) 
    AGAINST(:searchfor IN NATURAL LANGUAGE MODE) DESC    
LIMIT $offset,$itemsperpage");

Upvotes: 1

Related Questions