Solutions to username collisions

Question

Here's my PHP code - it checks to see if a username exists and if it does it adds a "_" and an ascending number to it.

$username starts as a user entered Username.

$usernameCopy = $username;
$appendNum = 1;
$userOK = false;
while( $userOK == false )
{
    $query = "SELECT ua.id 
    FROM someDB.users_accounts ua
    WHERE ua.user = '$username'; ";

    $resultID = mysqli_query($linkID, $query) or die("Data not found. USERNAME");
    $numRows = mysqli_num_rows($resultID);
    if( $numRows <> 0)
    {
        //USER ALREADY EXISTS
        $appendNum = $appendNum + 1;
        $username = $usernameCopy . '_' . $appendNum;
    }
    else
    {
        $userOK = true;
    }
}

Then I use the $username.

Is it possible to do something like this is a single SQL query?

Try this: http://stackoverflow.com/questions/6300849/mysql-insert-select-uuid — dcaswell, Aug 27 '13 at 06:39
What happens if you look for a username `jhondoe` and you already have in your table two rows with values `jhondoe` and `jhondoe_2`? Your php code will again produce already existing `jhondoe_2`. — peterm, Aug 27 '13 at 06:44
Just curious, have you defined the username column as `UNIQUE` in the MySQL table ? — James P., Aug 27 '13 at 06:46
Not as simple as that, there are a lot of chances of duplication, you need atleast 2 queries to get that done, not one. — Hanky Panky, Aug 27 '13 at 06:50
@JamesPoulson What's the problem you foresee with that? His code is in a loop, testing whether the username is taken. If username_1 is taken, it will then try username_2, and so on. — Barmar, Aug 27 '13 at 07:07
You're right. Skipped that so zapped my comment. Still, if there are thousands of alternate names it will be thousands of queries which sounds inefficient. See my "answer" below for an alternative. — James P., Aug 27 '13 at 07:13

Barmar · Answer 1 · 2013-08-27T07:06:10.370

I think this will do it:

$query = "SELECT CONCAT('$username',
                        IFNULL(MAX(CAST(SUBSTR(user, char_length('$username')+1) AS DECIMAL))+1,
                               '') avail_username
          FROM user_accounts
          WHERE user RLIKE '^$username[0-9]*\$'";

This will generate names like johnsmith, johnsmith1, johnsmith2, etc.

Putting the _ between them can be done, but it gets more complex.

$query = "SELECT IF(maxnum IS NULL,
                  '$username',
                  CONCAT('{$username}_', maxnum+1) avail_username
          FROM (SELECT MAX(CAST(SUBSTR(user, char_length('$username')+2) AS DECIMAL))
                FROM user_accounts
                WHERE user RLIKE '^$username(_[0-9]+)?\$') x";

The NULL test handles the case where there's no name at all with the prefix yet -- the user then gets the base name. The other cases get the maximum numeric suffix of all the names, and then add 1 to it to get an available suffix. If the only name in the DB is the base name, the suffix will be the empty string, which converts to 0 when CAST, and this will then increment to 1.

`IF` inside of a query? is that allowed in mysql? why not use the `ifnull`-function or `case`? — DrCopyPaste, Aug 27 '13 at 08:22
@DrCopyPaste Yes, IF is allowed in a query. http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html. I used `IFNULL` in the first version, but it doesn't apply in the second because the non-null result is not the variable I'm testing. You can use CASE, it's just more verbose so I prefer `IF`. — Barmar, Aug 27 '13 at 08:26

score 0 · Answer 2 · answered Aug 27 '13 at 06:52

For unique username, you can use like this.

$firstname = "Testing";
$username=preg_replace('/[^a-zA-Z0-9]/s', '', $firstname). $this->generateRandomString(5);

Function generateRandomString,

function generateRandomString($length = 5) 
{
    $characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $randomString = '';
    for ($i = 0; $i < $length; $i++) {
        $randomString .= $characters[rand(0, strlen($characters) - 1)];
    }
    return $randomString;
}

James P. · Answer 3 · 2013-08-27T07:46:06.567

Not a straight answer but on the PHP side you could try something like this.

if( $numRows <> 0) //USER ALREADY EXISTS
{
        $username = $usernameCopy . '_' . md5(microtime());
}

Not infallible but you get the idea. It's unlikely you'll get collisions and you can add a random element to reduce that likelyhood further. With that said, as Barmar points out very few people would want to enter 32 extra characters just to support unicity in this way. So scratch this in terms of usability.

On the SQL side, if you use the +1 solution the issue is that you will eventually have to loop to test names, which would be a slight issue when there's a big number of them. To avoid that and find the last username used, you could do something along these lines:

SELECT FROM users WHERE username LIKE 'username%' AND date_created = MAX(date_created)

Edit: Another thought. Don't know how practical this would be but you could have a table for tracking dupes.

username_dupecheck

id PK
basename varchar
last_increment int

In PHP you could extract the basename as follows. You will have to check this against cases where numbers are outside the numeric suffix and decide if this is practical.

$basename = preg_replace("/\d+$/","",$username);

P.S: MySQL's ON DUPLICATE KEY syntax might open other possibilities.

Ugh, would you really like to have to enter a username with an md5 string at the end of it, instead of just james_1? — Barmar, Aug 27 '13 at 07:00
It's an idea to build on. Didn't say it was practical ;p . See my comment above about issues using the +1 approach. — James P., Aug 27 '13 at 07:00
Even james_1 wasn't unique, because he was james_6 in Scotland at the same time, so there already was a james_1 there.... sorry, couldn't resist a history joke — Mark Baker, Aug 27 '13 at 07:06

Solutions to username collisions

3 Answers3