Loop Help

[XploreR]

hi all. i need some help with this loop problem. Someone told me it is inefficient if it worked the way i wanted it to work and the output only shows 1 result. I know i must put the $sql4 statement inside the $sql3 while loop but i simply just can't get it to work. Any Help appreciated

What my loop does:

sql3 will select all data from a table when a column Gameid equals to another id number. sql4 will select data when $Hid from sql3 matches another field Called Hid too in table (xiccc.CodeCreators)
However it does only shows 1 result

Code:

$sql3 = "SELECT * FROM xiccc.$table2
WHERE Gameid='$Id' ORDER BY Territory ASC";

$db->query($sql3);

while ($db->next_record()) {

$Territory = $db->f(Territory);
$TotalCodes = $db->f(TotalCodes);
$Date = $db->f(Date);
$Hid = $db->f(Hid);
}


$sql4 = "SELECT Nick, Email FROM xiccc.CodeCreators
WHERE Hid='$Hid'";

$db->query($sql4);

while ($db->next_record()) {

$Nick = $db->f(Nick);
$Email = $db->f(Email);

$color = ($i++ % 2 ) ? '#000000' : '#333333';
$tmp3 = "<tr bgcolor='$color'><td>&nbsp;<font size='2' face='Verdana, Arial'><a href='http://www.xplodercodes.com/codes/dreamcast/codes/codes.phtml?id=$Id'>$Territory</a></font></td><td>&nbsp;<font size='2' face='Verdana, Arial'>$TotalCodes</font></td><td>&nbsp;<font size='2' face='Verdana, Arial'>$Date</font></td><td>&nbsp;<font size='2' face='Verdana, Arial'><a href='mailto:$Email'>$Nick</a></font></td></tr>";

$t->set_var('data', $tmp3);
$t->parse('CBlock', 'CodesBlock', true);

}

[petesmc]

Try the following code...it is inefficient as you'd have allot of queries!!!

$sql3 = "SELECT * FROM xiccc.$table2 
WHERE Gameid='$Id' ORDER BY Territory ASC"; 

$db->query($sql3); 

while ($db->next_record()) { 

$Territory = $db->f(Territory); 
$TotalCodes = $db->f(TotalCodes); 
$Date = $db->f(Date); 
$Hid = $db->f(Hid); 


$sql4 = "SELECT Nick, Email FROM xiccc.CodeCreators 
WHERE Hid='$Hid'"; 

$db->query($sql4); 

while ($db->next_record()) { 

$Nick = $db->f(Nick); 
$Email = $db->f(Email); 

$color = ($i++ % 2 ) ? '#000000' : '#333333'; 
$tmp3 = "<tr bgcolor='$color'><td> <font size='2' face='Verdana, Arial'>
<a href='http://www.xplodercodes.com/codes/dreamcast/codes/codes.phtml?id=$Id'>$Territory</a></font>
</td><td> <font size='2' face='Verdana, Arial'>$TotalCodes</font></td><td> 
<font size='2' face='Verdana, Arial'>$Date</font></td><td> <font size='2' face='Verdana, Arial'>
<a href='mailto:$Email'>$Nick</a>
</font></td></tr>"; 

$t->set_var('data', $tmp3); 
$t->parse('CBlock', 'CodesBlock', true); 

} 
}

[XploreR]

It dosen't work either as it now shows the last result. Any help guys? I think it's to do with the parsing part or setting the variable?