List all data with the same id

Question

I have the following problem: I have a table called pedido. In this table I get data from two other tables:

I would like to make a list only of products that have the same id_venda. In my code only appears the first item added with equal id:

    <?php

$perfil1=mysql_query("SELECT * FROM pedido WHERE id_venda='$id'");

$lista=list($id, $produtosb , $idvenda)=mysql_fetch_row($perfil1);


$prod = "SELECT * FROM produtos WHERE id_produto=". $lista[1];
$query = mysql_query($prod);
$b=mysql_fetch_array($query);
$prod = $b ['produtos'];

?>

<input type="text" name="id" style="width: 450px" readonly="true"  value="<?php echo $prod; ?>"><br>

I tried using Loop while, but it returns all table data. Which repeat loop should I use?

Answer 1

Come on...

Assuming that the column produtos be the sale product id in the table pedidos, and with id_venda defined, we solve the problem with a query only:

Supposing that your column produtos has been:

id | nome

Behold the select:

$SQL = "SELECT * FROM produtos WHERE id_produto in (SELECT produtos FROM pedido WHERE id_venda='$id')";
$result = mysql_query($SQL);

while ($db_field = mysql_fetch_assoc($result) ) {

print $db_field['ID'] . "<BR>";
print $db_field['nome'] . "<BR>";

}

EDIT:

Use with Join:

$SQL = "SELECT * FROM pedido inner join produtos on produtos = id_produto where id_venda='$id'";