Select with undefined number of conditions

5

It's the following, I have a search field, and I want to search in places other than what is registered, type like: tenis nike shox preto .

I put in the search: tenis preto . If I use a like '%tenis preto%' description it will not return anything to me.

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

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

I'm using:

  • PHP
  • PostgreSQL
asked by anonymous 16.10.2014 / 08:30

4 answers

9

No need to mount strings, which is bad practice. Just pass the parameters in array form with either of 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 logic above is or . If it is necessary to do and change any by all

    
17.10.2014 / 23:12
4

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 easier reading)

In 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's better to have more things than the user can not find what they need. Keep in mind, however, that the price you pay for complexity is a slower search. LIKE and indexes do not work well together.

    
16.10.2014 / 19:13
1

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 set up to three fields, require at least the first one to be filled and make three likes and 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 search type google the array of strings is sent to the function that will look for records that are related to those tags.

With a little more complexity you can implement tag ranks; ignore the tags with smaller ranks if they do not have a minimum return; ignore gender, number and verbal pushups; automate the extraction of tags from the records; etc.

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

    
16.10.2014 / 13:24
0

Friend. There is a simple solution to your problem.

I'm using MySql , but it's easy to change for you.

First, define the columns in which you want to fetch the value, assuming it is more than one. (Works for 1 or N columns.)

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

Then mount 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 .= ')';
}

However, the problem will continue for the search, if it is searched for: Tenis Preto , will only return the values that contain the total range.

As a workaround, one can use IMPLODE and EXPLODE :

$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 WHERE above instead of searching straight for $_GET ['sSearch'] .

    
16.10.2014 / 16:57