prevent duplicate usernames adding a sequence number after username

Question

I'm trying to Implement the facebook registration. It works and i'm getting back all the data I need. Now I want to assign a username to the user like this:

$username = ''.$first_name.'.'.$lastname.'';

The problem is that I don't know if a user with the same name and last name will register to the website and i would like to check if the username is taken and add a sequence number to the basic $username (facebook does the same), like this:

name.lastname
name.lastname.1
name.lastname.2
etc

I tried with:

$temp_username = ''.$first_name.''.$last_name.'';
$check_username = mysql_query("SELECT username FROM users WHERE username = '$temp_username'");
$num_rows = mysql_num_rows($check_username);
if ($num_rows == 0){
    $username = strtolower($temp_username);
} else {
    $username = strtolower(''.$temp_username.'.'.$num_rows.'');
}

but of course it doesn't work because there is always just one user with that username.

EDIT*** this is how I fix it (thanks to zander):

        $temp_username = ''.$first_name.''.$last_name.'';
        $num_rows = mysql_num_rows(mysql_query("SELECT username FROM users  WHERE username = '$temp_username' OR username LIKE '$temp_username%' "));
        $username = strtolower(''.$temp_username.'.'.$num_rows.'');

Answer 1

$num_rows = mysql_num_rows(mysql_query("SELECT username FROM users WHERE username = '$temp_username' OR username LIKE '$temp_username.%' ")); will return the number of rows you actually expect. Then, use $username = strtolower(''.$temp_username.'.'.$num_rows.''); to get it done. No need of loops.

Answer 2

The following SELECT determines the user with the highest number if there are any

select max(reverse(SUBSTRING(reverse(username), 1, LOCATE('.', reverse(username))-1))) trail 
  from users
 where username like 'John.Smith.%';

SQL Fiddle Demo

Add it to PHP like this

...
if ($num_rows == 0){
    $username = strtolower($temp_username);
} else {
   ... query for the max number here
   ... concatenate the username with the max number 
}

Ah and last but not least. Make sure your code is not vulnerable to SQL injection. Use bind parameters. Good start is this answer: Best way to defend against mysql injection and cross site scripting

Answer 3

There are many existing answers that correctly suggest using the LIKE operator in your WHERE clause. But there is one critical issue that none of the existing answers have addressed.

Two people could attempt to add the same username at the same (or nearly the same) time. Each would SELECT the count of existing usernames that are LIKE that name, and they each would generate the same number suffix, and you still get duplicates.

I am neither a mysql developer nor php developer, so I won't provide much in the way of specific syntax.

You will want to make sure your users table uses the InnoDB storage engine. Your code will need to:

1. START TRANSACTION

2. SELECT FOR UPDATE to make sure only one person can get the count of a particular username at a given time

3. INSERT your new user

4. COMMIT your transaction.

See Select for update for more information.

Answer 4

Use this code instead:

$check_username = mysql_query("SELECT username FROM users WHERE username = '$temp_username' OR username LIKE  '$temp_username.%' ");

example this will match:

johnsmith or joshnsmith.X where x will be 1 , 2 , 3 .......etc

Answer 5

DB Dump

CREATE TABLE Users (
  `username` varchar(255) PRIMARY KEY,
  `firstname` varchar(255),
  `lastname` varchar(255)
);

INSERT INTO Users (`username`, `firstname`, `lastname`) VALUES (
  'praveen.kumar', 'Praveen', 'Kumar'
),(
  'praveen.kumar.1', 'Praveen', 'Kumar'
),(
  'praveen.kumar.2', 'Praveen', 'Kumar'
);

Now to the SQL, we can do this way:

SELECT *
FROM `Users`
WHERE `username` LIKE "praveen.kumar%"
ORDER BY `username` DESC

Gives an output:

+-----------------+-----------+----------+
|        USERNAME | FIRSTNAME | LASTNAME |
+-----------------+-----------+----------+
| praveen.kumar.2 |   Praveen |    Kumar |
| praveen.kumar.1 |   Praveen |    Kumar |
|   praveen.kumar |   Praveen |    Kumar |
+-----------------+-----------+----------+

And you can get the latest one this way:

SELECT *
FROM `Users`
WHERE `username` LIKE "praveen.kumar%"
ORDER BY `username` DESC
LIMIT 1

The PHP Code:

<?php
    # Outputs the largest number with that username.
    $nextUser = substr($userNameFromDB, strrpos($userNameFromDB, "."));
    $nextUser++;
?>

SQL Fiddle: http://sqlfiddle.com/#!2/ad149/1

Answer 6

Use the count() function and the like operator:

$check_username = mysql_query("
    SELECT count(username) 
    FROM users 
    WHERE username like '$temp_username%'
");

It will return the number of existent names. No need to call mysql_num_rows.

Answer 7

You should use the count() function

$query = mysql_query("
  SELECT count(user_name) cnt
    FROM users 
    WHERE username = '$just_registered_username'
");

and then fetch the result using

$row = sql_fetchrow($query);

And then get the count of users as

$next_index = $row->cnt;

Then append it to the new username

$new_username = "{$just_registered_username}.{$next_index}";

Don't forget to add comments to your final code. Also try and use PDO for database access.

Answer 8

If you want to find a user name that does not exist, you have to try combinations, until you find a non existing username.

Therefore, loop until you find a non existing name:

$temp_username = $first_name . $last_name;
$i=1;
$found = false;
while(!$found) {
  $check_username = mysql_query(
     "SELECT username FROM users WHERE username = '$temp_username'");
  $found = mysql_num_rows($check_username);
  if ($found){
    $username = strtolower($temp_username);
  }
  else{
    $temp_username = $first_name . $last_name . '.' . $i;
    $i++
  }
}