query database for username

pyro

Is there a way for me to search a database to detect if a username is already in use?

Let me give an example:

I have a script that allows users to register for a site. What I need to do is query the database to see if the username they have chosen is already in use... I can't seem to figure out how to do this.

Any ideas?

n2solving

select count(*) from users where username = '$username_to_test'

put the value returned into variable, such as $count

if( $count > 0 )
{
// the username is taken
}
else
{
// the username is not take
}

similarly, if you have a field called 'id' in your table, you could also do 'select id from users where username = '$username_to_test'

if a value is returned, the username is taken

ahundiak

Assuming that you have username declared with a unique index then another approach is to go ahead and try to insert a record. Use mysql_affected_rows() to determine if the insertion worked. If it fails then you know the username is already being used.

This saves a query and and ensures that no one "sneaks" in a new record from the time you checked and the time you actually did the insertion.

pyro

Originally posted by n2solving
put the value returned into variable, such as $count

Do you mean like this?

$count = select count(*) from users where username = '$username_to_test'

Originally posted by n2solving
if a value is returned, the username is taken

How do you simply check if a value is returned? I can easly search like this $query = "SELECT * FROM tablename WHERE username='$username'"; but how do I check if it returned a value.

Originally posted by ahundiak
Assuming that you have username declared with a unique index then another approach is to go ahead and try to insert a record.

Woudn't that just overwrite the current data?

Sorry about all these questions, I'm new to databases...

ahundiak

Look through the articles on the home page of this site. There are several "getting started with mysql and php". You need to get comfortable with the basics.

Inserting a record will not impact existing records.

pyro

Originally posted by ahundiak
Inserting a record will not impact existing records.

I know that, but will it check to see if a username already exists?

I'll take a look those articles. Thanks...

cgraz

run your query WHERE username='$username'

then count the number of rows (hint: mysql_num_rows() )

If the number of rows returned is 0, then the username is not in the database. If the number of rows returned is greater than 0, then that username already exists.

Cgraz

ahundiak

pyro,

When you create a table you can set constraints on it that have to be met before insertions and modifications can take place.

For example:

CREATE users
(
user_id INT PRIMARY KEY AUTO_INCREMENT,
user_name CHAR(20) UNIQUE
);

Will prevent two records from having the same user_id or user_name. So if you try to insert a new record with the same user_name as an existing record, the insertion will fail.

pyro

cgraz and ahundiak...Thanks for your help. I think that either one of these would do what I'm looking for. ahundiak, what will that do if there is already a username in the database? Will it return a database error, or is there a way to return a custom error message (ie. that usename is already taken, please choose another)

Brendan_Nolan

This is what cgraz's method would be like:

$sql = "SELECT *
                FROM logins 
                WHERE username='someusername'";
$result = mysql_query($sql);
$num = mysql_numrows($result);

if ($num = "1"){

// the username is taken, so do something like :
// echo "Sorry, the username you have chosen has been taken. Please try again. <br><br>";


}

else {

// the username is fine... add the record to the database


}