Syntax For Count help

[Wilson]

I have a query that I cannot get to display. I have been through umpteen itirations of If, for and while PHP syntax, but cannot get the count(total) columm to display. As I only get to work on this on Saturdays, I am tired of not being able to figure this out. Can anyone tell me if what code to use to get the thing to display, and if the COUNT [total] requires a special type of code to display a columm that is adding numbers from the distinct artists?

$sql = 'SELECT DISTINCT `artist` , COUNT( `total` ) FROM `songs` GROUP BY `artist` ORDER BY `artist` asc ';
$result = mysql_query($sql, $dbh)
OR die ($sql . mysql_error());
$count= mysql_num_rows($result);
echo ("$count");
 
echo ("<table border=1>");
WHILE ($row = mysql_fetch_array($result))
{
echo ("<tr>");
FOR ($row = $artist[artist];
    $row = $total[total]);
echo ("<td>$row[$artist]</td>");
echo ("<td>$row[$total]</td>");
echo ("</tr>");
echo ("</table>");
}

This is my latest try, but it is apparent I am going backwards. The query returns the proper amount of rows (echo ("$count") and displays pefectly in phpmyadmin, but will not display on the web. When I remove the count function, displaying the table is no problem, but that negates the reason for the query


UPDATE, I got my answer by changing the query to this

$sql = 'SELECT DISTINCT `artist` , COUNT( `total` ) FROM `songs` GROUP BY `artist` ORDER BY `artist` asc ';
$result = mysql_query($sql, $dbh)
OR die ($sql . mysql_error());
$count= mysql_num_rows($result);
echo ("$count");

echo ("<table border=1 width=100%>");
WHILE (LIST($artist, $total) = mysql_fetch_array($result))
{
echo "<tr>\n" .
"<td>$artist</td>\n" .
"<td>$total</td>\n" .
"</tr>\n";
}
echo "</table>";

I used list instead of For

[flann]

count is an aggregate function and cannot be used in this fashion. You'll need to do two separate queries, or use a sub query if available.

[Wilson]

Actually, the script as shown works very well, the 2nd one anyways. I have lots of MIDI music, and this script shows the artists and counts up the files per artist.

The script as show also works just fine in PhpMyAdmin as well.

[flann]

my appologies, i didn't think it would work at all.

[Wilson]

Thats cool man, I only made it work accidently. Its not at all like I know what i am doing.