How to check if a user name exists?

[Tom8001]

hi, how can i check if a username exists? i tried this 

$usrsql = "SELECT * FROM $tbl_name WHERE username='$username' AND password='$password'";
        $usrres = mysql_query($usrsql);
        $usrcount = mysql_num_rows($usrres);

        if($usrres && mysql_num_rows($usrcount)>0) {

            die("Username is already taken!");
        } 

[ginerjm]

Does that code actually run?  (do you have error checking turned on?

 

Try:

 

$usrsql = "SELECT * FROM $tbl_name WHERE username='$username' AND password='$password'";
$usrres = mysql_query($usrsql);
if (!$usrres)
{
    (handle query failure situation)
    exit();
}
if (mysql_num_rows($usrres) > 0
            die("Username is already taken!");
    else
       (handle no username situation)

        

[Psycho]

Are you wanting to check if a username "exists" or are you trying to authenticate a user? If you are only wanting to know if the username exists, why does the query do a check on the password?

 

To check if a username exists, I would do this

 

SELECT id FROM table_name WHERE username = 'value_to_check' LIMIT 1

Then check if the number of rows returned is 0 or 1.

 

Note that mysql_ extensions are deprecated and should not be used. Use either mysqli_ or PDO. Plus, you shoudl also use a prepared statement to guard against SQL Injection.

[Jacques1]

Doing a SELECT query to check the existence of a value is a very, very poor approach and leads to race conditions: If two users ask for an unregistered name at the same time, then your script lets them both use the name.

 

While this may sound unlikely for a low-traffic website, a malicious user might actually do this on purpose in order to trigger bugs in your application. If your code expects the usernames to be unique, who knows how it will react to two users with the same name?

 

So, no, this is not an acceptable solution. The database needs to check the uniqueness of the username. And that's exactly what a UNIQUE constraint does.

 

When you've added the constraint, you simply do your INSERT query. If the database complains about a duplicate value, you know that the name is already taken and tell the user about it:

<?php

// assign proper names to MySQL error codes
define('MYSQL_ERROR_DUPLICATE_ENTRY', 1062);



// test values
$_POST['name'] = 'foo';
$_POST['password'] = 'bar';

$database = new PDO('mysql:host=localhost;dbname=test', 'YOUR_DB_USER', 'YOUR_DB_PASSWORD', array(
	PDO::ATTR_EMULATE_PREPARES => false,
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
));

$registrationStmt = $database->prepare('
	INSERT INTO
		users
	SET
		name = :name,
		password = :password
');

try
{
	$registrationStmt->execute(array(
		'name' => $_POST['name'],
		'password' => $_POST['password'],
	));
}
catch (PDOException $error)
{
	// check if the name is already taken
	$errorCode = $error->errorInfo[1];
	if ($errorCode == MYSQL_ERROR_DUPLICATE_ENTRY)
	{
		echo 'This name is already taken.';
	}
	else
	{
		throw $error;
	}
}

This is absolutely reliable, plus it is shorter and more efficient (because there's no need for a second query).

Edited November 26, 2014 by Jacques1

[NotionCommotion]

I agree about the benefits of the unique constraint.  However, it would be nice to warn the user that the username is taken before the submit.  I typically do a validation SELECT (on the username but not the password) beforehand, and f the user if on the rare occasion the insert doesn't go.

[Jacques1]

You may add an (inaccurate) Ajax pre-check as an extra usability feature, but the actual uniqueness check must be done by a database constraint. It cannot be implemented with normal queries.

[Strider64]

You may add an (inaccurate) Ajax pre-check as an extra usability feature, but the actual uniqueness check must be done by a database constraint. It cannot be implemented with normal queries.

What I do is check one check at the database constraint and one at registration time with a validation check (no Ajax).

 

Here's the method I use for the validation check:

  public function checkAvailability($username) {
    $db = Database::getInstance();
    $pdo = $db->getConnection();

    $this->query = "SELECT 1 FROM users WHERE username=:username";

    $this->query_params = [':username' => $username];

    $this->stmt = $pdo->prepare($this->query);

    $this->stmt->execute($this->query_params);

    $this->result = $this->stmt->fetch();

    if ($this->result) {
      return 'Username is invalid or not available, please re-enter!';
    }
  } 

If the validation doesn't get it, when the user's data is submitted and by some chance there is another user with the same name then it will catch it as Jacques1 has already. The only bummer is the user will have to re-enter all his/her data, but I can't see that happening unless it's a really busy website (A good thing ) or a freak happening. 

Edited November 27, 2014 by Strider64

[Tom8001]

 

Does that code actually run?  (do you have error checking turned on?

 

Try:

$usrsql = "SELECT * FROM $tbl_name WHERE username='$username' AND password='$password'";
$usrres = mysql_query($usrsql);
if (!$usrres)
{
    (handle query failure situation)
    exit();
}
if (mysql_num_rows($usrres) > 0
            die("Username is already taken!");
    else
       (handle no username situation)

        

 

This works great!, thanks all for your help

[Jacques1]

I fear you haven't understood a single word of the previous posts.

Edited November 27, 2014 by Jacques1

[Psycho]

Doing a SELECT query to check the existence of a value is a very, very poor approach and leads to race conditions: If two users ask for an unregistered name at the same time, then your script lets them both use the name.

 

While this may sound unlikely for a low-traffic website, a malicious user might actually do this on purpose in order to trigger bugs in your application. If your code expects the usernames to be unique, who knows how it will react to two users with the same name?

 

So, no, this is not an acceptable solution. The database needs to check the uniqueness of the username. And that's exactly what a UNIQUE constraint does.

 

The OP did not ask about "creating" a record and ensuring that the username does not already exist. He asked about verifying that a username does not exist. Many sites implement an AJAX call to check a username after the user enters the value in a form, but before they have submitted the form. So, yes, what I provided was a perfectly acceptable solution for what was asked.