Preventing Duplicate Entries into MYSQL table

[k3ndr4]

Hello.  I have a form that submits information to a backend SQL database using AJAX/PHP.  The form consists of two dropdown boxes.  The value of each dropdown box and the username of the logged in user are inserted into a SQL table upon submitting the form.  How do I prevent the user from creating duplicate submissions?

 

Here's the lowdown.  It is an NFL pool.  A user logs into the site and then chooses a game week and then a team that they think will win that week.  I need to post the user, the game week, and the team to a table in MYSQL.  Obviously I only want a user to post ONE team for a given week.

 

Here is my PHP code:

 

if(isset($_GET['week']) && isset($_GET['team'])){

 

$week = mysql_real_escape_string($_GET['week']);

$team = mysql_real_escape_string($_GET['team']);

 

$insertPick_sql = "REPLACE INTO picks09 (week, team, user) VALUES('$week', '$team', '$user_name')";

$insertPick= mysql_query($insertPick_sql) or die(mysql_error());

 

echo $user_name . ', your pick has been recorded. <br>' . 'You chose: ' . $team . ' for week ' . $week . '.';

} else {

echo 'Error! You did not make a valid selection!';

}

 

Note:  There will be duplicate weeks - multiple users will submit a team for week 1, etc.

 

There will be duplicate teams - some users may choose the same team for a given week.

 

There will be duplicate users, but NOT for the same week.  Only one team per user for a given week.

 

Any help would be greatly appreciated.

[RussellReal]

INSERT UNIQUE and make one of your unique fields.. E.G. username as a unique key.. and it will throw a mysql error if it tries to add a new row with the same username..

[k3ndr4]

I already have a unique primary key field.  It is pickID and auto increments with every user pick.  Do I not need this field?

[k3ndr4]

Also my problem is that there will be duplicate entries to the table.  There is really nothing unique to go by.  The same user will exist when adding a different week.  The same week will exist will multiple users submitting teams for a given week.  And the same team will exist when multiple users choose the same team for a given week or multiple weeks.

[kratsg]

Then what kind of duplicate entries are you trying to prevent?

[k3ndr4]

I do not want a user to submit more than team for a given week.

[RussellReal]

so make 'week' a unique field..

[k3ndr4]

Sorry, meant "I do not want a user to submit more than ONE team for a given week."

 

I have the insert function working in that it will overwrite a previous submission, but only if all 3 fields being submitted are the same.

 

i.e. Week1, Pittsburgh Steelers, Jane Doe

 

If this combo gets submitted to the table again, it overwrite the previous row and updates the timestamp.

 

I want to overwrite a row when the week and the user match.  So if Jane Doe changes her mind and wants Cleveland Browns, she can resubmit the form and the Pittsburgh STeelers will be overwritten.  I don't necessarily need to alert the user that this is happening since the team picks are displayed on the same page.  They can see them change in real time.

[k3ndr4]

If week is a unique field won't that prevent other users from submitting a team for the same week?

 

If Jane Doe submits a team for week 1, will John Doe be able to submit a team for the same week?

[RussellReal]

nopes.. lol idk you gotta dilemma

[kratsg]

Then just find the number of rows like so:

 

$num_teams = mysql_num_rows(mysql_query("SELECT `id` FROM `table` WHERE `username` = 'some_user' AND [week is this week]"));

 

If there are no rows returned, $num_teams = 0, and you can insert. Since you just care about a single user submitting ANY team for that same week.

 

Then, you need to check for the team (two users can't submit the same team)

 

$num_users = mysql_num_rows(mysql_query("SELECT [etc... check for team matching for same week] "));

 

if $num_users is 0, that means the team isn't submitted for the week

[k3ndr4]

I was able to make the user and week fields unique, so no user could submit more then one team for a given week.  It simply overwrites the former choice.

 

Now that the table is updating correctly, I need to prevent user submissions for a given week based on a deadline (date, time).  I have a table which lists deadline for each week to be submitted.

 

OBviously we don't want people changing their selections once a game begins.

 

Any ideas?

[kratsg]

... Just put a check to see if the deadline has passed or not before updating.. if it has, do NOT let them update.