CODEWITHSUNDEEP
phpmysql
gordyr
gordyr

Reputation: 6276

How to check if a row exists which contains certain content in MySQL

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";
    }
}

Upvotes: 0

Views: 920

Answers (7)

Aaron W.
Aaron W.

Reputation: 9299

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`)

Upvotes: 2

lqez
lqez

Reputation: 3008

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.

Upvotes: 1

Scott Hunter
Scott Hunter

Reputation: 49803

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.

Upvotes: 0

MilkyWayJoe
MilkyWayJoe

Reputation: 9092

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..

Upvotes: 0

Chris
Chris

Reputation: 469

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

Upvotes: 1

ComFreek
ComFreek

Reputation: 29424

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).

Upvotes: 1

Mark Shust at M.academy
Mark Shust at M.academy

Reputation: 6429

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

Upvotes: 1

Related Questions