how to order by most match

[asmith20002]

Hi,

I have a query like this :

select * from table where option1 like '%word%' or option2 like '%word%' or option1 like '%word2%' or option2 like '%word2' ...

how can I order the result so that it gives me the rows which have both 'word1' and 'word2' first, then go the rows which only matches one of the words.

Maybe I can say something like fulltext order but the query 'LIKE'.

How can I make that ?

Thanks a lot

[chazzy]

you'd need to use full text searching.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

specifically this example:

SELECT MATCH('Content') AGAINST ('keyword1
keyword2') as Relevance FROM table WHERE MATCH
('Content') AGAINST('+keyword1 +keyword2' IN
BOOLEAN MODE) HAVING Relevance > 0.2 ORDER
BY Relevance DESC

[asmith20002]

Thanks for the reply.

I WANT to use full text. But I need to catch 2-letter words too. But my host do not allow me changing that in my mysql configuration.

How can I use fulltext ti have 2-letter words be included too?

[chazzy]

you could program your app differently depending on how the search was performed. if the input's 4 or less don't give them ordering, otherwise do.