PHP Display only the nearest result

Question

Good morning everyone, I didn’t know exactly how to put the title, nor how to research it. I have a if else in PHP that says that if the result is not found at first, it will try to find a similar result by making an array with the text. Let’s say for the example cited below my base has something like significado palavra sono and the result of that Estado caracterizado por supressão da vigília... In this case the words da and palavra shall be located on other lines, but the line significado palavra sono will be the most localized with a total of 3 locations.

How do I rank this and display only the most localized line ?

PHP

$texto = "significado da palavra sono";
$palavras = explode(" ", $texto);
foreach($palavras as $palavra){
    $sql = "SELECT * from dicionario where recebido like '%$palavra%'";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()){
            echo "<p>[".$palavra."]Resultado similar: ".$row['resultado']."<p>";
        }
    }
}

Later on I want to try to implant something about probability. Another question, is there a better way to do this ?

Thank you.

Answer 1

You must replace the LIKE and use the Full-Text Search, in this case it should be used as an example:

mysqli_query($con, '
  SELECT   *, 
           MATCH(recebido) AGAINST ("'.$busca.'") AS relevancia 
  FROM     dicionario 
  WHERE    MATCH(recebido) AGAINST ("'.$busca.'")
  ORDER BY relevancia DESC
');

This will get the lines that the $busca is in recebido and will order by degree of accuracy, if we can say so.

---

I believe it is advisable to make one INDEX of FULLTEXT of the column that will use this resource, thus:

ALTER TABLE `dicionario`
    ADD FULLTEXT INDEX `recebido_nome_do_index` (`recebido`);

This is even an advantage over LIKE that does not use the defined index. ;)