How to count rows within results?

[wxdqz]

Hi, I have a mysql/php database with lyrics.

For example: a table named A, with the fields Artist, Album, Song, and Lyrics.


I can display results like this:

Artist albums: (3 albums = 28 songs)
Album1
Album2
Album3

I am trying to get this:

Artist albums: (3 albums = 28 songs)
Album1 (14 songs on this album)
Album2 (2 songs on this album)
Album3 (12 songs on this album)

but now I can only get this:

Artist albums: (3 albums = 28 songs)
Album1 (28 songs on this album)
Album2 (28 songs on this album)
Album3 (28 songs on this album)


The code I use to get those results is:


$result = mysql_query("SELECT DISTINCT Artist,Album FROM $Letter WHERE Artist = $Artist order by Album",$db);

$AlbumCount = mysql_num_rows($result);

$result2 = mysql_query("SELECT DISTINCT Artist,Album,Song FROM $Letter WHERE Artist = $Artist order by Album",$db);

$SongCount = mysql_num_rows($result2);


$result3 = mysql_query("SELECT COUNT(Song) as Number FROM $Letter WHERE Artist = $Artist");

$NewCount = mysql_result ($result3,0,"Number");


echo "

<table><tr><td>
$Artist albums:
($AlbumCount albums = $SongCount songs)</td></tr>\n";

while ($myrow = mysql_fetch_array($result)) {

printf("<tr><td>%s

($NewCount songs on this album)

</td></tr>\n", $myrow[Album]);

}

echo "</table>\n";


Any help is greatly appreciated...