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

Question

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

Navigation

#1 by (1 votes)

0

In PHP how do I appear instead of the foreign key id, appear the field that owns the data?

My has the group table column id_grupo %% and column grupo_nome .

I have the product table there is the products of the groups that signed up in another table, but when I put to display in php instead of the name is showing 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

php mysql

asked by anonymous 17.06.2016 / 01:59

1 answer

Check start and end date and insert text between them! Go through the value of an array of a php function [closed]

score 1 · Accepted Answer

Well, the information you want to display is in another table , is not it?

You want to display the grupo_nome field that is in the grupo table.

In your produtos table you have a relation to the group, through the prod_grupo field, which is a 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 operations. So:

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 JOIN we use foreign and primary keys to link between tables.

More information about JOIN : link