Search System - Select within another

Question

Hi, I’m running a search engine, but I’m kind of a layman when it comes to querys. The method I thought to refine the search, which in this case would be a simple search between two fields of a table with several products, was to go eliminating through selects.

$varBusca = str_replace(array(',',' ','.','%','-','/','\\'),'-',$_POST['busca']);
$varBusca = explode('-',$varBusca);
/*  metodo 1 */
$query_busca="";
$pos_busca=0;
foreach($varBusca as $chave)
{
    if ($pos_busca==0){
        $query_busca.="SELECT * FROM ( # ) WHERE pro_nome LIKE '%$chave%' OR pro_descricao LIKE '%$chave%'";
    }else{
        $query_inserida="SELECT * FROM ( # ) WHERE pro_nome LIKE '%$chave%' OR pro_descricao LIKE '%$chave%'";
        $query_busca=str_replace('#',$query_inserida,$query_busca);
    }
    $pos_busca++;
}
$query_busca=str_replace('#','produtos',$query_busca);
echo $query_busca;

I break the search string and search for every word. But this query ends up returning me no table item, even if there is some item with some query word.

Any suggestions?

Answer 1

I adapted this solution from a another answer from me. The idea is to use PHP to divide your search into separate words, and generate the WHERE clause for you, but in a way that you already have a query that searches all the words at once:

<?php

   $pesquisa = str_replace( array( ',', '.', '%', '-', '/', '\\' ),' ', $_POST['busca'] );
   $palavras = explode( ' ', $pesquisa ); // dividindo as palavras pelo espaço
   $palavras = array_filter($palavras); // eliminando ítens vazios

   $query = 'SELECT * FROM produtos ';
   $cola = 'WHERE ';

   //Aqui você pode juntar vários campos no concat.
   $campo = 'CONCAT( pro_nome, " ", pro_descricao)';

   foreach ($palavras as $palavra) {
      $palavra = trim($palavra); //Removendo espaços em branco
      $palavra = mysql_real_escape_string($palavra); //Precisa da conexao com o banco!
      $query .= $cola.campo.' LIKE "%'.$palavra.'%" ';
      $cola = 'AND ';
   }

   echo htmlentities( $query );
?>

And the result when searching lápis azul 3b will be:

SELECT * FROM produtos WHERE
   CONCAT( pro_nome, " ", pro_descricao) LIKE "%lápis%" AND
   CONCAT( pro_nome, " ", pro_descricao) LIKE "%azul%" AND
   CONCAT( pro_nome, " ", pro_descricao) LIKE "%3b%"

(line breaks added for easy reading)

This way the search will find all these results:

Lápis azul 3b
Lápis 3b azul-claro
Lápis azulado 3bcd

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 a slightly slower search.

Answer 2

I don’t know if I understand what you’re looking for.

You have a search field, where a user can type "Any text in the field" and in the search you want to bring all records that in a given field contains one of the field words ("One", "text", "any", "no", "field")?

If that’s it, you can do it like this:

$search = explodes(" ", $_POST["search"]);

$strSQL = "SELECT * FROM tabela";

foreach ( $search as $key => $value ){

    if ( $key == 0 ){
        $strSQL .= " WHERE ";
    }else{
        $strSQL .= " OR ";
    }

    $strSQL .= "campo like '%{$value}%'";

}

thus, assuming that the text typed in the search field is "Any text in the field", the value of the variable $strSQL will be:

SELECT * FROM tabela WHERE campo like '%Um%' OR campo like '%texto%' OR campo like '%qualquer%' OR campo like '%no%' OR campo like '%campo%'