Member Avatar for jKidz

Hi Friends,
I am developing a Music download system.
I thought to have two database tables.
One for songs and other one is for artists because some songs have two or more artists.
Then I created two tables call songs and songartists.
Following are the structures of that tables.

songs

Field   Type        Null    Default
id      int(11)     No  
name    varchar(50) No  
size    int(10)     No  
lyrics  varchar(35) No  
music   varchar(35) No  
mastered    int(35) No  
link    varchar(355)No  
added   timestamp   No  CURRENT_TIMESTAMP

songartists

Field   Type         Null   Default
id      int(11)       No    
sid     int(10)       No    
artist  varchar(355)  No

Now I want to display last 5 songs in the home page like following (song name - artist).

Pure Love - ARASH feat Helena
Hotel Califonia - The Eagles

I know I should use JOIN function. But my dear friends I have no idea how to use join function.
Can someone explain me with my database structure it will be great for me.

  • 3 Contributors
  • 2 Replies
  • 217 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by ms061210

Recommended Answers

All 2 Replies

Member Avatar for cereal

Look at group_concat() in MySQL:

To create a query like this:

select s.name, group_concat(sa.artist order by sa.artist) as artists from songs s, songartists sa where s.id = sa.sid group by sa.sid;

But I would create an artists table, so you don't have to repeat the same artist each time you insert a new song, the new schema would look like this:

create table songs(
  id int unsigned not null auto_increment primary key,
  name varchar(50) not null,
  size int unsigned not null default 0,
  lyrics varchar(35) not null default '',
  music varchar(35) not null default '',
  mastered varchar(35) not null default '',
  link varchar(255) not null default '',
  added timestamp not null default current_timestamp
) engine = myisam;

create table artists(
  id int unsigned not null auto_increment primary key,
  name varchar(255) not null
) engine = myisam;

create table songartists(
  song_id int unsigned not null,
  artist_id int unsigned not null
) engine = myisam;

And the query would be:

select s.name, group_concat(a.name order by a.name) as artists from songs s, artists as a, songartists sa where s.id = sa.song_id and a.id = sa.artist_id group by sa.song_id;

Here there are two examples, the first is based on your two tables, the second on my suggested method:

Bye!

Member Avatar for ms061210

Try to use this ismple query:

SELECT songs.name, songartists.artist
FROM songs
INNER JOIN songartist
ON  songartist.id = songs.id
ORDER BY songs.name Desc
LIMIT 5
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.