CODEWITHSUNDEEP
phpmysqlsql
Jason Bullen
Jason Bullen

Reputation: 155

Solutions to username collisions

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?

Upvotes: 0

Views: 268

Answers (3)

James P.
James P.

Reputation: 19617

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.

Upvotes: 0

Barmar
Barmar

Reputation: 781726

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.

Upvotes: 1

Saranya Sadhasivam
Saranya Sadhasivam

Reputation: 1294

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;
}

Upvotes: 0

Related Questions