User registration: dealing with duplicate username

ljcasey

Hi i have a database of users on my site. When a person registers, if they try and use a username which is already being used it will just post a mysql error because i have the username field set as unique.

How could i detect this and use a custom message such as "Sorry, that username is already in use"

Is the only way to do it is to hit the database like so...
SELECT * FROM users WHERE username=$username
then if the number of rows > 0 allow the registration, if not give the message.

Is there a simpler way to do it?

Roger_Ramjet

ljcasey wrote:
then if the number of rows > 0 allow the registration, if not give the message.

You mean if the number of rows == 0 allow the registration, don't you?

There is a way to do this, through using an insert ... select query and then looking at the return string for duplicates > 0.

If you use an INSERT ... VALUES statement with multiple value lists or INSERT ... SELECT, the statement returns an information string in this format:

Records: 100 Duplicates: 0 Warnings: 0

Records indicates the number of rows processed by the statement. (This is not necessarily the number of rows actually inserted because Duplicates can be non-zero.) Duplicates indicates the number of rows that could not be inserted because they would duplicate some existing unique index value. Warnings indicates the number of attempts to insert column values that were problematic in some way.

The insert ... select would look like this instead of your nomal value list or set


$sql = "INSERT INTO users(username) SELECT '$username'";

I'll leave you the fun of working out how to fetch and analyse the contents of the result string that that query will return.

Probably just easier to query for the name and output a message if it is found.