Can my Database check for duplicates?

[aaricwon]

I am helping with a website for the Mixed Martial Arts event TUFF N UFF.

 

We have an online registration form here: http://www.bjjnews.org/TUF/register

 

 

Is there a way I can have it check for duplicate entries and not allow them?

[rhodesa]

Of course. This would be my thought process:

 

Step 1: Define what a "duplicate is"

• To be able to check for a duplicate we need to first decide what constitutes as a duplicate. Looking at your form, I would think email or phonen number would be the best method. It's not feasible to only consider a duplicate to be exactly the same data on EVERY field because they are bound to do something different (an extra space, all lower case, etc).

 

Step 2: Check for duplicate on submit

• Now that we know what we are looking for, it's time to write some code. We need to make some modifications to the script that INSERTs the submitted information into your database. Before the INSERT, you will want to use a SELECT statement to find records that match the submitted data. So something like:
  

<?php
  $email = mysql_real_escape_strint($_POST['email']); //Email that was just submitted
  $result = mysql_query("SELECT * FROM `fighters` WHERE `email` = '{$email}'")
    or die("Query Failed");
  if(mysql_num_rows($result))
    die("Email already exists");
?>

 

Hope that helps

[PFMaBiSmAd]

If you define a column as a UNIQUE index, an INSERT query with a duplicate value will error instead of inserting. Your code would need to have proper error checking logic (most simple beginner php code does not) to be able to make use of this feature.

[fnairb]

rhodesa is spot on.  I would make one additional suggestion.  Unique Indexes are your friend.  Set up a unique index that matches the definition you come to from Step 1.  Doing so will guaranteed enforced uniqueness.  However, you can't create the unique index unless you either have no data or it is currently unique so in this case it may require you to clean up your table first.

 

[rhodesa]

My 2 cents on Unique Indexes: While I do use them for some cases, I tend not to use them on "people" type tables because there is one specific case when there could be a duplicate record (for me at least). I usually have the ability to "Remove" a user in my code, but the user is never actually removed, I just add the date to a datetimeRemoved field and then filter on that when SELECTing users. That way if I ever wanted to restore the user I could. This means that after a user is "removed" a new one could be created with the same 'unique' field.

 

But that's just me