[RESOLVED] preventing duplicate mysql entries

deltaforce229

is there away to prevent adding a new row to a table if it already exists? such as if a person was signing up to a site and he wanted to use a username that was already taken.

mysql_connect("localhost", "foo", "bar") or die ("Cant connect to db");
mysql_select_db("foo") or die ("Cant select db");

if (isset($_POST['username']) && $_POST['password'])
{
	$username = $_POST['username'];
	$password = $_POST['password'];
    $newuser = "INSERT INTO `account` (`username`,`password`) VALUES ('$username','$password')";
	mysql_query($newuser);
}

tomhath

Put a unique index on the column. Make sure you check for an error from mysql_query because you'll get one if it tries to insert a duplicate. Something like this:

create unique index uname on account (username)

deltaforce229

how do i check for the error?

crux_op

Its probably bad practice to generate an error just to know you have a duplicate user name.

instead before inserting any username, check to see if it exists in the DB first.

laserlight

instead before inserting any username, check to see if it exists in the DB first.

I believe one of our resident database experts informed me that that might lead to a race condition: you check, find that there is none, then try and insert... but in between the two steps someone else has inserted just what you planned to insert. As such, checking for a duplicate error is a better method.

NogDog

// assume $sql has your insert query, and you have a unique key defined for the table:
$result = mysql_query($sql);
if(!$result)
{
   if(mysql_errno() == 1062)   // 1062 is "duplicate entry" error
   {
      echo "Sorry, that username is already taken; please try another.";
   }
   else
   {
      // some other mysql error:
      die("DB insert failed: " . mysql_error());
   }
}
else
{
   if(mysql_affected_rows())
   {
      echo "Record successfully added to DB";
   }
   else
   {
      die("Unknown error occurred, record not inserted");
   }
}

Obviously, you don't have to die() in the places where I did; you'll ultimately want to use more user-friendly error-handling.

deltaforce229

Wow i didn't expect more then one responses but thanks everyone! I will actually be using this for multiple projects.

Sxooter

laserlight wrote:
I believe one of our resident database experts informed me that that might lead to a race condition: you check, find that there is none, then try and insert... but in between the two steps someone else has inserted just what you planned to insert. As such, checking for a duplicate error is a better method.

Yeah, it doesn't hurt to check in code, but it costs you overhead of doing it. Generally it's better to just toss the data at the db and see what it has to say and handle the error if it rejects it.

darthvazor

I tried your solution to my part. It works at first time then if I add different username, it come in error that it already used although that username is not exist in my sql database.

Here is my codes:

mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
$first=$_POST['first'];
$query = "INSERT INTO contacts VALUES ('','$first','','','','','')";
// assume $sql has your insert query, and you have a unique key defined for the table:
$result = mysql_query($query);
if(!$result)
{
if(mysql_errno() == 1062) // 1062 is "duplicate entry" error
{
echo "Sorry, that username is already taken; please try another.";
}
else
{
// some other mysql error:
die("DB insert failed: " . mysql_error());
}
}
else
{
if(mysql_affected_rows())
{
echo "Record successfully added to DB";
}
else
{
die("Unknown error occurred, record not inserted");
}
}
mysql_query($query);
mysql_close();
?>

what did i miss? Thanks.

Daniel Pinnell