Select with indefinite amount of conditions

Question

The thing is, I have a field of research, and I want to search in different places than what’s registered, like this: tenis nike shox preto.

I put in research: tenis preto. If I use a description like '%tenis preto%' he had returned me nothing.

I’d have to put description like '%tenis%' or descricao like '%preto%'.

So far so good, I put in SQL there two conditions, however and if he wants to search with 3 or more words, will vary the amount of where that I need to use, has how to do this?

I’m using:

• PHP
• Postgresql

Answer 1

It is not necessary to assemble strings, which is actually bad practice. Just pass the parameters in the form of an array with either of the two syntaxes:

select *
from t
where descricao ilike any ('{"%tenis%", "%nike%", "%shox%", "%preto%"}')

select *
from t
where descricao ilike any (array['%tenis%', '%nike%', '%shox%', '%preto%'])

ilike is insensitive to the box. The above logic is or. If need be and the any for all

Answer 2

One possibility with a more complete result is to use PHP to split your search into separate words, and generate the WHERE clause for you:

<?php

   $pesquisa = 'carro verde amassado ';

   // Aqui você pode juntar vários campos no concat.
   $campo = 'CONCAT( title, " ", description, " ", author)';
   // Ou usar um só, mas nesse caso talvez compense um LIKE tradicional
   // $campo = 'title';

   $palavras = explode( ' ', $pesquisa ); // dividindo as palavras pelo espaço
   $palavras = $array_filter($palavras); // eliminando ítens vazios

   $where = '';
   $cola = 'WHERE ';

   foreach ($palavras as $palavra) {
      $palavra = trim($palavra); //Removendo espaços em branco
      $palavra = mysql_real_escape_string($palavra); // Aqui você sanitiza de acordo com o DB
      $where .= $cola.campo.' LIKE "%'.$palavra.'%" ';
      $cola = 'AND ';
   }

   echo htmlentities( $where );
?>

And the result will be:

WHERE
   CONCAT( title, " ", description, " ", author) LIKE "%carro%" AND
   CONCAT( title, " ", description, " ", author) LIKE "%verde%" AND
   CONCAT( title, " ", description, " ", author) LIKE "%amassado%"

(line breaks added for easy reading)

This way the search will find all these results:

O carro amassado era verde
A carroceria foi atingida por abacates verdes amassados
Verde e amassado carro

Note that although some lines do not have exact results, it is better to have more things than the user does not find what they need. Keep in mind, however, that the price you pay for complexity is slower research. LIKE and indexes do not work well together.

Answer 3

It depends on the complexity of the functionality you want to implement and the size of your application. For a small and simple application you can fix up to three fields, require at least the first one to be filled in and make three Likes being that the Likes of the unfilled fields would be something like LIKE "%".

Now if you need more complexity and a larger application I suggest you implement a tag table and a relationship table between tags and records that you want to return.

When doing a google search the string array is sent to the function that will search for records that are related to those tags.

With a little more complexity you can implement tag ranks; ignore tags with smaller ranks if they make no minimal returns; ignore gender, number and verbal pushbacks; automate tag extraction from records; etc

I implemented something like this once but for MS-SQL and . Net because although DBMS has native tools for this type of SELECT the hosting did not allow to activate them (unless the server was dedicated, which was expensive).

Answer 4

Friend. There is a simple solution to your problem.

I’m using the MySql, but it is easy to change for your case.

First, set the columns in which you want to fetch the value, imagining it to be more than one. (Works for 1 or N columns).

/* Colunas da Tabela */
$aColumns = array (
        'ID',
        'PRODUTO',
        'DESCRICAO'
);

Then mount the WHERE:

/* Filtros.. */     
$sWhere = "";
if ($_GET ['sSearch'] != "") {
    $sWhere = "WHERE (";
    for($i = 0; $i < count ( $aColumns ); $i ++) {
        $sWhere .= $aColumns [$i] . " LIKE '%" . strtoupper ( mysql_real_escape_string ( $_GET ['sSearch'] ) ) . "%' OR ";
    }
    $sWhere = substr_replace ( $sWhere, "", - 3 );
    $sWhere .= ')';
}

But the problem in question will continue, for the search, if sought: Tenis Preto, will only return the values containing the total range.

As a solution, one can use the IMPLODE and the EXPLODES:

$busca  = "Tenis Nike Preto"; //Set GET ou POST..
$busca_explode =  explode(' ', $busca); //Desmonta String..
print_r($busca_explode) ; // Visualização como fica..
$busca_total = implode('%', $busca_explode); // Tenis%Nike%Preto
echo '<br>'.$busca_total; 

Just pass this value to the WHERE above instead of searching straight for the $_GET ['sSearch'].