Best way to check for duplicate entry

[Anon]

What is the best way to ensure that the data entered into a col isn't already there? For example:

Say I have a column that is identifying a person by number (Cross checked with another table containing their name etc...). I want to make sure that person doesn't get entered in that table twice. Is there a certain function I should be looking for?

Thanks! hope I explained it well enough.

chadT

[jcornett]

There are a few ways to handle it, but I've found the best way to do it is by simply doing a check before inserting the data into the table by using the mysql_num_rows function.

In your case you have 2 tables, with one referencing the user by a number. (presumably an autonumbered field in the database)

Do a check like this before inserting the command. (this assumes MySQL but should be applicable to other databases since it is SQL)

Code Below:

$result = mysql_query("SELECT ident from <TABLE NAME HERE> where name = '<NAME HERE>'");
$num = @mysql_num_rows($result);
If ($num) {
<CODE HERE represents user already in DB>
} else {
<No matching user>

}

A few things to note:
= If you get a result for $num, then there was a match for that particular name.
= Notice the @ sign before the mysql_num_rows function. This is to suppress error messages that will be generated if the result set is empty. (meaning no user was found)
= I've used the name to match up, but you could easily have used any other piece of data you don't want duplicated.

If I can offer any more help, just let me know. 🙂

John Cornett
Senior Development Engineer
Web Teks, Inc.
http://www.web-teks.com
[e-mail]

[kparker]

Let the dbms itself handle it for you, by creating a unique index on the column(s).