Concatenate result of select

Question

Concatenate result of select

Navigation

#1 by (4 votes)

3

I'm having a hard time showing the result of select the way I want. I will explain first what I have and then what I want, I have two tables in the database:

product_type: product_type_type, product_type and product_mark:
product_id and product_name.

I made select by joining the two tables to show me the result of the two:

<select name="carne">
    <option value="vazio">
   </option> 
<?php 
$sql = "(select tipo_produto from produto_tipo) 
  union (select nome_marca from produto_marca)";
$result = mysqli_query($conexao, $sql);
while($linha = mysqli_fetch_assoc($result)){ ?>
 <option value=" <?php echo $linha['tipo_produto'].$linha['nome_marca']; ?> ">
<?php echo utf8_decode($linha['tipo_produto'].$linha['nome_marca']); ?>
</option> 
<?php } ?>  
</select>

So far so good, but it is not showing the concatenated result. It's showing like this:

arroz
tio jõao
urbano

I need you to show this:

arroz  tio joão
arroz  urbano

Since the result of the first part ( arroz ) comes from a table and the result of the second part ( tio joao ) comes from the second table.

php mysql

asked by anonymous 09.02.2016 / 20:27

1 answer

Problem with sieve of Eratosthenes - JAVA Comparator - PriorityQueue

score 4 · Accepted Answer

The problem is in UNION and in modeling

UNION server to merge results other than 2 or more SELECTS that can be completely independent, and these results will be listed under strong> just having the same number of columns with the same data type respectively!

If you want your query to return the result of a relationship on the same line, you can use the WHERE (obsolete) or JOIN

Considering the information you've passed, there is no PRODUCT table and you want a product type to have multiple brands and a brand has multiple product types forming a relationship MANY to MANY

In this scenario it is necessary to create a third table just to make the relationship between the 2 and the code would look like:

    Select tipo_produto , nome_marca FROM produto_tipo INNER JOIN 
TabelaDeRelacao ON produto_tipo.id_produto_tipo = TabelaDeRelacao.FK_Produto_Tipo INNER 
JOIN  produto_marca on produto_marca.id_produto_marca = TabelaDeRelacao.fk_produto_marca

But your example suggests that the product_type name is not very appropriate for your table and should be product , have a third field fk_product_name with reference to the product_brand table and generate a ONE to MANY relationship (a brand has multiple products and a product has a single brand)

And in this case the solution would be:

   Select tipo_produto , nome_marca FROM produto_tipo INNER JOIN 
produto_marca ON produto_tipo.fk_produto_marca = produto_marca.id_produto_marca

Or there must be a third table Produto (which has, for example, name, fk_type_product, fk_product_name) forming 2 ONE to MANY

 select tipo_produto , nome_marca from Produto 
inner join produto_tipo on fk_produto_tipo = id_produto_tipo
inner join produto_marca on fk_produto_marca = id_produto_marca