How to view data from a foreign key table in my main table?

Question

In PHP as I do to appear instead of the foreign key id, appear the field that has the data?

My group table has the column id_grupo and column grupo_nome.

I have product table there have the product groups I have registered in another table, but when I put to display in php, instead the name is appearing the group number.

this is code I learned to call the table data

product table

   prod_id|prod_nome     |prod_grupo
   1      |Rebite Pop 406|    1
   2      |Rebite pop 406|    2

Group table

grupo_id |   grupo nome
1        |    alumínio
2        |    inox

This code on my php page

<?php
include("conexao.php");
$consulta = "SELECT * FROM cadastro_produtos";
$con = $conn->query($consulta) or die($conn->error);
?>

<?php while($dado = $con->fetch_array()){ ?>
<tr>
<td><?php echo $dado["prod_id"];?></td>
<td><?php echo $dado["prod_nome"];?></td>
<td><?php echo $dado["prod_grupo"];?></td>
</tr><?php } ?>

<td><?php echo $dado["prod_grupo"];?></td> aparece o numero, como faço para aparecer o nome do grupo ?

Thank you

Answer 1

Good, the information that you want to display is in another table, it’s not even?

You want to show off the field grupo_nome that’s on the table grupo.

On your table of produtos you have a relationship with the group, through the field prod_grupo, which is a foreign key (FK - Foreign Key).

What you need to do is a link between the two tables in your query and then you can retrieve the information you want. We do this through JOIN. Thus:

SELECT p.*, g.grupo_nome
  FROM cadastro_produtos p
 INNER JOIN grupo g ON p.prod_grupo = g.id_grupo

From there on the page just use:

<td><?php echo $dado["grupo_nome"];?></td>

Note that in the JOIN we use the foreign and primary keys to make the link between the tables.

More information on JOIN: http://dev.mysql.com/doc/refman/5.7/en/join.html