How to sort the results with reference to the keyword

Question

I am developing a classifieds site. For searching product or company I am using wildcard (%) in both in front and back.

$keyword = "%".$user_keyword."%";

And I am using the following mysql query:

$query = "SELECT DISTINCT   login.id_user,  
                login.company,
                login.district,
                FROM login LEFT JOIN products 
                ON login.id_user = products.id_user
                WHERE   (login.district LIKE ? 
                    AND login.place LIKE ?)
                        AND
                    (login.company LIKE ?
                    OR login.summary LIKE ?
                    OR products.description LIKE ?)
                LIMIT ?, ?";    
                ";

This is working. But I want to sort the list with reference to the keyword. For eg: If an user search for "foo" the following result may be get:

fool
kafoo
safoora
foo
foolan

I want to get "foo" as the first item

http://stackoverflow.com/questions/3980638/sort-by-soundex-or-similar-closeness — Mihai, Nov 10 '13 at 13:29

score 1 · Answer 1 · answered Nov 10 '13 at 18:06

Please try following:

$query = "SELECT DISTINCT   login.id_user,  
                login.company,
                login.district,
                FROM login LEFT JOIN products 
                ON login.id_user = products.id_user
                WHERE   (login.district LIKE ? 
                    AND login.place LIKE ?)
                        AND
                    (login.company LIKE ?
                    OR login.summary LIKE ?
                    OR products.description LIKE ?)
                order by if(LOCATE('$user_keyword', login.district)=0, 9999, LOCATE('$user_keyword', login.district))+
                         if(LOCATE('$user_keyword', login.place)=0, 9999, LOCATE('$user_keyword', login.place))+
                         if(LOCATE('$user_keyword', login.company)=0, 9999, LOCATE('$user_keyword', login.company))+
                         if(LOCATE('$user_keyword', login.summary)=0, 9999, LOCATE('$user_keyword', login.summary))+
                         if(LOCATE('$user_keyword', products.description)=0, 9999, LOCATE('$user_keyword', products.description))
                LIMIT ?, ?";    
                ";

score 0 · Answer 2 · answered Nov 10 '13 at 17:18

If you just need exact match to appear as the first one in the result, then you should add something like this to your query:

[...]
ORDER BY
  CASE
    WHEN checked_column = 'keyword' THEN 1  /* exact match */
    ELSE 2  /* non-exact match */
  END,
  checked_column  /* sort non-exact matches */

But if you need a more sophisticated sorting like scoring your results to reflect how similar they are to a given keyword, then the concept you are looking for is called edit distance.

How to sort the results with reference to the keyword

2 Answers2