SQL get all possible results using separators

Question

I have database with list of similar words. I need to get similar words and I am trying this approach to get every similar word:

1. I am using LIKE on mysql query to get similar words.
2. It's not enough to use LIKE, so I make every possible string with % to get more similar results. I don't know and I can't find if there is any LIKE alternative to find much more relevant queries.

So for example to find similar words like "EL", I am using this query:

SELECT * FROM `words` WHERE word LIKE 'el' OR word LIKE '%el' OR word LIKE '%el%' OR word LIKE '%e%l%'

And it returns only one result, which is not what I want to. However if I would use multi_query and multiple queries like:

SELECT * FROM `words` WHERE word LIKE 'el';
SELECT * FROM `words` WHERE word LIKE '%el';
SELECT * FROM `words` WHERE word LIKE '%el%';
SELECT * FROM `words` WHERE word LIKE '%e%l%';

To fetch these I use:

if ($con->multi_query($query)) {
do {
    /* almacenar primer juego de resultados */
    if ($result = $con->store_result()) {
        while ($row = $result->fetch_row()) {
            printf("%s\n", $row[0]);
        }
        $result->free();
    }
    /* mostrar divisor */
    if ($con->more_results()) {
        printf("-----------------\n");
    }
    } while ($con->next_result());
}

/* cerrar conexión */
$con->close();

These are (example/similar) results I get with it:

el,espinel,el,wheels,espinel,wheels

It would get all the possible results. Of course I would need to filter the duplicates, but I would get them all.

For single query I use:

$result = $con->query($query);
$row = mysqli_fetch_row($result);

However I think multiple queries and filtering would take more time then single query, so I am looking for a way to get all the results on single query or even better without creating all possible variations of string.

Answer 1

As mentioned already, you only need to run the last query. In order to get the desired order of the results (relevance), you'd need to implement that logic via php.

$keyword = 'Your search term';

function getRelevance($value,$keyword){
    $value = strtolower($value);
    $keyword = strtolower($keyword);

    $index = strpos($value, $keyword);
    $word_index = strpos($value, ' '.$keyword);

    if($index==0) // first word starts with keyword
        return 3;
    else if($word_index!==false) // any word starts with keyword
        return 2;
    else if($index!==false) // keyword matches anywhere
        return 1;
    else
        return 0;
}

$keyword = mysqli_real_escape_string($con, $keyword); // prevent SQL injection
$res = $con->query("SELECT * FROM words WHERE `word` LIKE '%$keyword%'");

$words = array();
while($line = $res->fetch_assoc()){
    $line['relevance'] = getRelevance($line['word'],$keyword); // assign relevance value based on the "el" query and the elements word
    $words[] = $line;
}

function compareRelevance($a,$b){
    return $b['relevance'] - $a['relevance'];
}
usort($words,'compareRelevance');

This will fetch all matching entries from the 'words' table and sort them based on the search term.