inner join mysql 2 tables

Question

inner join mysql 2 tables

Navigation

#1 by (0 votes)
#2 by (-1 votes)

0

Good night guys, I need to link two tables and I can not fix it or find where I'm going wrong.

I have two tables:

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

Table 2: br_stat | id - name - (1 - Santa Catarina)

Query: name and iduf field of the br_region table and name / strong> br_region.name

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

PHP Code

<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>

php join mysqli inner

asked by anonymous 21.10.2017 / 04:19

2 answers

-1

Change your code for 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>

21.10.2017 / 04:41

Import one .java into another .java [closed] Function not running in Javascript

score 0 · Accepted Answer

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

In return select you are returning 2 columns, both have the same name / title , in front of 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 repetition:

<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 have created a nickname for each table, where br_regiao became r and br_estado turned and . Obviously if the query was larger it would be advisable to use suggestive nicknames in order not to get lost during the
Later it also assigns aliases the columns that are returned so that they have distinct names and PHP can generate valid indexes for the data array. See:

<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 above return I'm only returning the id of the regions table. If you need to also return the id of the status table, be sure to add an alias because the column names are the same.