Connect 2 tables with same id

Question

I think my doubt is easy (from beginner). I have 2 tables in mysql:

tbl_commission:

ID | comissao
1  | 13
2  | 26

tbl_usuario:

ID  |  nome 
1   |  João 
2   |  Maria

Need to list in table:

Nome    |    Comissão
João    |    13 
Maria   |    26

after connecting the database I am using:

$result = mysqli_query($con,"SELECT id, comissao FROM tbl_comissao");
echo "<table border='1'>
<tr>
<th>Representante</th>
<th>Comissão</th>
</tr>";
while($row = mysqli_fetch_array($result))
{
$id = $row['id'];
$comissao = $row['comissao'];
echo "<tr>";
echo "<td>" . $id ."</td>";
echo "<td>" . "R$ ".number_format($comissao,2, ',', '.') . "</td>";
echo "</tr>";
}
echo "</table>";

How do I get the name on the other table?

Answer 1

Whenever you need to make a relation, in the table that has the data that will relate to the user (in your case), you should have a column identifying the user ID, so you can make the relation. Then change the structure of tbl_comissao for:

ID | id_usuario | comissao
1  |     1      |    13
2  |     2      |    26

To relate you can make 2 querys. One to pull the commissions and in the loop do another query to see which is the user of id_usuario

$result = mysqli_query($con,"SELECT id, comissao FROM tbl_comissao");
echo "<table border='1'>
<tr>
<th>Representante</th>
<th>Comissão</th>
</tr>";
while($row = mysqli_fetch_array($result))
{
$usuarioQuery = mysqli_query($con,"SELECT nome FROM tbl_usuario WHERE id = '".$row['id_usuario']."' ");
$rowUser = mysqli_fetch_array($usuarioQuery);

$id = $row['id'];
$comissao = $row['comissao'];
echo "<tr>";
echo "<td>" . $rowUser[0]['nome'] ."</td>";
echo "<td>" . "R$ ".number_format($comissao,2, ',', '.') . "</td>";
echo "</tr>";
}
echo "</table>";

But the best solution is to use INNER JOIN or LEFT JOIN or RIGHT JOIN (depends on your need) to make the relationship:

$result = mysqli_query($con,"SELECT c.id, c.comissao, u.nome FROM tbl_comissao AS c INNER JOIN tbl_usuario AS u ON u.id = c.id_usuario");
echo "<table border='1'>
<tr>
<th>Representante</th>
<th>Comissão</th>
</tr>";
while($row = mysqli_fetch_array($result))
{

$id = $row['id'];
$comissao = $row['comissao'];
echo "<tr>";
echo "<td>" . $row['nome'] ."</td>";
echo "<td>" . "R$ ".number_format($comissao,2, ',', '.') . "</td>";
echo "</tr>";
}
echo "</table>";

Read this topic to see how to use the Joins clauses

Answer 2

Try the query in a DB query before trying it in PHP, it is much easier to test and try.

You want to make a JOIN in SQL. Using Mysql, I know two methods to do this:

SELECT nome, comissao
FROM tbl_comissao
    INNER JOIN tbl_usuario using (id)

The other method is:

SELECT nome, comissao
FROM tbl_comissao c
    INNER JOIN tbl_usuario u ON (c.id = u.id)

Remember that some Dbms do not understand the first method. For example, if you are using MSSQL, you will need to merge using the ON.

Answer 3

To join the two tables, do so:

SELECT A.NOME, B.COMISSAO FROM TBL_USUARIO A 
LEFT JOIN TBL_COMISSAO B ON
A.ID = B.ID