Connect 2 tables with same id [duplicate]

1

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

tbl_commission:

ID | comissao
1  | 13
2  | 26

tbl_user:

ID  |  nome 
1   |  João 
2   |  Maria

I need to list in a 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 in the other table?

    
asked by anonymous 27.03.2017 / 19:48

3 answers

2

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

You want to JOIN in SQL. Using MySql, I know two methods of doing 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 DBMSs do not understand the first method. For example, if you are using MSSQL, you will need to merge using ON .

    
27.03.2017 / 19:56
2

Always when you need to make a relationship, in the table that has the data that will relate to the user (in your case), must have a column identifying the user ID, so you can make the relationship. Then change the structure from tbl_comissao to:

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

To relate you can do 2 querys. One to pull the commissions and in the loop make 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 relation:

$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 JOIN clauses

    
27.03.2017 / 19:59
1

To merge the two tables, do the following:

SELECT A.NOME, B.COMISSAO FROM TBL_USUARIO A 
LEFT JOIN TBL_COMISSAO B ON
A.ID = B.ID
    
27.03.2017 / 19:56