MySQL/PHP Count

Question

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!

Could you please put the code in, instead of a screenshot of the highlighted code? — Ry-
– Ry- ♦, Commented Nov 18, 2012 at 14:33
Why do you want to see HTML when the question is about MySQL? — Jezen Thomas
– Jezen Thomas, Commented Nov 18, 2012 at 14:35
What is wrong with your current query? What are you trying to achieve? — Mahmoud Gamal
– Mahmoud Gamal, Commented Nov 18, 2012 at 14:37

Martin Vrkljan · Accepted Answer · 2012-11-18 14:51:37Z

1

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>";
}

answered Nov 18, 2012 at 14:51

Martin Vrkljan

86910 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL/PHP Count

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related