Need help with php mysql count syntax

[jefffogel1974]

I am building a web application that list band names in the right column and in the left column the number of songs in my DB for each band. But I cant get it to work correctly. Can anyone help with this and let me know what im doing wrong in the code.

 

URL

http://www.xtremetab.com/tablatureDB/test.php

 

 

Code

 

<?php

 

$data = mysql_query("SELECT DISTINCT AllBands FROM Allsongs WHERE SUBSTR(LCASE(AllBands),1,1) = LCASE('1') ORDER BY AllBands ASC LIMIT 1,50");

 

echo '<table width="100%">';

echo '<tr><td>Artists:</td><td># of songs:</td></tr>';

 

while($result = mysql_fetch_assoc($data)){

 

$_sql = mysql_query("SELECT DISTINCT(AllSongs) as SongCount FROM allsongs WHERE AllSongID='".$result['AllSongID']."'"); $count['SongCount'];

 

 

 

echo '<tr>';

echo '<td>'.$result['AllBands'].'</td>';

echo '<td>[ <strong>'.$count['SongCount'].'</strong> Songs ]</td>';

echo '</tr>';

 

 

}

echo '</table>';

 

?>

 

[pkedpker]

is $count is variable  did you mean  count()...

http://us3.php.net/count

[joel24]

you're not counting the songs at all?

or pulling the results into a variable..?

 

<?php

$data = mysql_query("SELECT DISTINCT AllBands FROM Allsongs WHERE SUBSTR(LCASE(AllBands),1,1) = LCASE('1') ORDER BY AllBands ASC LIMIT 1,50");

echo '<table width="100%">';
echo '<tr><td>Artists:</td><td># of songs:</td></tr>';

while($result = mysql_fetch_assoc($data)){

$_sql = mysql_query("SELECT DISTINCT(AllSongs) as SongCount FROM allsongs WHERE AllSongID='".$result['AllSongID']."'"); 

//count the number of rows returned by the previous SQL query
$songCount = mysql_num_rows($_sql);

echo '<tr>';
echo '<td>'.$result['AllBands'].'</td>';
echo '<td>[ <strong>'.$songCount.'</strong> Songs ]</td>';
echo '</tr>';


}
echo '</table>';

?>

[Mark Baker]

SELECT AllBands,
        COUNT(AllSongs)
  FROM Allsongs 
WHERE SUBSTR(LCASE(AllBands),1,1) = LCASE('1') 
GROUP BY AllBands 
ORDER BY AllBands ASC 

[jefffogel1974]

Hi Joel

 

Once I put in the $songCount = mysql_num_rows($_sql);

on line 17 it returns errors, check out the page below.

 

http://www.xtremetab.com/tablatureDB/test.php

 

Am I missing something?