Duplicate entry prevention not working..

Dolemite50

Hi,

I have a site that allows members to review songs. I am currently using this method to make sure that there is not already a review for that song from that member:

if (isset($_POST['giveReview'])) {
$sqla = "SELECT COUNT(review) AS count FROM reviews WHERE userid = '$userid' AND songid='$songid'";
$result = mysql_query($sqla); 
$revca= mysql_result($result, 0);
if($revca > 0){
$msg="Sorry, it appears that you have already reviewed this song.";
}
else{
INSERT INTO...
}

If I click the post review button on a song that I have already reviewed; it works fine and tells me that I've already reviewed the song. Somehow there are still duplicate entries showing up. (weird refresh issue?) I currently have a unique field called reviewid that is set to auto_inc. What I would like to do is set some sort of additional unique property so that userid and songid cannot have a duplicate entry. Is this possible?

Thanks.

bbreslauer

Yes, it is possible. If you have phpMyAdmin, then you can just create a UNIQUE key on multiple fields (which is done by going to the table, and then "Create an index on _ columns", which is under the field list to the left-hand side). If you want to know the SQL syntax, here it is:

ALTER TABLE tablename ADD UNIQUE keyname ( field1, field2);

Dolemite50

Thank you very much.

I'm in phpmyadmin and chose create on 2 columns, and am now on this page:
Create a new index
Index name: Do I just put in anything? Like useridsongid?
Index type: (I chose unique)
I then chose songid and userid in the two fields that were "ignore" by default. It then asks for size which I thought was odd. Doesn't it just use the size already defined when you created the columns? I guess I'm not quite sure what it's going to do here so am hoping for some more advice before I continue.

Thanks again from a nervous newb.

bbreslauer

Index name can be anything that you want. Don't worry about the size, just leave it blank.

Dolemite50

Thanks a lot for the followup.

Well, it's become a nightmare. I think I searched every page that Google and PHPB has to offer tonight. When I changed the settings it warned me about having rows with songid and userid matching. I began deleting them manually and then found a query on here that showed I had hundereds to delete. Some have one extra entry and some have 10.

My tables have about 8 columns,.one of them being auto_inc. I attempted to do the temp identical table/Distinct resolution but it didn't work because of the tables not quite being duplicate. If I tried to Distinct select all columns except the ID it gave an error of wrong number of columns.

I then read that you could just create the unique fields and import in and the mysql will give you an error and not allow the dupes,..but when it got to an error it would just quit.

Man,..almost 9am now. I give.

Thanks again.

Dolemite50

Hmmm,

I found several more posts saying that you can just create the unique columns on the temp table and then copy the other table over. They are saying that mysql won't allow you to copy the duplicated rows over which is true; but for me it just gets to the first dupe and exits with an error. Is there a way to get it to continue?

Using phpmyadmin btw.