MySQL UPDATE Resetting values randomly?

I have a highscores table, it seems to be working fine apart from the problem of at random times it seems to be resetting certain users back to 0, this is my query:

$user   = isset($_GET['username']) ? $_GET['username'] : "";
$time   = isset($_GET['time']) ? $_GET['time']  : "";
$videos  = isset($_GET['videos']) ? $_GET['videos'] : "";
$credits  = isset($_GET['credits']) ? $_GET['credits'] : "";


$user  = mysql_real_escape_string($user);
$time  = mysql_real_escape_string($time);
$videos = mysql_real_escape_string($videos);
    $credits = mysql_real_escape_string($credits);
    $secret = mysql_real_escape开发者_如何学JAVA_string($secret);

// Main Query
$retval = mysql_query("
     INSERT INTO
     highscores(Username, Time, Videos, Credits)
     VALUES
     ('$user', '$time', '$videos', '$credits')
     ON DUPLICATE KEY UPDATE
     Time = '$time',
     Videos = '$videos',
     Credits = '$credits'
     ",
     $conn
     );

It updates fine most of the time, can anyone see what the problem is?

I guess you want to update the credit and not zero it.

Say you set $credit to 0 before you execute the query, than the ON DUPLICATE KEY UPDATE part will cause the current user credits to be zeroed. Instead you should do something like this:

<?php
$user = 109;
$time = time();
$videos = 'something';
$credits = 0;
$retval = mysql_query("INSERT INTO
                       highscores
                       (Username, Time, Videos, Credits)
                       VALUES
                       ('$user', '$time', '$videos', '$credits')
                       ON DUPLICATE KEY UPDATE
                       Time = '$time',
                       Videos = '$videos',
                       Credits = Credits + 1", $conn);

I think you are looking for

$query = sprintf("INSERT INTO highscores(Username, Time, Videos, Credits)
    VALUES('%s', '%s', '%s', '%s') 
    ON DUPLICATE KEY UPDATE Time = Time + %2$s, Videos = Videos + %3$s, Credits = Credits + %4$s"     
    mysql_real_escape_string($user), // escape every variable you will be using in
    mysql_real_escape_string($time), // an SQL query to protect yourself against
    mysql_real_escape_string($videos), //  SQL injection or use parametriezed 
    mysql_real_escape_string($credits)); // queries with wrappers such as PDO or MySQLi

$retval = mysql_query($query,$conn);

If a user exists already, this will just add to the current Credits the new value, but it won't change anything else. This seems logical to me. If you also need to increment other columns such as Videos, do the same thing I did for the Credits.

Other have pointed what causes this behaviour. Here's an alternative syntax for the ON DUPLICATE UPDATE

// Main Query
$retval = mysql_query("
     INSERT INTO highscores
       (Username, Time, Videos, Credits)
     VALUES
       ('$user', '$time', '$videos', '$credits')
     ON DUPLICATE KEY UPDATE
       Time = Time + VALUES(Time),
       Videos = Videos + VALUES(Videos),
       Credits = Credits + VALUES(Credits)
     ",
     $conn
     );