0

I am using the below code to insert ratings for specific songs on my application.

I am recording the songID, the rating given to the song and the userID who has voted on the song.

What I want to do is to prevent a user from voting if they have 'already' voted on a specific song. Therefore I need to be able to check if a row exists in the table before insertion.

So... If userID = 1, songID = 1 and rating = 4. This should insert fine.

If subsequently, an insertion attempt is made for userID=1, songID=1, rating=*, it should fail to insert

However if the user is voting on a different song... that should be allowed and the insertion should happen.

Any ideas how I would go about this?

//Add rating to database

if(!empty($_POST['rating']) && isset($_POST))
{
    //make variables safe to insert
  $rating = mysql_real_escape_string($_POST['rating']);
  $songid = mysql_real_escape_string($_POST['song_id']);

    //query to insert data into table
    $sql = "
        INSERT INTO wp_song_ratings
        SET
        songid = '$songid',
        rating = '$rating',
        userid = '$user_id'";
    $result = mysql_query($sql);
    if(!$result)
    {
        echo "Failed to insert record";
    }
    else
    {
        echo "Record inserted successfully";
    }
}
Wouter Dorgelo
  • 11,770
  • 11
  • 62
  • 80
gordyr
  • 6,078
  • 14
  • 65
  • 123
  • Shouldn't users be able to change their mind and rate song differently after a while? Like someone said, Update or replace would make more sense. – AR. Nov 28 '11 at 18:16
  • Actually yes... using a unique index across the two columns wouldn't be suitable you're absolutely right AR. Sorry Aaron, a good simple solution but my poor articulation of the original question means the answer isn't quite right for the application. Thanks for pointing it out AR. – gordyr Nov 28 '11 at 18:20

7 Answers7

2

Add a UNIQUE KEY for userID and songID. If you don't want them to change their rating you shouldn't allow it from the front-end, but still make the check on the backend. It will fail if a UNIQUE KEY is in place.

ALTER TABLE `wp_song_ratings` ADD UNIQUE KEY `user_song_key` (`userID`, `songID`)
Aaron W.
  • 9,254
  • 2
  • 34
  • 45
  • This is the simplest solution and of course it works fine. Would there be any negative performance impact of using this method versus the others mentioned? – gordyr Nov 28 '11 at 18:15
  • Check out http://stackoverflow.com/questions/1823685/when-should-i-use-a-composite-index - that contains some good info on performance and links. – Aaron W. Nov 28 '11 at 18:20
  • Sorry Aaron, As has been pointed out, while this is a simple answer to my question, I forgot to mention that users need to be able to modify their votes. My apologies. – gordyr Nov 28 '11 at 18:21
  • 1
    From all of the DBA's I've worked with they typically don't like programmers to "program in" logic around forcing the db to throw an exception...they would prefer the programmer do their job and NOT cause the db to catch it...but it is good practice to have this kind of backup protection in case someone using a SQL tool tries to insert a duplicate record on their own. – Chris Nov 28 '11 at 18:23
  • The index across the two will help with performance (and there shouldn't be the same userID and songID at any time in the table). Just do a check to see if there's already a record for that user/song then either `INSERT` or `UPDATE`...or just do a `REPLACE` – Aaron W. Nov 28 '11 at 18:25
  • If they need to modify votes then do this answer and use the REPLACE statement instead of INSERT as @Iqez mentioned. – Chris Nov 28 '11 at 18:25
  • Perfect... This combined with the REPLACE query has exactly the effect I am looking for... I'm going to award Aaron with the correct answer as although his answer didn't answer the original question fully (due to my poor desription of the problem) it ultimately led me to solution. The rest will all be done client side. Thanks All, and special props to Iqez! – gordyr Nov 28 '11 at 18:32
1

Sounds like you will need to use a stored procedure in mysql -- more info here http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Mark Shust at M.academy
  • 6,300
  • 4
  • 32
  • 50
1

You can first do a SELECT statement:

if(!empty($_POST['rating']) && isset($_POST))
{
    //make variables safe to insert
  $rating = mysql_real_escape_string($_POST['rating']);
  $songid = mysql_real_escape_string($_POST['song_id']);

  $select_sql = "SELECT COUNT(*) WHERE songid='$songid' AND userid='$user_id'";
  $select_result = mysql_query($select_sql);

  if ( mysql_num_rows($select_result) > 0 )
  {
    /* already voted! */
  }
  else
  {
    //query to insert data into table
    $sql = "
        INSERT INTO wp_song_ratings
        SET
        songid = '$songid',
        rating = '$rating',
        userid = '$user_id'";
    $result = mysql_query($sql);
    if(!$result)
    {
        echo "Failed to insert record";
    }
    else
    {
        echo "Record inserted successfully";
    }
  }
}

Of course, there are also other ways to implement such things (like stored procedures).

ComFreek
  • 29,044
  • 18
  • 104
  • 156
1

Add the following WHERE clause to your sql statement

WHERE NOT EXISTS (SELECT 1 FROM wp_song_ratings WHERE songid= '$songid' and userid = '$user_id')

That works on Oracle and SQL Server...not exactly sure about MySQL

Chris
  • 459
  • 2
  • 3
1

How about to use REPLACE query for it? REPLACE query will replace the old one that has same primary key ( or unique key ) without an error. I think some user may want to update their votes.

lqez
  • 2,898
  • 5
  • 25
  • 55
0

I'd change how you load this view by not allowing 'vote' when the current user has already voted for the song, or if you want to allow them to change the rating, make an update instead of insert for that user on the same song..

MilkyWayJoe
  • 9,082
  • 2
  • 38
  • 53
0

If the table has been defined such that (songid,userid) must be unique for each record (by defining this pair as a key), then the insert will fail if the user tries to re-vote on the same song.

Scott Hunter
  • 48,888
  • 12
  • 60
  • 101