Prevent multiple data entries in MySQL

Question

So here is the deal. I have looked around everywhere, and all other techniques relate to refreshing the browser, and methods to prevent the php page from resubmitting the post data. I am new to this (obviously :p) But anyways, my questions I believe is simple. I just want a method, possibly an if else statement that would check the post data entries, and if there is a match already in my table, than do not execute the query. I am not worried about querying all of the results of the table, as I only suspect this table will ever have 50-60 entries.

Here is the php page that handles the form submission:

$firstName = $_POST['firstName'];
$lastName = $_POST['lastName'];
$email = $_POST['email'];
$city = $_POST['city'];
$state = $_POST['state'];

$submitDate = date("Y-m-d");

mysql_connect ("localhost", "abc", "123") or die ('Error: ' . mysql_error());
mysql_select_db ("members");

$query = "INSERT INTO persons (ID, firstName, lastName, email, city, state, submitDate)VALUES (
'NULL',
'".$firstName."',
'".$lastName."',
'".$email."',
'".$city."',
'".$state."',
'".$submitDate."'
)";

mysql_query($query) or die ('Error Updating database');

echo "Database Updated With: " .$firstName ." " .$lastName ." " .$email ." " .$city ." " .$state; 

mysql_close($con);

Sorry, cant ever seem to get my php to format correctly with those code braces. Anyways. just to re-iterate, looking for a way to maybe based on the first and last name. if those already exist, then do not allow the submission of the data. I have tried a few if then statements but i do not think I am getting the concept down of comparing the result to my query. I hope this all makes sense!!!

Answer 1

I would suggest adding a UNIQUE index on the columns you want to have unique.

Answer 2

You can just use INSERT IGNORE INTO ... and let MySQL handle it.

$query = "INSERT IGNORE INTO persons (ID, firstName, lastName, email, city, state, submitDate) VALUES (
'NULL',
'".$firstName."',
'".$lastName."',
'".$email."',
'".$city."',
'".$state."',
'".$submitDate."'
)";

Answer 3

Is your problem only that refreshing the page resends the POST data? The pretty much standard way to prevent that is to redirect the browser after having processed the form data, like so:

header('Location: ' . $_SERVER['PHP_SELF']);

Keep in mind, changing headers has to be done before any output is sent to the browser, so this should be above your doctype, and be sure there is no white space before either.

Answer 4

One way of doing this is to make sure your table has appropriate primary keys set (firstname and lastname at least), and then just trying the insert and seeing whether it fails on duplicate. You can check the error message using the mysql_error() function for this purpose.

Answer 5

You can do a select on the database with those two fields to check if a row already exists, but if this is something that needs to be unique there should also be a unique index on those two columns in your MySQL table.

Answer 6

I had this issue as well. Basically what I did is before the insert, do a select on the criteria that would qualify as a duplicate and check for it to return; if it does not we are ok to enter.

$query = "SELECT COUNT(id) AS mycount FROM persons WHERE firstName = '".$firstnName."' AND lastName = '".$lastName."'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
if($row['mycount'] == 0) {
    //Do insert
}