Check for Duplicates in a Database Before Entering Data

Before Entering data into开发者_运维知识库 a database, I just want to check that the database doesn't have the same username in the database already.

I have the username in SQL set as a key, so it can't be duplicated on that end, but I am looking at finding a more user-friendly error message then "KEY already exists".

Is there are simple way to check if the variable value already exists in a row?

Either preform a check before attempting the insert, or catch the exception and display it in a more clean and user-friendly way in your application.

Edit: Take a look at this tutorial on PHP exception handling. You probably want to wrap your query execution in a try-catch block, like so:

try
{
    // do your query here (it's been forever since I've used PHP)
}
catch(Exception $e)
{
    // display a clean error to the user
}

Except, instead of catching a general type Exception, you'll want to figure out what sort of exception you're actually getting (something like MySQLDuplicateKeyException, or whatever it may be - echo the exception you get when testing, and use that). This way, you won't display an error informing the user of an existing username, if in fact, there is another problem (like a DB connection error, for instance).

Good luck!

The technique to check whether data exists is to issue the query:

 SELECT COUNT(*) FROM YourTable WHERE YourKeyCol = YourKeyValue

and then examine the first returned column of the only returned row in the dataset. If it contains 0, the data wasn't there, otherwise it was found.

But as others have pointed out, you can just go ahead and issue your INSERT. Examine the error code to determine whether it failed. This is more performant because, for those cases where the data is not already in the database, you will execute only one query instead of two.

There might be a special way to do this depending on what RDBMS you are using. For example, using MySQL, you can say

INSERT INTO table (username,value) VALUES ('foo',123) ON DUPLICATE KEY UPDATE value = 123;

Since you've already set username to be a unique key, this will insert ('foo',123) into table only if foo is not already in the table. If it does exist, then the value is updated.

Or, you could use something like

INSERT IGNORE INTO table (username,value) VALUES ('foo',123)

which ignores the insert if foo is already in the table.

I can think of two ways to do this.

The first is simply to do a select statement beforehand on that username to detect any duplicates. If a row is returned then you know that that username already exists.

The other is that you can get mysql to return the error number using mysql_errno, you can then simply have an if statement that checks for a specific error number. The only problem with this that it may not indicate which field is a duplicate of the key.

I have used a pre select statement in my scripts.