Is it possible, to calculate the number of matches in mysql like structure?

Question

Lets assume i have a query like the following

$query_search = "SELECT * FROM `search` WHERE `title` LIKE '%$word%' OR `content` LIKE '%$word%'";

i need to order rows by count of matches?

is it possible in such structure of database, or i need to redesign it?

Thanks much

Answer 1

The syntax may be a bit off but would something like the following work?

ORDER BY 
 COALESCE(LENGTH(`title`)-LENGTH(REPLACE(`title`,'$word','')),0)+
      COALESCE(LENGTH(`content`)-LENGTH(REPLACE(`content`,'$word','')),0) 
DESC

The number of matches in the string can be determined by (LEN(string)-Len(ReplacedString))/Len(Word)

As Len(Word) is a constant and won't affect the relative ordering I have left it out in the above.

Answer 2

   ORDER BY  (CASE WHEN title REGEXP $word then 1 else 0 end +
              CASE WHEN content REGEXP $word then 1 else 0 end) DESC