query from the result of another query

[Anon]

Is the a way to do this

$Query = "SELECT * FROM artist ORDER BY artist.name ";
$result = mysql_query($Query);
echo "<TABLE border=2>\n";

do {
$Query2 = "SELECT * FROM album WHERE artist_id = "$row["artist_id"]" ";
$result2 = mysql_query($Query2);
print("<TR><TD>".$row["artist_name"]."</TD><TD>\n");
do{
print($row["album_name"]);
} while ($row = mysql_fetch_array($result2));
echo "</TD>\n";
}while ($row = mysql_fetch_array($result));
echo "</TR>\n";
echo "</TABLE>\n";
?>

What I'm trying to do exactly is to display the artist and next to it all of his alum on the second column I want to continue the process

artist1

album1
album2
album3

artist2
album1
album2
album3

I hope you get the idea.

Please help, I am new to PHP and mysql.

[Anon]

<?
//Include all of your database connection stuff here

$query = "SELECT * FROM artist ORDER BY artist.name ";
$result = mysql_query($query);
echo "<TABLE border=2>\n";

while ($row = mysql_fetch_array($result)) {
print("<TR><TD>".$row["artist_name"]."</TD><TD>\n");

$query2 = "SELECT * FROM album WHERE artist_id = " . $row["artist_id"];
$result2 = mysql_query($query2);

while ($row2 = mysql_fetch_array($result2)) {
print("<TD>".$row2["album_name"]."</TD>\n");

}
echo "</TR>\n";
}
echo "</TABLE>\n";
?>

A couple of things in your code. I personally like while loops over do while loops because with a database pull there is not always the garentee of a result. If you don't have a result then you won't execute the code in the while loop. The second comment is that it is safer to not just use $row for both of the results. You might want to consider using $row and $row2 to keep the data from getting mixed up. I am not sure if this is the formate you wanted your table in but I will let you play with that. This code should return the following result.

|---------------------------|
| artist1 | album1 | album2 |
| artist2 | album1 | album2 |
| artist3 | album1 | album2 |
|---------------------------|

Hope that helps.

Daniel Ice
http://www.coderguy.com