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