MySQL keyword correlation ranking method detailed example analysis _mysql

Small projects sometimes need to use keyword search relevance sort, with Sphinx appear overkill, with the order of MySQL by against.
Method One:

Copy Code code as follows:

SELECT * from articles where (title-like '%keywords% ') or (content like '%helloworld% ') Order by ("case" title like '% keywords% ' THEN 2 else 0 end] + (case while content like '%helloworld% ' THEN 1 ELSE 0) ASC, Dateline DESC

Method Two:
For example, if the search keyword "IBM", "Server",
First, for the Search keyword processing, the code is as follows:

Copy Code code as follows:

$kw = Preg_replace ("/(\s+) | ( +) +/"," ", $kw);//instead of spaces, newline, tab, Chinese spaces
$kw = Preg_replace ("/(^\s*) | ( \s*$)/"," ", $kw);//Remove the trailing spaces
$kw = Preg_replace ("/(\s+)/", "", $kw);//replace multiple spaces with one space
$q = Explode (", $kw);//Enumeration keywords

There is also a need to add a code to remove the punctuation, but this code will have problems, do not know how to solve.

Then the code that generates the SQL statement

Copy Code code as follows:

$f = Array ("name", "description"); field of query Name= product name, description= product Description
$s = array (4,2); Weights, Name field matching points 4 points, description field matching product 2 points, and finally sorted by integral

Copy Code code as follows:

Create a query conditional 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 ";