PHP Search system ignore uppercase letters and add keywords

Question

I have a simple search system that searches for values in the comic book and displays on the page. In the table I have a column called palavraschaves where the words I leave as a query parameter are registered. I need two things. The first is that when typing the word with uppercase or lowercase letter it understands the same way without I need to register both in the BD. The second thing is that it adds the words in the search. For example: I entered the words in the table in the following order; house building roof. If I type roof house does not return me anything, just search for one of the two. I want him to search for the two.

Code

$busca = trim($_POST['busca']);
$sql = mysqli_query($conn, "SELECT * FROM pesquisa_clientes WHERE palavraschaves LIKE '%".$busca."%' ORDER BY nome");
$numRegistros = mysqli_num_rows($sql);
if ($numRegistros != 0) {


echo "<h4 class='result'>Resultados para: <b> " . $busca . "</b></h4><br />";

        while ($usuario = mysqli_fetch_object($sql)) {

echo "<div id='resultados'>";
echo "<img src='images/clientes/".$usuario->logo."' alt='Foto de exibição' /><br />";
echo utf8_encode("<h4><b> " . $usuario->nome . "</b></h4><br />");
echo utf8_encode("<p><span><b> " . $usuario->subcategoria . "</b></span></p><br /><br />");
echo utf8_encode("<p><b>Bairro:</b> " . $usuario->bairro . "</p><br />");
echo "<p><b>Telefone:</b> " . $usuario->telefone . "</p><br />";
echo "<a href='". $usuario->link_cliente. "'><b>Saiba Mais</b></a><br /><br />";
echo "</div>";
}
} else {
        echo "<h4 class='result'>Nada foi encontrado com a palavra:<b> ".$busca."</b></h4>";
}
    ?>

Answer 1

• Regarding capital letters and minuscules Your question is not very clear. I will assume that the words in the database are all in minuscules and the user type uppercase or minuscule. In this case just use the function strtolower which converts all characters to lower case

• About "add key words" use REGEXP, explode and implode.

DEMO

$busca = trim($_POST['busca']);
//para remover os espaços duplicados.
$busca = preg_replace('/\s(?=\s)/', '', $busca);
//tudo em minusculas
$busca = strtolower($busca)

//quando uma pessoa digitar umas palavras, elas são explodidas pelo espaço
$exp = explode(" ", $busca); // separando pelo espaço

//e em seguida são implodidas pela | (barra vertical)
$imp = implode("|", $exp); // unindo os valores pela |

//a consulta é como segue
SELECT * FROM pesquisa_clientes WHERE palavraschaves REGEXP '$imp' ORDER BY nome

A | (vertical bar) is necessary for the use of REGEXP, it symbolizes the OR "or", that is, fetch between the values separated by | (vertical bar).

To convert accents you can use the function

Function TirarAcento( $texto ) 
{
    $array1 = array(   "á", "à", "â", "ã", "ä", "é", "è", "ê", "ë", "í", "ì", "î", "ï", "ó", "ò", "ô", "õ", "ö", "ú", "ù", "û", "ü", "ç"); 

    $array2 = array(   "a", "a", "a", "a", "a", "e", "e", "e", "e", "i", "i", "i", "i", "o", "o", "o", "o", "o", "u", "u", "u", "u", "c"); 

    return str_replace( $array1, $array2, $texto ); 
}  

 $busca = trim($_POST['busca']);
 $busca = preg_replace('/\s(?=\s)/', '', $busca);

 $busca = strtolower($busca);

 $busca = TirarAcento($busca);
 ............................
 ............................
 ............................

call the function TirarAcento after the function strtolower

Want to know why? Hover your mouse in the area below yellowed

because the function TirarAcento is making the exchange of accented letters only for minuscules, therefore it is indispensable to put the search terms first in minuscules with the function strtolower

Answer 2

About the uppercase and lowercase letters, I believe that the best way is for you to define how you will save the words in the database, if it is uppercase for example, when reading the words that the user typed you turn that string uppercase with strtoupper(string). The second part of the problem, you can break the string that the user typed with the explode, done this, you will have an array with the captured words, ai just use the implode and separate the values of the arrays by commas in your select.

would look something like this:

$busca = trim($_POST['busca']);
//transformo tudo em maiúsculo
$busca = strtoupper($busca)
//quebro a string nos espaços, e gravo no array
$array= explode(" ", $busca)
//where IN (valor1, valor2,valor3), uso o implode para coloca a virgula após cada valor do array
$sql = mysqli_query($conn, "SELECT * FROM pesquisa_clientes WHERE palavraschaves in ('.implode(',', array_map('intval', $array)).') ORDER BY nome");

I don’t know if this is working because I didn’t test it, but here’s the idea