问题描述:

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

网友答案:

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.

网友答案:

You can do this without full text search (where the default values wouldn't work anyway for your query because "i" wouldn't be treated as a word):

order by ((concat_ws(name, type, location) LIKE :search1) +
          (concat_ws(name, type, location) LIKE :search2) +
          (concat_ws(name, type, location) LIKE :search3)
         ) desc

This orders by the most matches to the fewest. Your where clause needs to follow the same format as well, with three different like clauses.

相关阅读:
Top