Well:
- Each artist has multiple albums
- Each album has multiple songs
Hence it makes no sense than to do anything other than:
$Music = array(
'Coldplay' => array(
'A Rush of Blood to the Head' => array(
'Politik',
'In My Place',
'God Put A Smile Upon Your Face',
'The Scientist',
'Clocks',
// ...
),
'Mylo Xyloto' => array(
'Mylo Xyloto',
'Hurts Like Heaven',
'Paradise',
'Charlie Brown',
// ...
)
// ...
),
'Phil Collins' => array(
'Face Value' => array(
'Behind The Lines',
'Droned',
'Hand In Hand',
// ...
)
)
);
So it goes $Music[-artist-][-album-][-song-], i.e. $Music[‘Coldplay’][‘Mylo Xyloto’] contains an array of the songs.
But how do you do this with a database? Well it’s a different set up to the arrays:
CREATE TABLE Artists(
ID int(11) AUTO_INCREMENT,
Name varchar(56),
PRIMARY KEY (ID)
);
CREATE TABLE Albums(
ID int(11) AUTO_INCREMENT,
Name varchar(56),
PRIMARY KEY (ID)
);
CREATE TABLE Songs(
ID int(11) AUTO_INCREMENT,
Artist int(11),
Album int(11),
Name varchar(56),
PRIMARY KEY (ID)
);
INSERT INTO Artists (Name) VALUES
('Coldplay'),
('Phil Collins');
INSERT INTO Albums (Name) VALUES
('A Rush Of Blood To The Head'),
('Mylo Xyloto'),
('Face Value');
INSERT INTO Songs (Artist, Album, Name) VALUES
(1, 1, 'Politik'),
(1, 1, 'In My Place'),
(1, 1, 'God Put A Smile Upon Your Face'),
(1, 1, 'The Scientist'),
(1, 1, 'Clocks'),
(1, 2, 'Mylo Xyloto'),
(1, 2, 'Hurts Like Heaven'),
(1, 2, 'Paradise'),
(1, 2, 'Charlie Brown'),
(2, 3, 'Behind The Lines'),
(2, 3, 'Droned'),
(2, 3, 'Hand In Hand');
The reason I chose not to give albums an artist (and give songs just an album) is that sometimes albums contain multiple artists (compilation albums, for example).
To construct the above array:
$Music = array();
$MusicQuery = mysql_query('SELECT a.Name Artist, al.Name Album, s.Name Song FROM Songs s INNER JOIN Artists a ON s.Artist = a.ID INNER JOIN Albums al ON s.Album = al.ID');
while(list($Artist, $Album, $Song) = mysql_fetch_array($MusicQuery)){
if(!isset($Music[$Artist])){
$Music[$Artist] = array();
}
if(!isset($Music[$Artist][$Album])){
$Music[$Artist][$Album] = array();
}
$Music[$Artist][$Album][] = $Song;
}
var_dump($Music);
