Search in related table

Question

I have a search script that scans up to three fields of a table called products, now I need to do a search in a table called brand, the product table has the id_brand that relates to the brand table, but I’m not able to implement.

The research is 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 id_brand that relates to the brand table and need to do a search by brand name.

The error that is occurring is this:

Column 'descricao' in where clause is ambiguous

Answer 1

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

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

The solution depends on what description you want to use on WHERE. Assuming it’s the product:

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

---

About your last comment, this:

WHERE produtos CONCAT(...

should be:

WHERE CONCAT(...

Answer 2

The error you posted is already explicitly telling you what the error is. The following is the table produtos should also contain a column called descricao, right? Then how are you 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 column descricao, since she’s on both tables.

So what you should do is this:

• Place alias in the query in your tables;
• Use the 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 how I realized that instead of alias you were using the table name that works similar to the alias. You just can’t where add the table name to specify which is the column of the where:

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

Me, to avoid that kind of trouble, I’m used to default to always use alias in my queries for all fields even if they are not ambiguous.

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

WHERE produtos CONCAT(...

should be:

WHERE CONCAT(...

^{Code (second doubt) taken from @bfavaretto’s reply}

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