Create Non Duplicate User Name in Database

Question

I am trying to create a username for my website based on their first name and their last name. (FirstName.LastName) If a person has the same first name and last name then they would have the same username. I am trying to make it so that if they do have the same name it would just add an incrementing number. Here is what I have so far.

$sq = "SELECT * FROM Users";
$quer = mysqli_query($conn, $sq);
$userName = '';
$mod = 1;
$foundUser = false;
while ($row = mysqli_fetch_array($quer)){
    if($row['UserName'] == ucfirst(strtolower($_POST['fName'])) . '.' . ucfirst(strtolower($_POST['lName']))){
        while($foundUser = false){  
            if($row['UserName'] == ucfirst(strtolower($_POST['fName'])) . '.' . ucfirst(strtolower($_POST['lName'])) . $mod){
                $mod = $mod + 1;
                echo 'hitting mod';
            }else{
                $userName = ucfirst(strtolower($_POST['fName'])) . '.' . ucfirst(strtolower($_POST['lName'])) . $mod;
                $foundUser = true;
                echo 'last else';
            }
        }
    }                       
}

This isn't currently populating the $userName variable and I don't know why. I have a while loop. None of the echos are being hit which is weird. There is definitely multiple users with the same username already in my table.

I would suggest searching the database for the username first, rather than getting all users and looping them. As your database grows this will slowly becoming less and less manageable. E.g. SELECT * FROM Users WHERE UserName = Firstname.Lastname — Onfire, Apr 13 '18 at 04:37

Phuc Lam · Accepted Answer · 2018-04-13T06:00:30.107

Because $userName only assign if it is exist in database. You need add else after first if condition.

if($row['UserName'] == ucfirst(strtolower($_POST['fName'])) . '.' . ucfirst(strtolower($_POST['lName']))){
    while($foundUser = false){
        if($row['UserName'] == ucfirst(strtolower($_POST['fName'])) . '.' . ucfirst(strtolower($_POST['lName'])) . $mod){
            $mod = $mod + 1;
            echo 'hitting mod';
        }else{
            $userName = ucfirst(strtolower($_POST['fName'])) . '.' . ucfirst(strtolower($_POST['lName'])) . $mod;
            $foundUser = true;
            echo 'last else';
        }
    }
} else {
    $userName = ucfirst(strtolower($_POST['fName'])) . '.' . ucfirst(strtolower($_POST['lName']));
}

You can search the username in database first, rather than getting all users and looping them. This is my suggestion:

$userName = ucfirst(strtolower($_POST['fName'])) . '.' . ucfirst(strtolower($_POST['lName']));
$sq = "SELECT * FROM Users WHERE UserName LIKE '".$userName."%'";
$quer = mysqli_query($conn, $sq);
$count = mysqli_num_rows($quer);

if($count > 0) {
    $userName = $userName . $count; // or $userName = $userName . ($count + 1)
}

It works. You needed to change the equals sign to the 'LIKE' when using wildcards though. — randomnessthatsfunny, Apr 13 '18 at 05:25

Mehran Prs · Answer 2 · 2018-04-13T06:35:00.440

If in your query using where username like UserName like '".$userName."%'"

what happen if we this scenario ?

user A register with $_POST['fName']=John & $_POST['lName']=Doe (set JohnDoe as username).
User B register with $_POST['fName']=John & $_POST['lName']=Doei (set johnDoei as username).
User C register with $_POST['fName']=John & $_POST['lName']=Doe (count of users that username is like JohnDoe is two row, set 'JohnDoe2' as usrename)

you have JohnDoe3 for User C username, but shoud get JohnDoe1 or JohnDoe2 username . any user that begin name with JohnDoe affect count of rows, not just users that is JohnDoe+{number}.

Another scenario that cause have duplicate username :

user A register with $_POST['fName']=John & $_POST['lName']=Doe (set JohnDoe as username).
User B register with $_POST['fName']=John & $_POST['lName']=Doe2 (set johnDoei as username).
User C register with $_POST['fName']=John & $_POST['lName']=Doe (count of users that username is like JohnDoe is two row, set JohnDoe2 as usrename but already taken by user user B, now you have duplicate rows, and if want to use username+(count+1) as username, assume user B have $_POST['lName']=Doe3, for user C you setting JohnDoe3 as username and again have duplicate username in your table).

My suggestion is get username from user when registering, and search just user stetted username in database and show error to user that try another username if taken already.

One note : You should using parameter binding for prevent sql injection, see How can I prevent SQL injection in PHP?

Create Non Duplicate User Name in Database

2 Answers2