Forum Home
Press F1
 
Thread ID: 121545 2011-10-30 22:54:00 Database table layout suggestions? Chilling_Silence (9) Press F1
Post ID Timestamp Content User
1241011 2011-11-01 01:46:00 Info you need for ccli is here www.ccli.co.nz

Good thinking about song title and people knowing the same song by different names as its pretty common. Also bear in mind that some songs have the same title so having an artist table could be useful then as well. I once learnt the wrong song for Sunday morning because there were 2 songs called the same things and the worship leader assumed everyone would know which one she was talking about.

Anyway it may be best to wait for Erayd and see what he has to say as all of this stuff Im pulling from my days at uni which were sometime ago and I don't want to be pointing you in the wrong direction and it seems he does this stuff more often than I do. From what I can tell though you are on the right track. 		Barnabas (4562)
1241012 2011-11-01 01:49:00 Ah OK sweeeeeet, might add an extra field or two then to that table :)

I know what you mean, been there in that situation a couple of times ;) 		Chilling_Silence (9)
1241013 2011-11-01 06:16:00 OK so looking at using a single field for the lyrics and thinking of just doing away with the 'lyricsid' part altogether which means I'm probably able to get away with just two tables now, which is cool .



$result = mysql_query( " SELECT * FROM lyrics WHERE songid='$songid' AND lyricsid='$lyricsid' " );
while($row = mysql_fetch_array($result))
{
echo " SID: " . stripslashes($row['songid']) . " <br />LID: " . stripslashes($row['lyricsid']) . " <br /> " ;
$brokenlyrics = explode( " \n\r " , $row['lyrics']);
}
echo " <hr /><pre> " ;
echo $brokenlyrics[0] . " \n " ;
echo $brokenlyrics[1] . " \n " ;
echo $brokenlyrics[2] . " \n " ;
echo $brokenlyrics[3] . " \n " ;
echo $brokenlyrics[4] . " \n " ;
echo $brokenlyrics[5] . " \n " ;
echo " </pre><hr /> " ;


This means if I take something, such as the same lyrics from before:


Verse 1:
This is my prayer in the desert
And all that's within me feels dry
This is my prayer in my hunger and need
My God is the God who provides

Verse 2:
And this is my prayer in the fire
In weakness or trial or pain
There is a faith proved
Of more worth than gold
So refine me Lord through the flames

Means that the break between the choruses is now split . So, I guess what I've then got to store instead of a lyricsid is the applicable part of the array, for example I need to store " $brokenlyrics[1] " when I want it to display the second paragraph .
I think things are coming together . . . :D

I've got some code which is refreshing the page every 2 seconds, which is crude but works for now :)

Might be worth popping it up on a Google Code project when I'm a bit further completed :D 		Chilling_Silence (9)
1241014 2011-11-01 07:50:00 I hope you are doing something about the massive SQL-injection hole (it appears) you've got in that PHP code before you release it... not that it's going to be a particularly big target for attacks but still. somebody (208)
1241015 2011-11-01 09:12:00 Yeah I just copied / pasted ^^ from notepad, not from the actual database...
I'm not sure if this is enough, but I'm doing:


$songid = $_POST[songid];
$lyrics = addslashes($_POST[lyrics]);
$sql="INSERT INTO lyrics (songid, lyrics) VALUES('$songid','$lyrics')"; 		Chilling_Silence (9)
1241016 2011-11-01 09:52:00 mikebartnz, if you have a better way of storing all the lyrics, I'm all ears, but this certainly seems like the most ideal way of doing things. I want the ability to cram a whole lot of text in there, or have it spaced apart, so what I may end up doing for each "songid" is have a "starttags" and a "finishtags" column that I can put in the text height tags etc.
As Erayd said the only real reason for having the lyrics in a different table is to allow a text search. If you don't want that there is no point. 		mikebartnz (21)
1241017 2011-11-01 19:25:00 Can you explain a little about that? For example how it would differ? I don't think I understand ... Chilling_Silence (9)
1 2 3