[SOLVED] Best Way To Track Forever

[jfarthing]

Let's say I have a website where users post their music and other users rate it.  What would be the most logical way to allow a user to only rate a particular song once FOREVER also storing what they rated that song as?  the songs are stored in one mysql database, and the users in another...I'm probably thinking too hard about this!

[phpQuestioner]

there is a couple of ways you can do this; but there is not a 100% fool proof method to do this, but these are common ways.

 

 

1.) Use Cookies Or Set Sessions (can be erased)

2.) Get IP Address (can be faked)

[jfarthing]

Again, these are registered users, so records of these users are recorded per username.  Only one vote per username, how can I keep track of that including what song they voted on and what they voted it considering they could vote on 1000's of songs...?

[phpQuestioner]

Just have you script to insert a value in a field in your database in the row of the usersname once they have voted. Then before processing a vote; validate the usersname who is voting too see if the database field has a specific value in it. If it does; then don't let them vote again and if it does; let them vote. you could dynamically change the value to be inserted into the db field and to be validated for each poll you have.

[jfarthing]

I was thinking about creating a field for the user who is doing the voting within their row, adding a field called votes, and make it a comma delimited list of every song ID they have voted for and then checking if a song ID is within that list....is this logical and/or efficient?

[jfarthing]

I was just thinkin, if I implemented the above stated, how would I also record what they rated that song within that structure??

[~n[EO]n~]

The best thing I see here is to create a separate table with UserID, SongId and Vote. So everytime user votes for a song their id and songid will be saved in that table with Vote field set to YES (or whatever you want).

[jfarthing]

that could end up being a MASSIVE table.  Could I do as I was saying, but further delimit the array such as

 

10198:5, 10283:2, 1203:4

 

where the first part is the song id, proceeding the colon is the vote value proceeding the comma is the next song/vote combo etc etc...?  If so, how would I retrieve a specific song/vote out of that?

[phpQuestioner]

you probably could do it that way, but an easier way; if your going that route would be to create a field for the vote and a field for the song numbers. you may have to create a complex array and explode each comma if you want to validate if user has voted or not, but then again you might be able to explode each comma in a simple array for each database field (vote and song id) and use a basic if else condition to validate.

[jfarthing]

So create a seperate table just for voting and basically have 3 fields, songID, voterID, voteValue.  Then on the voting page, to validate, query that table something like:

$voteQuery = mysql_query("SELECT * FROM votes WHERE voterID = '$usrID' AND songID = '$songID'") or die (mysql_error());

if (mysql_num_rows($voteQuery) > 0) {
  echo "You have already voted for this song!";
} else {
  /***Show Voting***/
}

 

Damn, just thinking though, each user (could be thousands) could vote for 1000's of songs....again a MASSIVE table...will this be a potiential problem??

[phpQuestioner]

you could but then you would have to use a JOIN

 

what I was saying was in the table where you username and password is; create two new fields right beside your username and password (vote, song id/number). then use commas or something to seperate each vote and each song id/number. either way; this is going to take up some database space - the way your wanting to do this.

 

you could always do like I said before, but that would be a good method for maybe only a few votes and maybe only a few songs; not a massive amount of vote and a massive amount of songs.

 

http://www.phpfreaks.com/forums/index.php/topic,170311.msg752487.html#msg752487

[jfarthing]

Would it be easier and more efficient to utilize an external text-based file via fopen?

[phpQuestioner]

I personally would rather use a database; I really don't like text files for storing data accurately. I have stored data in a text file in the past and some how it got alternated in a way it shouldn't have. I just think database data is allot more secure then text file data, but hey - that's just me. Which ever you think works the best for you or creates less a file size for your server/host; then go for it.

[jfarthing]

I think I like your last idea the best, that sounds logical now that I think about it.  It will be stored so that once those two fields are imported into a php array, each voteid will have the same corresponding array key for vote value.  very logical...