PHP Show only the closest result

0

Good morning everyone, I did not know exactly how to put the title, nor how to research it. I have if else in PHP that says that if the result is not found first, it will try to find a similar result by doing an array with the text. Let's say that for the example quoted below my base has something like significado palavra sono and the result of that is Estado caracterizado por supressão da vigília... In this case the words da and palavra will be located in other lines, however the significado palavra sono line 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 I want to try to implement something about probability. Another question, is there a better way to do this?

Thank you.

    
asked by anonymous 29.01.2017 / 13:41

1 answer

1

You should replace LIKE and use Full-Text Search , in this case you should use 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 $busca is in recebido and will sort by degree of precision , if we can say so.

I think it's a good idea to INDEX FULLTEXT from the column that will use this resource, like this:

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

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

    
29.01.2017 / 14:18