MySQL: How to store/retrieve artist information?

Question

It's very confusing; it seems like I'll need to have at least one many-to-many relationship.

1. A track might be sung by 2+ artist - how can I store that in the database?
2. While showing that track, I want link to each artist so that when users click the track it opens that artist's profile page.

Can anyone provide me with some assistance in designing a class or classes to accomplish this?

Answer 1

Any many-to-many relationship requires three tables. For your example:

Song<br/> 
Name  SongID

Artist <br/>
Name  ArtistID

ArtistsInSongs <br/>
ArtistID SongID

I'll let you figure out the rest.

Answer 2

Try something like that

tblSongs
   SongId
   Title
   YearProduced
   etc.

tblArtist
   ArtistId
   Name
   ProfilePage
   etc..

tblSongArtists
    SongId
    ArtistId

Then your queries could look something like

SELECT Title, YearProduced, Name, ProfilePage
FROM tblSongs  S
LEFT OUTER JOIN tblSongArtists SA ON SA.SongId = S.SongId
LEFT OUTER JOIN tblArtists A ON A.ArtistId = SA.ArtistId
WHERE Title = 'I got the blues'  -- ....
ORDER BY SongId  -- or Song Title  (*)

(*) order by clause is in case the search criteria produces more than one song, and if you wish to keep the artists for a given song in sequence.
Such a query produces multiple rows for a song with several artists, one row per artists, the values of the columns from tblSongs being repeated.

Answer 3

public static function find_artist_on($track_id=0) {
    global $database;
    $sql = "SELECT * FROM " . self::$table_name ." s " ;
    $sql .= "LEFT OUTER JOIN trackartist TA ON TA.track_id =s.track_id";
    $sql .= "LEFT OUTER JOIN artist A ON A.artist_id =TA.artist_id";
    $sql .= " WHERE s.artist_id=" .$database->escape_value($track_id);
    $sql .= " ORDER BY artist_id ASC";
    return self::find_by_sql($sql);
    }