Using query result to do another query in loop

[H-Fish]

Hey everyone, i appear to have run into a bit of a brick wall here and i need some help if possible.

 

I have 2 tables, one called users2 and one called books.

Books has a field called UserId, which is the ID of the user that added the book to the database.

 

My problem, is that i need to take this ID from the book table, and use it to get some information from the user table.

 

Here is my code to get the ID from the book table:

 

query4="SELECT UserId, format, artist, name, label FROM books WHERE name = \"$getalbum\" order by name asc"; 
$result=mysql_query($query4); 
$num=mysql_numrows($result); 
$i=0;
while ($i < $num) 
{ 
$ownerid=mysql_result($result,$i,"UserId"); 
++$i; 
echo "$ownerid";

}

If i try to repeat this code (ie have two while loops inside each other, then it does not output the result, despite getting no error messages.

 

Here is the query i use to get the information from the users field

	   $query4="SELECT UserId, displayimage FROM $dbtable WHERE UserId = $ownerid order by UserId asc"; 

 

If anyone can shed some light on this i would greatly appreciate it

 

[H-Fish]

By the way, here is my attempt that doesn't seem to output anything

 

// Show the results for every user who has a certain album
$query4="SELECT UserId, format, artist, name, label FROM books WHERE name = \"$getalbum\" order by name asc"; 
$result=mysql_query($query4); 
$num=mysql_numrows($result); 
$i=0;
while ($i < $num) 
{ 
$ownerid=mysql_result($result,$i,"UserId"); 

//echo "<tr><td><a href = \"profile.php?id=$ownerid\">Profile</a></td></tr>";
$sql = "SELECT UserId, displayimage FROM $dbtable WHERE UserId = \"$ownerid\" order by UserId asc";
$results = mysql_query($sql);

while($row = mysql_fetch_array($results, MYSQL_ASSOC))
{
    echo $row['displayimage'];

} 
++$i; 
}

[per1os]

<?php

$query1="SELECT UserId, format, artist, name, label FROM books WHERE name = \"$getalbum\" order by name asc"; 
$result1=mysql_query($query1) or DIE(mysql_error());
while ($row = mysql_fetch_array($result1)) {
      $rows[] = $row;
} 

foreach ($rows as $row) {
      $query2="SELECT displayimage FROM $dbtable WHERE UserId = ".$row['UserId']." order by UserId asc";
      $result2=mysql_query($query2) or DIE(mysql_error());
      
      while ($row2 = mysql_fetch_array($result2)) {
            echo "UserId: " . $row['UserId'] . " Display Image: " . $row2['displayimage']; 
       } 
}

?>

 

The reason I put the first one into an array of rows is because I do not think (note this is not fact, just think) that you can do a while mysql_fetch_array inside of a while fetch_mysql_Array due to resource etc. I am not sure but yea you are safer with the way shown above.

 

[H-Fish]

I see where you are coming from here, i figured maybe it was because i couldn't have two while loops inside each other. The code you gave still does not echo $row2['displayimage']; but i see the principle at least so i'll have a look at it.