Checking For Duplicates In Mysql -- Not Working

[Enso]

Hi All,

 

I've been working on a piece of code to check for duplicate username records in a MySQL database. If there is already a username in the database that the user is trying to register with, it displays an error. If not, it writes a row with the person's information to the database. The problem is that, with the way I currently have it written, it is skipping the validation and trying to write to the database anyway. Here is what I have:

 

 

$sql_query = "SELECT COUNT(*) from users WHERE username = '$uname'";

$result = mysql_query($sql_query);

if(mysql_num_rows($result) == 0) {

echo "<script>alert('Sorry, that username already exists. Please choose another.');history.back(-1);</script>";

}

 

else{

$sql="INSERT INTO `users` (`fname`, `lname`, `email`, `username`, `password`, `insert_time`) VALUES ('$fname', '$lname', '$email', '$uname','$password' ,'$date')";

}

 

I'm hoping maybe one of you can see something that I don't.

 

Thanks!

[Jessica]

If the rows == 0, you are alerting that the username exists. Otherwise if the rows == 1 or 2 or anything other than 0, you add the values.

 

Logic...

 

The bigger problem is that the num_rows will ALWAYS be 1, since you're selecting a single row with a count.

Edited October 2, 2012 by Jessica

[The Little Guy]

The easiest way is to place a "Unique" key on the username column, then do an "INSERT IGNORE"

 

<?php
$sql = mysql_query("INSERT INTO `users` (`fname`, `lname`, `email`, `username`, `password`, `insert_time`) VALUES ('$fname', '$lname', '$email', '$uname','$password' ,'$date')");
if(mysql_affected_rows($sql) == 0){
   // Duplicate found
}else{
   // Success
}

Edited October 2, 2012 by The Little Guy

[Enso]

Hi Jessica,

 

Right -- that's what I'm wanting to accomplish: if rows == 0, that username has already been created in the database; rows = anything above 0, perform the insert listed in the else statement. Am I writing it incorrectly in the code? Sorry if some of the questions seems silly...I'm pretty new at a lot of these concepts.

 

Thanks for your help

[Enso]

Hi Little Guy,

 

The problem that I run into when I try the code you specified is that it seems to skip the validation piece and the site goes to the "account successfully created" page regardless of whether or not the account already exists. However, I did put a unique constraint on the username field in the database, so even though the website says a previously existing account has been successfully created again, the database does not write the row if the username already exists. I just can't get the logic to work properly on the website side. It seems to be all or nothing -- either the site accepts everything without the validation, or the validation pops up on every account created (though if it's an unused username, it still writes to the database even when the validation screen comes up).

[Jessica]

Think about the logic of that.

 

"SELECT COUNT(*) from users WHERE username = '$uname'" - if the count is 0, then the username does NOT exist already. If it's 1 or more, it DOES exist. You're saying the opposite.

 

 

 

 

But still, mysql_num_rows WILL ALWAYS RETURN 1 ROW on a count, unless the query fails completely.

[The Little Guy]

$sql = mysql_query("INSERT IGNORE INTO `users` (`fname`, `lname`, `email`, `username`, `password`, `insert_time`) VALUES ('$fname', '$lname', '$email', '$uname','$password' ,'$date')");

 

 

Sorry, the query is wrong. you need "INSERT IGNORE" in there

[Enso]

Got it. And you were right, the COUNT was causing a return of rows regardless of whether or not it found any corresponding rows. I took that out and it now seems to be working. I never considered that. Thanks so much for your help!

[Barand]

Check the value of the COUNT(*)

 

<?php
$sql_query = "SELECT COUNT(*) from users WHERE username = '$uname'";
$result = mysql_query($sql_query);
if (mysql_result($result, 0) > 0) {			   // check the count value
   echo "<script>alert('Sorry, that username already exists. Please choose another.');history.back(-1);</script>";
}
?>