Classic ASP Grouping of Database Records

Difficulty Level

grouping
Example, you have a database with your music collection in it. You want to group everything by Artist then break it down by album and list the songs on the album.

This isn’t all that complicated, but it’s something that I struggled with 15+ ago when I was first learning Classic ASP. So I thought I would share with how to do it.

Here’s how we setup the MySQL Database

Table name: Music

Columns:

idMusic int(10) AUTO-INCREMENT
artist varchar(35)
album varchar(35)
song varchar(35)

<%
Set MyConn = Server.CreateObject("ADODB.CONNECTION")
Set RS = Server.CreateObject("ADODB.RECORDSET")

MyConn.Open "music" 'ODBC connection to MySQL Database

SQL = "SELECT * FROM songs ORDER BY artist, album, song" ' SQL statement must use ORDER BY or it wont work correctly

Set RS = MyConn.EXECUTE(SQL)
%>
<html>
<head>
<title>Database Grouping</title>
</head>
<body>
<table>
<%
Do While not RS.EOF
Artist = RS("artist")
Album = RS("album")
if Artist <> ArtistPrev then
%>
<tr>
<td>
<br>
<h3><%= Artist %>:</h3>
</td>
</tr>
<%
End If

if Album <> AlbumPrev then
%>
<tr>
<td><b><%= Album %></b></td>
</tr>
<%

End if
%>
<tr>
<td>
<%= RS.Fields("song") %>
</td>
</tr>

<%
AlbumPrev = Album
ArtistPrev = Artist
RS.MoveNext
Loop
%>
</table>

</body>
</html>