Search in related table

Navigation
-1

I have a search script that scans up to three fields of a table named products, I now need to do a search on a table named tag, the product table has the tag id that relates to the tag table, but I am not able to implement.

The search looks like this: 

$pesquisa = $_REQUEST['s'];
$pesquisa = (strtolower($pesquisa));    

// Agrupando campos no concat.
$campo = 'CONCAT(descricao, " ", resumo, " ", detalhes, " ", codigo_msb)';
// dividindo as palavras pelo espaço
$palavras = explode( " ", $pesquisa );  
// eliminando ítens vazios 
$palavras = array_filter($palavras);

// Inicializando a variável
$where = '';
$cola = 'WHERE ';

foreach ($palavras as $palavra) {
  // Removendo espaços em branco
  $palavra = trim($palavra);      
  $palavra = mysql_real_escape_string($palavra, $conexao); 
  $where .= $cola.$campo.' LIKE "%'.$palavra.'%" ';
  $cola = 'AND ';
}

mysql_select_db($database_conexao, $conexao);
$query_rsBusca = "SELECT 
                      produtos.id_marca,                          
                      marca.descricao AS marca  
                    FROM
                      produtos
                      INNER JOIN marca ON (produtos.id_marca = marca.id_marca) ".$where." ";
$rsBusca = mysql_query($query_rsBusca, $conexao) or die(mysql_error());
$row_rsBusca = mysql_fetch_assoc($rsBusca);
$totalRows_rsBusca = mysql_num_rows($rsBusca);

The products table has the tag_id that relates to the tag table and needs to search the tag name.

The error that is occurring is this: 

Column 'descricao' in where clause is ambiguous
    
asked by anonymous 15.01.2015 / 22:10

2 answers

1

The error is there at the beginning of your code (the explanation is what Fernando said, has more than one column with the same name involved in the query): 

$campo = 'CONCAT(descricao, " ", resumo, " ", detalhes, " ", codigo_msb)';

The solution depends on which description you want to use in WHERE . Assuming it is the product: 

$campo = 'CONCAT(produtos.descricao, " ", resumo, " ", detalhes, " ", codigo_msb)';

About your last comment, this: 

WHERE produtos CONCAT(...

should be: 

WHERE CONCAT(...
    
16.01.2015 / 02:10
1

The error you posted is already explicitly saying what the error is. It is the following table produtos should also contain a column named descricao , right? So since you are not using alias in your query, this error is occurring: Column 'descricao' in where clause is ambiguous , as you posted in the question. Because the database does not know which table you want to use the descricao column, since it is in both tables.

So what you should do is the following:

  • Put alias in the query in your tables;
  • Use alias to indicate from which table you want the column explicitly;

Follow the modified code: 

// modifique seu 'where' que é o que está causando o problema
// coloquei como alias "p ou m", pois não sei de qual tabela você quer
$where .= $cola.'p ou m'.$campo.' LIKE "%'.$palavra.'%" ';

// adicione o alias a sua consulta
$query_rsBusca = "SELECT 
                      p.id_marca,                          
                      m.descricao AS marca  
                    FROM
                      produtos as p
                      INNER JOIN marca as m ON (p.id_marca = m.id_marca) ".$where." ";

Or as I realized that instead of alias you were using the table name that works similar to alias . You can simply add the table name to where to specify which column is where : 

// coloquei como alias "produto ou marca", pois não sei de qual tabela você quer
$where .= $cola.'produto ou marca'.$campo.' LIKE "%'.$palavra.'%" ';
  

To avoid this kind of problem, I have become accustomed to default to always use aliases, in my queries for all fields even though they are not ambiguous.

Your second question (in the comments) , is like @bfavaretto, quoted: 

WHERE produtos CONCAT(...

should be: 

WHERE CONCAT(...
  

Code ( second question ) taken from @bfavaretto's reply

     

I've created an iterative example of the complete solution, here .

    
16.01.2015 / 01:12
How to apply regex, using C # in a pdf file? [closed] Comparing dates in JavaScript