CODEWITHSUNDEEP
phpmysql
Jack D
Jack D

Reputation: 29

MySQL/PHP Count

I have 2 MySQL tables, one for storing albums and the other for songs. I am displaying a list of albums in a table, and I want to be able to add a column called songs to display the number of songs in this album. The way I have it now just messes up my table:

Thanks for everyone's help!

Upvotes: 0

Views: 105

Answers (1)

Martin Vrkljan
Martin Vrkljan

Reputation: 879

Assuming that "playlist" is what you consider an album and the first while loop iterates on playlists, I'd rewrite your code like this:

while($row = mysql_fetch_array($rs)) {
    echo "<tr>\n";
    echo "<td>".$row['playlist_id']."</td>";

    // Assuming playlist_id is an integer value in your database
    $query = "
        SELECT Playlist_id, COUNT(Playlist_id) AS songCount
        FROM ws_music 
        WHERE Playlist_id = ". intval ($row['playlist_id']) ."
        GROUP BY Playlist_id 
    ";
    $result = mysql_query($query) or die(mysql_error());
    // No need for the second while loop
    $row2 = mysql_fetch_array($result);
    echo "<td>There are ". $row2['songCount'] ." ". $row2['Playlist_id'] ." song.</td>";
    echo "</tr>";
}

Upvotes: 1

Related Questions