Inner Join mysql 2 tables

Question

Good evening guys, I need to link two tables and I’m not able to correct or find where I’m going wrong.

I have two tables:

Table 1: br_regiao | id - iduf - name - (1 , 1, Litoral)

Table 2: br_estado | id - nome - (1 - Santa Catarina)

Query: field name and iduf table br_region and field name table br_estado making a field echo br_estado.name and br_region.name

Error : Only shows the table field br_estado.name and in the field br_regiao.name nothing appears.

PHP Codigo

<tbody>
<?php					
$query = mysqli_query($_mysqli,"select * from br_regiao inner join br_estado on br_regiao.iduf = br_estado.id order by br_regiao.id desc");
while ($categoria = mysqli_fetch_array($query,MYSQLI_ASSOC)) {
                                    ?>
    <tr>
      <td><?php echo $categoria['id']; ?></td>
      <td><?php echo $categoria['br_estado.nome']; ?></td>
      <td><?php echo $categoria['br_regiao.nome']; ?></td>
      <td>
        <a class="btn btn-primary btn-sm show-tooltip" title="Edit" href="<?php echo $URL_ADMIN; ?>regiao.php?id=<?php echo $categoria['.id']; ?>"><i class="fa fa-edit"></i></a>
        <a class="btn btn-danger btn-sm show-tooltip" title="Delete" href="<?php echo $URL_ADMIN; ?>regiaoalt.php?id=<?php echo $categoria['id']; ?>"><i class="fa fa-trash-o"></i></a>
      </td>
    </tr>
    <?php
}
?>
</tbody>

Answer 1

The problem is that you are trying to access 2 indexes that do not exist br_estado.name and br_regiao.name.

On the return of select you are returning 2 columns, both have the same name/title, before that PHP will generate an index for only one of the columns. You can see what I said by adding this line inside your loop:

<td><?php echo $categoria['nome']; ?></td>

To solve this problem you can work with Alais for the columns. For ease I did the following:

• I created a nickname for each table, where br_regiao turned r and br_estado turned and. Obviously if the query was larger it would be recommended to use suggestive nicknames so we don’t get lost during select
• Later it also aliases the columns that are returned, so that they have different names and PHP can generate valid indexes for the data array. Behold:

---

<tbody>
<?php                   
$query = mysqli_query($_mysqli,"select r.id, r.nome reg_nome, e.nome est_nome from br_regiao r inner join br_estado e on r.iduf = e.id order by r.id desc");

while ($categoria = mysqli_fetch_array($query,MYSQLI_ASSOC)) {
                                    ?>
    <tr>
      <td><?php echo $categoria['id']; ?></td>
      <td><?php echo $categoria['est_nome']; ?></td>
      <td><?php echo $categoria['reg_nome']; ?></td>
      <td>
        <a class="btn btn-primary btn-sm show-tooltip" title="Edit" href="<?php echo $URL_ADMIN; ?>regiao.php?id=<?php echo $categoria['.id']; ?>"><i class="fa fa-edit"></i></a>
        <a class="btn btn-danger btn-sm show-tooltip" title="Delete" href="<?php echo $URL_ADMIN; ?>regiaoalt.php?id=<?php echo $categoria['id']; ?>"><i class="fa fa-trash-o"></i></a>
      </td>
    </tr>
    <?php
}
?>
</tbody>

In the return above I’m only returning the id table of regions. If it is necessary to return also the id of the state table, remember to add an alias because the column names are equal.

Answer 2

Change your code to this

<?php	

$result = "SELECT A.id, A.id_uf, A.nome AS nome_regiao, B.id, B.nome AS nome_estado FROM BR_REGIAO A LEFT OUTER JOIN BR_ESTADO B ON (A.BR_REGIAO.IDUF = B.BR_ESTADO.ID) ORDER BY B.BR_REGIAO.ID DESC ";
$resultado = mysqli_query($conn, $result);

while( $row = mysqli_fetch_assoc($resultado)){
?>
    <tr>
      <td><?php echo $categoria['id']; ?></td>
      <td><?php echo $categoria['nome_estado']; ?></td>
      <td><?php echo $categoria['nome_regiao']; ?></td>
      <td>
        <a class="btn btn-primary btn-sm show-tooltip" title="Edit" href="<?php echo $URL_ADMIN; ?>regiao.php?id=<?php echo $categoria['.id']; ?>"><i class="fa fa-edit"></i></a>
        <a class="btn btn-danger btn-sm show-tooltip" title="Delete" href="<?php echo $URL_ADMIN; ?>regiaoalt.php?id=<?php echo $categoria['id']; ?>"><i class="fa fa-trash-o"></i></a>
      </td>
    </tr>
<?php
}
?>
</tbody>