Search system - Select within another

Question

Search system - Select within another

Navigation

#1 by (7 votes)
#2 by (1 votes)

5

Hello, I'm doing a search engine, but I'm kind of a layperson about Querys. The method that I thought to refine the search, which in the case would be a simple search between two fields of a table with several products, was to be eliminated 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 each word. But this query ends up returning no items from the table, even though there is some item with some query word.

Any suggestions?

php mysql query busca

asked by anonymous 08.05.2014 / 22:07

2 answers

1

I do not know if I can understand what you want.

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

If so, you can do this:

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

$strSQL = "SELECT * FROM tabela";

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

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

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

}

In this way, assuming the text entered 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%'

14.05.2014 / 17:00

Jquery mask in dynamically created fields Entity Framework - Update nested lists

score 7 · Accepted Answer

I've adapted this solution from a other 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 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 easier reading)

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