I have a database which will store contest participant's votes. I want to allow participants to update their votes if they decide to change their mind. I am using the ON DUPLICATE KEY UPDATE feature of MySQL. I have the username set as the primary key and so if the username is already in the table, then it updates the votes that person had previously entered with the new ones they choose.
The problem I am having is that if a participant already exists in the table and tries to update his/her votes, then the fields that holds the votes becomes blank. The fields do not get updated with the new votes, they get wiped out completely.
Any idea why this is happening?
applicable code is shown below.
$sql = "INSERT INTO $table_name
(username, wk1_home, wk1_2nd, wk1_3rd, wk1_pts) VALUES
('$username', '$_POST[wk1_home]','$_POST[wk1_2nd]','$_POST[wk1_3rd]', '$_POST[wk1_pts]')
ON DUPLICATE KEY UPDATE wk1_home = '$_POST[$wk1_home]', wk1_2nd = '$_POST[$wk1_2nd]', wk1_3rd = '$_POST[$wk1_3rd]'";
$result = @mysql_query($sql, $connection) or die(mysql_error());