How to get around an sql situation where I don’t have all the information

Asked

Viewed 27 times

0

I’m using the php programming language where:

I have the following method of a class:

public function executaquery($s, $con) {
    $query = '';
    $total = 0;
    $s = str_replace(".", ";", $s);
    $s = str_replace(",", ";", $s);
    $s = str_replace(" ", ";", $s);

    $resultados = (explode(";",$s));

    $total = count($resultados);

    if ($total >= 1) {
        foreach ($resultados as $resultado) {
            if ($query == '') {
                $query = "SELECT resposta FROM suporte where pergunta like '%".$resultado."%'";
            }
            else {
               $query = "{$query} and  pergunta like '%".$resultado."%'";
            }
        }
    }               

    $result = mysqli_query($con,$query) or die();

    return $result;
}

When I type for example the following sentence: windows installation.

My method returns me 1 record in my query;

But if I put windows setup, as I have two questions in my bank or containing installation or containing configuration my program does not bring any because as I am using the operator AND in my query the program can not locate the three terms above in case I could bring the information if I could change operator to OR in my query.

Does anyone know of any mechanism to circumvent a situation like this? will I have to place a query and check the return otherwise return nothing the same query changing operator?

php mysql sql

  • You need to define what you want in return. If you want the answers to contain ALL the terms then use AND, if you want the answer to contain AT LEAST ONE of the terms, then use OR. The specification of your application is what will determine how you should implement SELECT. If it is some other alternative to these two then you will have to study how to implement. Evaluate if the Full-Text Search functions cannot help you with the solution [https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html].

1 answer

0

Selects with LIKE are performance-poor. My suggestion for this situation would be to separate the content of your "answer" column into "words" at the time you are registering. Later you associate each word with this support record.

When performing SELECT, you search for the words and identify the associated support records. This way you can even sort by more relevant records.

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.