Building a Dynamic Website with Mysql and PHP

Question

Intro

Hello! I am in the process of building a lyrics website where I will store the lyrics in a MySQL database.

I want to have 2 main tables: 1 for the lyrics which will have id, lyrics title, lyrics text, artist name,and number of views.

The other table will be the artist table with: id, artist name

QUESTIONS:

How can I link the two tables by using the artist name field in both table? I want to display all the artists on my site from the table and see all the lyrics related to that particular artist/
How can I link to a particular record or field in a table?

Please help or if you know of any sites or videos that can help me learn these and other things that may help me in building my site would be appreciated.

Jason · Accepted Answer · 2011-10-24 21:39:01Z

2

Do not store the artist name in both tables. That defeats the purpose of having a separate artist table. Instead store the ArtistID in the Lyrics table, and only store the ArtistName in the Artist table. That way if you, for example, misspell an artist's name, you will only have to update one table, and you will not break the relationship between Lyrics and Artist.

Tables are linked together using Foreign Key relations.

answered Oct 24, 2011 at 21:39

Jason

89.5k15 gold badges137 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marco · Accepted Answer · 2011-10-24 21:40:47Z

0

I answer first question.
You should really change your lyrics table using an id_artist field instead of artist name.
This makes sure you don't duplicate names (so wasting space) and you can't write a wrong name leading you to wrong result during queries.
So you can do

SELECT a.`artist name`, l.title, l.text
FROM artist a INNER JOIN lyrics l
ON a.id = l.id_artist
WHERE a.name = '....'
// or you can use WHERE a.id = ...

answered Oct 24, 2011 at 21:40

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Collectives™ on Stack Overflow

Building a Dynamic Website with Mysql and PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related