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

Is it invalid to have more than one answer to a question from a particular user? You might want to consider enforcing that using a unique constraint on your table. — mopoke
– mopoke, Commented Dec 16, 2009 at 0:41
it appears you might have cut off some of you code, could you include the rest if that is the case. — Dostee
– Dostee, Commented Dec 16, 2009 at 0:44
cough. This looks like highly vulnerable to SQL injections. Please escape your variables... — Franz
– Franz, Commented Dec 16, 2009 at 0:49
It is running with magic quotes on, and it is on a local server and no-one wants to inject it. i think i put the code back in. — Logan
– Logan, Commented Dec 16, 2009 at 0:57
For blocks of code, indent with 4 spaces or use the code formatting button on the toolbar. Use backticks only for short inline pieces of code. — philfreo
– philfreo, Commented Dec 16, 2009 at 0:57

cmptrgeekken · Accepted Answer · 2009-12-16 00:47:02Z

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.

answered Dec 16, 2009 at 0:47

cmptrgeekken

8,0923 gold badges32 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeremy Raymond · Accepted Answer · 2009-12-16 01:47:08Z

1

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.

answered Dec 16, 2009 at 1:47

Jeremy Raymond

6,0673 gold badges33 silver badges34 bronze badges

Comments

Brimstedt · Accepted Answer · 2009-12-16 01:18:32Z

0

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

answered Dec 16, 2009 at 1:18

Brimstedt

3,15826 silver badges33 bronze badges

Collectives™ on Stack Overflow

INSERT command inserting duplicates

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related