Page 1 of 1

Need help with php mysql count syntax

Posted: Sun Jun 14, 2009 12:50 am
by xtremetab
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

Code: Select all

 
<?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>'; 
 
?>
 

Re: Need help with php mysql count syntax

Posted: Sun Jun 14, 2009 7:12 am
by miro_igov
After you acquire the result of the count query you should fetch it and assign the fetched line into $count:

Code: Select all

 
//...
$_sql = mysql_query("SELECT DISTINCT(AllSongs) as SongCount FROM allsongs WHERE AllSongID='".$result['AllSongID']."'");
$count = mysql_fetch_assoc($_sql);
 
echo '<tr>';
//...
 

Re: Need help with php mysql count syntax

Posted: Sun Jun 14, 2009 9:08 am
by xtremetab
After I add the code I get errors on line 17 what else could be wrong?

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

Re: Need help with php mysql count syntax

Posted: Sun Jun 14, 2009 10:19 am
by miro_igov
Your query is wrong - there is no funtion in mysql with name DISTINCT(), i guess you meant SELECT COUNT(AllSongs) as SongCount...

Always check if mysql_query() does not return FALSE (mysql syntax error) and the last error by mysql_error().