How to do While from one table and show data from another?

Asked

Viewed 324 times

2

I have two tables:

Tabela1: 
 Id int A_I;
 Modelo varchar;

Tabela2:
 Id int A_I;
 Id_fk int (foreign key da tabela1);
 cor;

And wanted to do a Tabela1 while but show table data2.

<?php
	include 'conn.php';
	mysqli_set_charset($con,"utf-8");
			
	$result = mysqli_query($con,"select * from tabela1");
	$result1 = mysqli_query($con,"select * from tabela2");
			
	while($row = mysqli_fetch_array($result)){
	
        }
        mysqli_close($con);
?>

How do I show the data now?

php html database
  • 1

    I know you don’t ask for this and that’s why I’m not going to answer down, because you may not want this at all. But why not Join the two tables: SELECT * FROM Tabela1 JOIN Tabela2 ON Tabela1.id = Tabela2.Id_fk;

  • And then how do I get the data from the table2? just in while $cor = $Row['cor']; ?

  • Yes in principle it will be... Have equal column names?

1 answer

4


The solution I left in comment would be the most appropriate:

SELECT * FROM Tabela1 JOIN Tabela2 ON Tabela1.id = Tabela2.Id_fk;

But for coherence between answer/question here is a solution to what you asked for:

In case table 2 can only have one line with the same Id_fk:

$selTab1 = mysqli_query($conn, "SELECT * FROM tabela1;");
$dataJoin = array();
while ($rowTab1 = $selTab1->fetch_assoc()) {
    $selTab2 = mysqli_query($conn, "SELECT * FROM tabela2 where Id_fk={$rowTab1['id']};");
    if($selTab2->num_rows > 0) {
        $rowTab2 = $selTab2->fetch_assoc();
        $dataJoin[] = array(
            'tab1_id' => $rowTab1['id'],
            'Modelo' => $rowTab1['Modelo'],
            'tab2_id' => $rowTab2['id'],
            'cor' => $rowTab2['cor']
        );
    }
}
echo '<pre>', print_r($dataJoin), '</pre>'; // aqui já vai ter os dados das duas

In case table 2 can have more than one line with the same Id_fk:

$selTab1 = mysqli_query($conn, "SELECT * FROM tabela1;");
$dataJoin = array();
while ($rowTab1 = $selTab1->fetch_assoc()) {
    $selTab2 = mysqli_query($conn, "SELECT * FROM tabela2 where Id_fk={$rowTab1['id']};");
    while($rowTab2 = $selTab2->fetch_assoc()) {
        $dataJoin[] = array(
            'tab1_id' => $rowTab1['id'],
            'Modelo' => $rowTab1['Modelo'],
            'tab2_id' => $rowTab2['id'],
            'cor' => $rowTab2['cor']
        );
    }
}
echo '<pre>', print_r($dataJoin), '</pre>'; // aqui já vai ter os dados das duas

Browser other questions tagged php html database

You are not signed in. Login or sign up in order to post.