Make sure username is unique

BrentonEccles

So, I'm coding a registration form, and I want to query the database to make sure that the username the user wants to register with is unique, and not already in use. How can I do this?

Thanks in advance.

laserlight

Make the username column UNIQUE. If there is an error on insert, check the error to see if it is because of a UNIQUE constraint violation.

jazz_snob

use laserlight's suggestion to force uniqueness in the database

before you register someone, check the username isn't already taken with a query something like this (w/ your own table and var names of course):

$sql = "select * from user_table where username like '" . 
            mysql_real_escape_string($username) . "'";
$res =mysql_query($sql);
if(mysql_num_rows($res) > 0) {
  // user name already taken
}

if you define the username column in the table w/ unique index constraint, it will help look up efficiency too:

constraint unique index(username)

BrentonEccles

Thankyou for the help. 😃

laserlight

before you register someone, check the username isn't already taken with a query something like this (w/ your own table and var names of course):

According to those around here more skilled at databases than me, that leave a small window for a race condition:
1. The script checks that the username is not taken.
2. Another user registers the username.
3. The script attempts to insert the username.

With a unique constraint, step #3 now results in a failure that you still must handle. Consequently, you might as well just insert the username and handle the failure, if any.

jazz_snob

In that case, would you check the mysql errno to see if it was a duplicate key error?

laserlight

In that case, would you check the mysql errno to see if it was a duplicate key error?

Yes.

NogDog

In fact, I've done it enough times that I know from memory the mysql_errno() for such an error is 1062. 🙂 (But you can check http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html for that or any other error you want to specifically track.)