Add to a record if record exists, otherwise create a record

Question

I’ve created a little weekly trivia game for my website. Basically its five questions, then at the end the user can add their score to a scoreboard.

The problem is that I want the scores to carry from week to week and cumulate. So let’s say you got 4 points one week, then 5 points the next. I want the scoreboard to reflect you have 9 points.

So I created a small form with an i nvisible field that has the users score, a field for the username, and a field for the e-mail address. Next week, when the user takes the quiz again, I want their score to be updated if the username and e-mail match a record in the database. If no record does match, I want an entry to be created.

Here’s the script I came up with, however, it doesn’t work (which doesn’t surprise me, I’m pretty new to PHP/MySQL)

$name = $_POST['name']; //The Username
$score = $_POST['submitscore']; //The users score (0-5)
$email = $_POST['email'];//Users email address
$date = date("F j, Y, g:i a");//The date and time

if($name != '') {
    $qry = "SELECT * FROM scoreboard WHERE name='$name'";
    $result = mysql_query($qry);
    if($result) {
        if(mysql_num_rows($result) > 0) {
        $sum = ($row['SUM(score)']+$score);
        "UPDATE scoreboard SET score = '$sum' WHERE name = '$name'";
        }    
        else    
    $q = mysql_query("INSERT INTO scoreboard (`name`, `email`, `date`, `score`) VALUES ('$name', '$email', '$date', '$score');");
        @mysql_free_result($result);
    }
    else {
        die("Query failed");
    }
}

My table scoreboard looks like this

id........name........email...........date...........score

1........J.Doe.....j.doe@xyz.com.....7/27/11.........4

Two things: Hello [Bobby Tables](http://bobby-tables.com), and [INSERT ... ON DUPLICATE KEY](http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html) — Marc B, Jul 28 '11 at 15:28
Is it giving you an error? if so, post it please. Or does it always do the later and insert the data? — grep, Jul 28 '11 at 15:29

cwallenpoole · Accepted Answer · 2011-07-28T15:56:44.783

4

You're looking for INSERT... ON DUPLICATE KEY syntax

"INSERT INTO scoreboard (`name`, `email`, `date`, `score`) ".
   " VALUES ('$name', '$email', '$date', '$score') ".
"ON DUPLICATE KEY UPDATE `score` = $sum";

Aside:

Use mysql_real_escape_string!

$name = mysql_real_escape_string( $_POST['name'] );
$score = mysql_real_escape_string( $_POST['submitscore'] );
$email = mysql_real_escape_string( $_POST['email'] );
$date = date("F j, Y, g:i a");//The date and time

EDIT

First, this doesn't really work unless you have a column SUM(SCORE):

$sum = ($row['SUM(score)']+$score);

If you want the sum of a column, you need to put that in the MySQL query directly. If you just want the score for that row, however, you can use $row['score']. If you need to add to an existing score you don't need to select for the value (thanks to a1ex07 for pointing this out)

ON DUPLICATE KEY UPDATE `score` = $score + score

edited Jul 28 '11 at 15:56

answered Jul 28 '11 at 15:28

cwallenpoole

79,954
26
128
166

1

He also has to add a unique index on `name` . And I believe it should be `..... ON DUPLICATE KEY UPDATE score=score+$score`; – a1ex07 Jul 28 '11 at 15:32
A duplicate key means that the original unique key has already been found. That is the row which will be updated. His original query has a direct assignment of score to the value $sum. – cwallenpoole Jul 28 '11 at 15:44
1

I'd say he doesn't need `SELECT .. ` at all( and $sum assignment). – a1ex07 Jul 28 '11 at 15:49
Is there a way to make `ON DUPLICATE KEY UPDATE score = $score + score` only update rows where the email address and name are duplicates (so it doesn't take the score column into account)? – Andrew De Forest Jul 28 '11 at 16:14
@Andrew De Forest Well, what do you have your unique constraints on? – cwallenpoole Jul 28 '11 at 16:44
@cwallenpoole I made the name column and the e-mail column unique, however I ran a test where the users had the same name and different e-mails, but it still just kept adding to the one record. – Andrew De Forest Jul 28 '11 at 16:53
Well, the update clause should support additional where conditions. – cwallenpoole Jul 28 '11 at 17:08
Well perhaps something is wrong with my syntax? `ON DUPLICATE KEY UPDATE score = $score + score WHERE email='$email'` ? – Andrew De Forest Jul 28 '11 at 17:22
How's this: `ON DUPLICATE KEY UPDATE score = IF( email='$email', $score + score, SCORE )` – cwallenpoole Jul 28 '11 at 17:30
Hmm still nothing `"INSERT INTO scoreboard (name, email, date, score) VALUES ('$name', '$email', '$date', '$score') ON DUPLICATE KEY UPDATE score = IF( email='$email', $score + score, SCORE )"` – Andrew De Forest Jul 28 '11 at 17:54
Ok, I think I see what's going on. Do you have a unique key on *both* name and email, not individually? `ALTER TABLE \`scoreboard\` ADD UNIQUE ( \`name\` , \`email\` );` – cwallenpoole Jul 28 '11 at 18:45

score 1 · Answer 2 · edited May 23 '17 at 12:26

1

This line is incorrect:

$sum = ($row['SUM(score)']+$score);

You probably want to replace it by:

$sum = ($row['score']+$score);

As you are new to PHP/MySQL I recommend you to read about MySQL Injections as your queries contain potential risks.

edited May 23 '17 at 12:26

Community

1
1

answered Jul 28 '11 at 15:29

FMCorz

2,586
1
21
18

score 0 · Answer 3 · answered Jul 28 '11 at 15:38

I'd have a database table to hold quizzes; a database table for members; and a database table that contains foreign keys to both tables along with a score so only one record can be created for each member and each quiz.

I'd also save the score in a session when the user finishes the quiz so the user can't then just submit any old score to your database; the score entered is the score your application generated.

This way, you can then just query SUM(score) of a member based on that member's ID.

Add to a record if record exists, otherwise create a record

3 Answers3

EDIT