1

I have a registration.php page I made and I need to check if a username is already in my database so that I won't have 2 members with the same name...

Here is how I am inserting the user info into the database (username, password, date of registration, etc.):

mysql_query("INSERT INTO UserDb (ID, TimeStamp, UserName, Password) VALUES ('$ipaddress', NOW(), '$user_username', '$user_password')");

How can I check that database "UserDb" under the "UserName" field to make sure the user's variable "$user_username" doesn't already exist?

Flexo
  • 87,323
  • 22
  • 191
  • 272
Albert Renshaw
  • 17,282
  • 18
  • 107
  • 195

4 Answers4

2

You can make the UserName a unique field. MySQL will then refuse to insert a new record if the username already exists.

Alternatively you can run a query searching for the username. If it doesn't exists, insert it. Wrap this into a transaction so you can be sure that after you've searched for a user, an additional new one with the same name was added before you add the new one.

hakre
  • 193,403
  • 52
  • 435
  • 836
  • 1
    Transaction might be insufficient, better to lock the table beforehand. – Erbureth Dec 23 '11 at 00:06
  • 2
    To put things simply, think of a transaction as a group of SQL statements that don't make any changes to the database until you say so (`COMMIT`), and they also block parallel accesses to the fields that are being modified, so no other transactions/statements can make changes to the same data at the same time. You can also discard all the changes made in a transaction (`ROLLBACK`). Read more about it here: http://dev.mysql.com/doc/refman/5.0/en/commit.html – Telmo Marques Dec 23 '11 at 00:31
  • 1
    Transaction and Table locking together needs some knowledge how it works, please see [12.3.5.1. Interaction of Table Locking and Transactions](http://dev.mysql.com/doc/refman/5.5/en/lock-tables-and-transactions.html) in the MySQL manual. – hakre Dec 23 '11 at 01:36
2

You can make the username field in the database a primary key or unique, which guarantees the username uniqueness in the database.

Then, if you try to insert an already existing username the mysql_query() function will fail, returning FALSE.

Besides that, you should always query the database for the existence of the username, using a simple select statement:

SELECT username FROM table WHERE username='$php_username_var';

Telmo Marques
  • 5,066
  • 1
  • 24
  • 34
  • Currently my "TimeStamp" was set by default to PrimaryKey... Does this mean that no two users can register at the same time? Will this be a problem in the future? Also, I could not seem to set my UserName field to a primary key in PHPMyAdmin... Is this because it is set to "FullText -type: text" Does it have to be something else? I'm probably going to use logic if statements with the select statement to help me with the learning experience of coding but I'm also curious about this primary key thing you mentioned, I knew nothing of it until know! thanks again for all the help so far! – Albert Renshaw Dec 23 '11 at 00:12
  • 1
    "Does this mean that no two users can register at the same time?" Technically, yes. "Will this be a problem in the future?" Yes, it is a potential source for new problems. But it doesn't make sense, in this case, to use the timestamp as the primary key anyway. "Is this because it is set to "FullText -type: text" You should consider Eli's suggestion on this one and make the username the email address, and then put a maximum length on it. You can read more about primary key and unique constraints in here: http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html – Telmo Marques Dec 23 '11 at 00:19
  • 1
    I forgot to explain that the maximum length is to allow the username/email to become a primary key. Consider using the Varchar datatype for it. – Telmo Marques Dec 23 '11 at 00:27
  • Switching over to varchar(20) fixed the problem! – Albert Renshaw Dec 23 '11 at 01:14
  • Okay I tried something like this to detect if the UserName was already entered: $checkUserName = mysql_query("SELECT UserName FROM UserDb WHERE UserName='$user_username'") or die(mysql_error()); print $checkUserName; It always returns "Resource id #2", wether it is a repeat name or not. What else do I need to do? – Albert Renshaw Dec 23 '11 at 01:14
  • The `mysql_query()` function returns a resource, that's why you see "Resource id #2". You need to extract the information from the resource using `mysql_fetch_array()`, like this: `$array = mysql_fetch_array($checkUserName ); $username = $array["UserName"]`. This example assumes a result of one line. More here: http://php.net/manual/en/function.mysql-query.php and here: http://php.net/manual/en/function.mysql-fetch-array.php Familiarize yourself with the use of documentation, you'll be thankful, trust me :) – Telmo Marques Dec 23 '11 at 01:24
  • When I do this: `$checkUserName = mysql_query("SELECT * FROM UserDb WHERE UserName = '$user_username'") or die(mysql_error());` `$arrayUserName = mysql_fetch_array($checkUserName);` `print $arrayUserName;` It prints "array" on the webpage if the username is in the database and it prints nothing if the username is not already in the database... So I figured a simple if statement like this would work: `if ($arrayUserName == "Array") {` But it isn't seeming to return. – Albert Renshaw Dec 23 '11 at 01:58
  • 1
    The `$arrayUserName` is an array containing all the fields of the selected line, so you have to do `$arrayUserName["UserName"]` to get the value of the "UserName" field. – Telmo Marques Dec 23 '11 at 15:50
1

create a unique key on UserName and if the INSERT errors out, check the error number.

Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
1

It's best to check for the username first, rather than depending on the database to tell you via error - always better to be explicit rather than have functionality implied.

Also, you may need to consider the situation where there are one or more existing records with the same username.

For example, if a user signs up for a subscription, cancels after a few months, and then signs up again later.

Sometimes it's best to re-open the account, and other times just to create a new one...In that case it might be ok to have duplicate rows with the same username, so long as only the current one is active, and the rest are for historical reporting purposes.

In fact, you might NEED the duplicate rows if the old user instances are referenced in your billing tables, notes, etc. If you deleted the user, it would cause issues with your reporting.

A word of advice as well - consider using the user's email address as their username - you know it's a string that's unique to them, gives you a default way to contact them, and can be validated at sign up.

Eli
  • 97,462
  • 20
  • 76
  • 81
  • That email is great advice! I won't be storing any email addresses on this project because I don't trust myself to have a secure enough website to store personal information... Just a week ago I was storing info without using mysql_real_escape_string or strip_tags... I wouldn't want to be held responsible for leaked info like that haha! But in the future I will definitely keep advice like this in mind! Thanks so much! – Albert Renshaw Dec 23 '11 at 00:20