通过计算通过PDO / SQL相关的单词对搜索结果进行排名

Question

I would like to create a search engine that displays results by ranking preset. The rating consists of two parts 1. The amount of post views (represented by POP [INT] in "post" table) and the amount of the words in the text itself (represented by TXT [STRING] in "post" table).

That is if there is a three-Results

//$search = 'apple macbook';

1. 3 hits (POP) and 3 keywords related (2 MACBOOK, 1 APPLE found in TXT) = 6 - will be presented first
2. 2 hits (POP) and 3 keywords related (1 MACBOOK, 1 APPLE found in TXT) = 5 - will be presented Second
3. 2 hits (POP) and 1 keyword related (1 APPLE found in TXT) = 3 - will be presented Third

My code:

$search = isset($_GET['s']) ? htmlspecialchars($_GET['s']) : ''; 
$search = strtoupper($search);
$search = strip_tags($search); 
$search = trim($search);
$words = explode(' ', $search);
$words_condition = array();
$arguments = array();
foreach ($words as $word) {
    $words_condition[] = 'TXT LIKE ?';
    $arguments[] = '%'.$word.'%';
}

    $query = $db->prepare("SELECT * FROM `post` WHERE ".implode(" OR ",
    $words_condition)." ORDER BY POP DESC"); $query->execute($arguments);

About my table structure ("post" table) there are 4 Column...

1. ID [INT] 2. SUB (the subject of the post) [STRING] 3. TXT (the body/full text) [STRING] 4. POP [INT] (hits of visit this post).

Answer 1

CREATE TABLE post (id INT, txt varchar(100), pop int);
INSERT INTO post VALUES (1, "key key cle cle", 1);
INSERT INTO post VALUES (2, "key key cle cle", 2);
INSERT INTO post VALUES (3, "key key cle", 2);
INSERT INTO post VALUES (4, "key cle cle", 1);

SELECT *, (LENGTH(txt) - LENGTH(replace(txt, 'cle', '')))/LENGTH("cle") as cle, 
    (LENGTH(txt) - LENGTH(replace(txt, 'key', '')))/LENGTH("key") as key,
    (LENGTH(txt) - LENGTH(replace(txt, 'cle', '')))/LENGTH("cle")+(LENGTH(txt) - LENGTH(replace(txt, 'key', '')))/LENGTH("key") as nbKW
FROM post 
ORDER BY nbKW DESC, pop DESC;

OK I test it and it works really well ! You have to adapt it with you argument !