phpmysql

Mysql ORDER BY closest match from partial matches

SELECT * FROM store WHERE 
concat_ws(name, type, location) LIKE :search1 OR :search2 OR :search3

for($n=0; $n<$count; $n++)
   $query->bindValue(':search'.$n, '%'.$search[$n].'%', PDO::PARAM_STR);}
}

I have a search query, i break user's input into array, ex:array('i', 'love', 'apple');

my question is how to ORDER by the closest match?

it search 3 columns, so if user type new york apple, it will return many content with new york from location column, and many content are nothing to do with apple

Solution

  • If you want to do this, you will need Full Text Search(http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html). Which is a bit more complicated than LIKE, but certainly quicker and smarter.