Categories/Sub-categories: music database example

By David J. Lake
2006-02-16

One of the most useful examples of managing data in category, subcategory form is for a music library. Face it, we all love music, want to catalogue what we have and share it with our family, friends and heck, even people we don't know.

First we will set up some basic tables, those can be tweaked later, and fill them with some data. I will then show you two different ways you would do inserts for those tables.

Of course it is also a useful exercise on knowing how to retrieve that data with php as many times these types of databases are managed for personal websites and even of course, radio and music store sites among other uses.

Right, on to the basic stuff. Let's set up three tables. One holding the artist, the next each album by the artist, and lastly the tracks that comprise those albums.

create table artists(
id smallint auto_increment primary key,
artistname varchar(40));

create table albums(
id smallint auto_increment primary key,
artist_id smallint,
albumname varchar(60));

create table tracks(
id smallint auto_increment primary key,
album_id smallint,
trackname varchar(100));

Now to quickly fill those tables with some sample data we can use the following:

insert into artists (artistname) values
('Black Sabbath'),
('Howlin Wolf'),
('Nirvana');

insert into albums (artist_id, albumname)
values
(1,'Paranoid'),
(1,'Sabbath Bloody Sabbath'),
(2,'Chess Blues Classics'),
(3,'Unplugged in New York'),
(3,'Bleach');

insert into tracks (album_id,trackname)
values
(1,'Electric Funeral'),
(1,'Hand of Doom'),
(2,'Killing Yourself To Live'),
(2,'A National Acrobat'),
(3,'Smokestack Lightning'),
(3,'Spoonful'),
(3,'Red Rooster'),
(3,'Dust My Broom'),
(4,'About A Girl'),
(4,'Where Did You Sleep Last Night'),
(5,'Floyd The Barber'),
(5,'Paper Cuts');

Getting basic data from the tables is easy enough. Note the following join is an outer join. This is because we may have an artist listed in the artists table, but perhaps not entered an album in the album table. To eliminate those artists it would be a simple matter of turning left outer join albums into inner join albums

select artistname, albumname from artists left outer join albums on artists.id = albums.artist_id where artistname='Black Sabbath'; 

+---------------+------------------------+
| artistname    | albumname              |
+---------------+------------------------+
| Black Sabbath | Paranoid               |
| Black Sabbath | Sabbath Bloody Sabbath |
+---------------+------------------------+
2 rows in set (0.50 sec)

To get the track listings from each artist and each album, you would just add the tracks table as follows:

select
artistname,
albumname,
trackname
from artists
left outer join albums
on artists.id=albums.artist_id
left outer join tracks
on albums.id = tracks.album_id
order by artistname, albumname, trackname; 

+---------------+------------------------+--------------------------------+
| artistname    | albumname              | trackname                      |
+---------------+------------------------+--------------------------------+
| Black Sabbath | Paranoid               | Electric Funeral               |
| Black Sabbath | Paranoid               | Hand of Doom                   |
| Black Sabbath | Paranoid               | Iron Man                       |
| Black Sabbath | Sabbath Bloody Sabbath | A National Acrobat             |
| Black Sabbath | Sabbath Bloody Sabbath | Killing Yourself To Live       |
| Howlin Wolf   | Chess Blues Classics   | Dust My Broom                  |
| Howlin Wolf   | Chess Blues Classics   | Red Rooster                    |
| Howlin Wolf   | Chess Blues Classics   | Smokestack Lightning           |
| Howlin Wolf   | Chess Blues Classics   | Spoonful                       |
| Nirvana       | Bleach                 | Floyd The Barber               |
| Nirvana       | Bleach                 | Paper Cuts                     |
| Nirvana       | Unplugged in New York  | About A Girl                   |
| Nirvana       | Unplugged in New York  | Where Did You Sleep Last Night |
+---------------+------------------------+--------------------------------+
13 rows in set (0.13 sec)

To narrow down to a specific artist we merely add a where clause as such:

select
artistname,
albumname,
trackname
from artists
left outer join albums
on artists.id=albums.artist_id
left outer join tracks
on albums.id = tracks.album_id
where artistname='Nirvana'
order by artistname, albumname, trackname; 

+------------+-----------------------+--------------------------------+
| artistname | albumname             | trackname                      |
+------------+-----------------------+--------------------------------+
| Nirvana    | Bleach                | Floyd The Barber               |
| Nirvana    | Bleach                | Paper Cuts                     |
| Nirvana    | Unplugged in New York | About A Girl                   |
| Nirvana    | Unplugged in New York | Where Did You Sleep Last Night |
+------------+-----------------------+--------------------------------+
4 rows in set (0.00 sec)

Another useful query would be to find out how many songs are listed for each artist. You can do so with the following query:

select
artistname,
count(trackname) as songtotal
from artists
left outer join albums
on artists.id=albums.artist_id
left outer join tracks
on albums.id = tracks.album_id
group by artistname
order by artistname; 

+---------------+-----------+
| artistname    | songtotal |
+---------------+-----------+
| Black Sabbath |         5 |
| Howlin Wolf   |         4 |
| Nirvana       |         4 |
+---------------+-----------+
3 rows in set (0.00 sec)

To change that slightly, we could ask for only those artists that list more than a certain number of tracks like this:

select
artistname,
count(trackname) as songtotal
from artists
left outer join albums
on artists.id=albums.artist_id
left outer join tracks
on albums.id = tracks.album_id
group by artistname
having songtotal > 4
order by artistname; 

+---------------+-----------+
| artistname    | songtotal |
+---------------+-----------+
| Black Sabbath |         5 |
+---------------+-----------+
1 row in set (0.00 sec)

Finally, if you want to print out a category only a single time, with all related items underneath it use the php code below. It will give you something similar to:

Black Sabbath
   Paranoid
   Volume IV
   Sabbath Bloody Sabbath

Now on to the php code. This is a basic select that we have used above. You may want to use a table or CSS to manage the layout of course.

Central to the output is setting up a variable for each artist, album and track. We grab all the information from the database at once and then loop through those results. Often I see folks do a query on the database for the categories. They then loop through the categories and do a separate sql query to grab out the sub-categories for each category. This not only necessitates multiple connections to the database, it also isn't as efficient as having the database do the work all at once.

Each row would consist of the artist, album and track. We then compare them to the variable we have set for each of them. Initially those variables are set as empty strings. When a new artist comes up, we print it out and then set the value of the $artist variable to the name of that artist. Each subsequent row will ignore the artist until a new artist is found. The same goes for the $album variable, printing out each time a new album is found, ignoring that data when it is the same.

  1. error_reporting(E_ALL);
  2.  
  3. mysql_connect ( 'localhost', 'xxxxxx', '******' );
  4. mysql_select_db ( 'music' );
  5.  
  6. $artist = "";
  7. $album = "";
  8. $track = "";
  9.  
  10. $sql = ("select
  11. artistname,
  12. albumname,
  13. trackname
  14. from artists
  15. left outer join albums
  16. on artists.id=albums.artist_id
  17. left outer join tracks
  18. on albums.id = tracks.album_id
  19. order by artistname, albumname, trackname");
  20.  
  21. $output = mysql_query($sql);
  22. $count = mysql_num_rows($output);
  23.  
  24. if ($count == 0) {
  25. echo "There are no artists available to show";
  26. } else {
  27.  
  28. while ($row = mysql_fetch_array($output, MYSQL_ASSOC)) {
  29.  
  30. if ($row['artistname'] != $artist) {
  31. // The artist is new so now change the value.
  32. $artist = $row['artistname'];
  33. $album = $row['albumname'];
  34. $track = $row['trackname'];
  35.  
  36. echo " $artist";
  37. echo " Album: $row[albumname]";
  38. echo " Track: $row[trackname]";
  39.  
  40. } else {
  41.  
  42. if ($row['albumname'] != $album) {
  43. // the album is new so now change the album value.
  44. $album = $row['albumname'];
  45. $track = $row['trackname'];
  46. echo " Album: $album";
  47. echo " Track: $row[trackname]";
  48. } else {
  49. echo " Track: $row[trackname]";
  50. }
  51. }
  52. }
  53. }
  54.  
  55. mysql_free_result($output);