PHP/Mysql - Print values with relation between tables

Question

In a system I need to print paint cans and each one has its own colors. I did it in a way that works, but repeats several times the requests in the database.

I made two tables(Example):

latas
______________
id | nome
1  | uso geral
2  | teste 1
3  | teste 2


cores
__________________________
id | nome | hexa | id_lata
1  | azul | #0495| 1
2  | preto | #000| 1

My code is more or less like this at the moment:

<?php
$sql_latas = mysqli_query($conn, 'SELECT * FROM latas');
while($row_latas = mysqli_fetch_array($sql_latas)){
?>
   <div class="lata">
      <span><?php echo $row_latas['nome']; ?></span>
   </div>

   <ul class="cores">

      <?php
      $sql_cores = mysqli_query($conn, 'SELECT * FROM cores WHERE id_lata = '.$row_latas['id']);
      while($row_cores = mysqli_fetch_array($sql_cores)){
      ?>

         <li><?php echo $row_cores['nome']; ?></li>

      <?php
      }
      ?>

   </ul>
<?php
}
?>

How can I do that?

Answer 1

You can design only one SQL, using JOIN to join the tables based on id_lata, in this case, you will be able to obtain the results of each of the tables, and also to distinguish one column from the other, you can in SELECT, set what is the column and what will be the name of it, as the example below:

SELECT 
    latas.nome as nome_lata,
    cores.nome as cor_lata,
FROM latas
JOIN cores ON(cores.id_lata=latas.id)


while($row=mysqli_fetch_assoc($sql)){
    echo $row['nome_lata'];
    echo $row['cor_lata']."<br><br>";
}