creating lyrics archive 1

[jlynch1]

i am about to experiment with mysql and php project to build a lyrics archive. This archive will have a list of all artist name - album name that there are lyrics for. if there are only lyrics for 1 song then the artist name - song name will be in the menu.

i am thinking of having 3 tables

1.artist table
---------------
art_id [primary]
art_name
art_details
---------------

2.album table
---------------
alb_id [primary]
art_id
alb_name
alb_year
alb_genre
---------------

3.album contents table
-----------------
alb_id [primary_combination]
track_no [primary_combination]
track_title
track_lyrics
-----------------

i would really appreciate any advice as i am fairly new and do not want to have to restart this project when i find inadequacies in my database structure

look forward to your replies

 

[Broccoli2]

At the risk of adding more complexity, can I suggest four tables?

artists
(ArtistID int not null primary key auto_increment,
...
)

albums
(AlbumID int not null primary key auto_increment,
...
)

tracks
(TrackID int not null primary key auto_increment,
AlbumID int not null,
ArtistID int not null,
SongID int not null,
...
)

songs
(SongID int not null primary key auto_increment,
...
)

This would allow for the same song being on different albums or sung by different people with minimum duplication

-Rob

 

[jlynch1]

then in the songs table i would have the following

songs
(SongID int not null primary key auto_increment,
SongName varchar(100)
SongLyrics mediumtext
SongInfo mediumtext
)

and the tracks will be
tracks
(TrackID int not null primary key auto_increment,
AlbumID int not null,
ArtistID int not null,
SongID int not null,
)

what else could i put in the tracks table.
this will be the main reference table that links the other 3 tables together.

The use of an extra table makes the databse larger.
would the queries be slower as a relsult

how would i tell what track a song is on an album.
would i just have to hope that they are entered in order by using the TrackID

 

[Broccoli2]

Your database wouldn't necessarily be any larger, but it would be more flexible. I would recommend adding a field like

TrackNumber int

to the

tracks

table for the number of the track on the album. Then you could do

SELECT
albums.AlbumName
, tracks.TrackNumber
, artists.ArtistName
, songs.SongName
FROM tracks
INNER JOIN songs ON tracks.SongID=songs.SongID
INNER JOIN albums ON tracks.AlbumID=albums.AlbumID
INNER JOIN artists ON tracks.ArtistID=artists.ArtistID
WHERE songs.SongName Like '%Yellow Submarine%'

-Rob

 

[jlynch1]

what is the difference between using an inner join command and saying

SELECT
albums.AlbumName
, tracks.TrackNumber
, artists.ArtistName
, songs.SongName
FROM albums,tracks,artists,songs
WHERE tracks.SongID=songs.SongID
AND tracks.AlbumID=albums.AlbumID
AND tracks.ArtistID=artists.ArtistID
AND songs.SongName Like '%Yellow Submarine%'

is there any need to create an index

 

[Broccoli2]

Functionally the commands produce the same results. However, using the

JOIN

syntax makes things a bit easier to read (in my opinion), as well as being faster to execute by the server. You do not need to index the fields used to join tables, but if you do you may notice a performance increase.

-Rob

 

[jlynch1]

another question
what i want to do is call a page that lists all the
artists and their albums eg.

radiohead - ok computer
etc

when you click on the album it goes to a page that list all the tracks

when i am selecting ArtistName & AlbumName
should i select ArtistID and AlbumID with them
to use as query strings with link or would i be better just to use the ArtistName & AlbumName

<a href=\&quot;$PHP_SELF?a_id=1&alb_id=1\&quot;>bla</a>
or
<a href=\&quot;$PHP_SELF?artist=Radiohead&album=Ok%22Computer\&quot;>

from this them i have a listing
01 - Airbag
02 - Paranoid Android

one last thing would i be better to use a variable called action to determine what to do or should i just to check to see what variables are set and determine from there

 

[Broccoli2]

I would recommend passing the reference to the album (referencing the artist is not really necessary?) as an ID field rather than as a string, because:

* It doesn't sound like you have a high turnover database, so the risk of invalid keys is low
* The query that 'receives' the value will run faster
* You are assured of a unique key
* You don't have to worry too much about escaping strings
* You don't have to worry too much about exceeding your byte limit on urls

On your second question about indicating action, I'm a big fan of self-referencing scripts (I mean a page sending user choices to itself), but:

* If the 'results' script is long, or the 'results' html page is very different from the 'choices' page, you might want to consider splitting into two scripts, one for the choices and one for the results. This bypasses the need for passing an 'action' variable.

* If you do go ahead with a self-referencing script, I prefer passing an 'action' variable as you mention only if you are likely to want to perform different actions. Well formed code should begin by verifying any user input in any case, and you can check here for a results request, ie most of my scripts follow this pattern
- clean and check user input if supplied
-

if ($validuserinput) {

(execute script/display result)

}

- display choices

Hope this helps

-Rob

 

[Broccoli2]

The MySQL site

http://www.mysql.com/doc

has fairly extensive help and you'll find the correct syntax that MySQL servers accept. It's pretty standard SQL apart from a few quirks (the main ones being no UNION command or subselects, but there are workarounds for these).

-Rob

 

[jlynch1]

another question
do i have to use the pre html tag when writing the lyrics of a song to a web page.ie a lot of text with lots of new lines.

btw i have part of it done , the easy parts
listing the artist & albums names
then printing songs on the ablum
then lyrics of song selected.

now on to adding the forms to add lyrics etc

i'll give u the url when i am finished it
seeing that u helped me out so much

do u have an icq a/c ?

 

[Broccoli2]

An alternative to using

<PRE>

would be to replace all the newlines with breaks, using something like

echo ereg_replace(&quot;\n&quot;,&quot;<BR>&quot;,$lyric);

(NB you may need

\r\n

instead, depending on how your lyrics are stored).

I'd be interested to see your project when it's done!

-Rob

 

[petersJazz]

Sounds interesting, a good learning project. But its not legal to put copywrited material on internet.

 

[jlynch1]

its not for the internet , its for at home on my linux box while listening to my mp3s.

i am running apache webserver on my box so it is possible for me to give someone else my ip address while connected to my isp to access it.