Mysql keyword relevance sorting method detailed example analysis

Small projects sometimes need to use keywords to search for relevance sorting. If sphinx appears to be a zombie, use order by of mysql to deal with it.
Method 1:
Copy codeThe Code is as follows:
Select * from articles where (title LIKE '% keywords %') or (content LIKE '% helloworld % ') order by (case when title LIKE '% keywords %' THEN 2 ELSE 0 END) + (case when content LIKE '% helloworld %' THEN 1 ELSE 0 END) ASC, dateline DESC

Method 2:
For example, if you search for the keyword "IBM" or "server ",
First, the code for processing the search keyword is as follows:
Copy codeThe Code is as follows:
$ Kw = preg_replace ("/(\ s +) | (+) +/", "", $ kw); // Replace space, line feed, tab, Chinese Space
$ Kw = preg_replace ("/(^ \ s *) | (\ s * $)/", "", $ kw); // remove spaces at the beginning and end
$ Kw = preg_replace ("/(\ s +)/", "", $ kw); // replace multiple spaces with one space
$ Q = explode ('', $ kw); // enumeration keyword

You also need to add a code to remove punctuation marks, but this Code may have problems and I don't know how to solve them.

Then the code for generating the SQL statement
Copy codeThe Code is as follows:
$ F = array ("name", "description"); // The queried field name = product name, description = product description
$ S = array (); // weight, the name field matches points for 4 points, the description field matches product 2 points, and finally sorts by points

Copy codeThe Code is as follows:
// Create a query Condition Statement
For ($ I = 0; $ I <count ($ q); $ I ++ ){
For ($ j = 0; $ j <count ($ f); $ j ++ ){
$ Clause [$ c] = "(". $ f [$ j]. "LIKE '%". $ q [$ I]. "%')";
$ Score [$ c] = "IF (LOCATE ('". $ q [$ I]. ','. $ f [$ j]. "),". $ s [$ j]. ", 0)";
$ C ++;
}
}
$ SQL = "SELECT id, name, description,
(". Implode (" + ", $ score).") AS score
FROM product
WHERE (". implode (" OR ", $ clause ).")
Order by score DESC ";