INSERT command inserting duplicates

Question

I am writing a quiz website.
What should happen is:

• A page has a question on it and four buttons or a textbox.
• When you click a button, it calls itself with the answer number in the address like: ?q=[question number]&a=[answer].
• If the question uses a textbox it POSTs the answer.
• This code should then detect that something has been sent to it and write that to a database.
• The user id is stored in a cookie.
• There is also a key column in the database. the idea is that it stores all answers a user has submitted over time and users can change their answers.
  

  <?PHP
      mysql_connect("------", "------", "------") or die();
      mysql_select_db("------") or die();
      $q=$_GET['q'];
      if(isset($_GET['a'])){
       $a=$_GET['a'];
      } else {
       $a=$_POST['longanswer'];
      }  
      if(isset($a)){
       $u=$_COOKIE['id'];
       if($qust['atype']==1){
        mysql_query("INSERT INTO answers (userid ,answer ,qid) VALUES ($u, $a, $q);");
       } else {
        mysql_query("INSERT INTO answers (userid ,answer ,qid) VALUES ($u, '$a', $q);");
       }
      }
  ?>
  

I don't think it should matter, but later on on the code, it queries the database with the SELECT command.
When i run this code, it seems to enter 2 or 3 entries to the database. The trend seems to be that when i run the code it enters the previous answer, followed by the new answer. Any help would be greatly appreciated. Thanks,

Logan

Answer 1

It seems like what you want to do is to allow only one answer per question per user. If that's the case, you'll want a UNIQUE constraint on your table on userid and qid:

ALTER TABLE answers ADD UNIQUE(userid,qid);

This will result in an error when you try to insert a row with the same userid and qid. If you want to allow users to change their error, you can change your INSERT query to:

INSERT INTO answers (userid ,answer ,qid) VALUES ($uid, '$answer', $qid) ON DUPLICATE KEY UPDATE answer='$answer'

This will prevent multiple records from showing up in the database, but multiple INSERTs might still be called from your PHP code. To fix that, we'd have to see some more of your code.

Answer 2

The unique constraint mentioned by cmptrgeekken should definately be added if you only allow one answer per user, but then you must also handle the primary key violation if it occurs: Inform the user it has already replied OR replace the previous value, depending of how you want the site to work.

Also, Is for some reason the request triggered more than once? Maybe by javascript, or some other logic of yours? If duplicate answers appears when you only click once, this seems to be the case.

/B

Answer 3

Another option would be to first try to retrieve the data you're about to enter into the database. If you find it, it's already there, so don't add it again. The suggestions for using constraints are sound but if the data you're trying to prevent duplicates of isn't easily added to the constraints or the data you don't want duplicates of is not exactly the same data (say just similar data) then this is another option.