CODEWITHSUNDEEP
phpmysqlforms
Mike
Mike

Reputation: 21

Prevent multiple data entries in MySQL

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!!!

Upvotes: 2

Views: 3763

Answers (6)

rayman86
rayman86

Reputation: 1395

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
}

Upvotes: 1

Mike Flynn
Mike Flynn

Reputation: 2223

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.

Upvotes: 1

coreyward
coreyward

Reputation: 80041

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."'
)";

Upvotes: 2

eykanal
eykanal

Reputation: 27017

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.

Upvotes: 1

Czechnology
Czechnology

Reputation: 14992

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

Upvotes: 2

Alex Turpin
Alex Turpin

Reputation: 47776

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.

Upvotes: 1

Related Questions