How to add number to username if it already exists

Question

I am trying to make a school portal system for my coursework. Every student has a username which consists of the first letter of their name, and first 4 letters of their surname. To create this username automatically, I am using this in the page where the admins adds students.

if (isset($_POST["btnAddUser"])) {
    //preparing statement to protect against sql injections
    $stmt = $conn->prepare("INSERT INTO tbluser (Username, Password, Role) VALUES (?,?,?)");
    $stmt->bind_param("sss", $usernamenew3, $password, $a);
    $password = $_POST["s_password"];
    $password = md5($password);
    $name = $_POST["s_name"];
    $surname = $_POST["s_surname"];
    $dob = $_POST["s_dob"];
    $a = "Admin";
    $usernamenew = substr($name, 0, 1);
    $usernamenew1 = substr($surname, 0, 4);
    $usernamenew3 = $usernamenew.$usernamenew1;

However, if for example two students with the same surname, and same initial letter of their name are entered, it would come up with an error, so I need it to add 01 the first time that username is used, 02 the second time and so on. Example. Name = Test, Surname = Student For this example I would like the first username with those letters to be TStud01, the second to be TStud02...

***You really shouldn't use [MD5 password hashes](http://security.stackexchange.com/questions/19906/is-md5-considered-insecure)*** and you really should use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. Make sure you [don't escape passwords](http://stackoverflow.com/q/36628418/1011527) or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, Jan 10 '20 at 17:56
Please don't just ask us to solve the problem for you. Show us how you tried to solve the problem yourself, then show us exactly what the result was, and tell us why you feel it didn't work. Give us a **clear explanation of what isn't working** and provide [a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Read [How to Ask](http://stackoverflow.com/help/how-to-ask) a good question. Be sure to [take the tour](http://stackoverflow.com/tour) and read [this](https://meta.stackoverflow.com/q/347937/1011527). — Jay Blanchard, Jan 10 '20 at 17:58
One way could be to create the username first. Then fetch all users that start with that username `LIKE 'some-username%`. That will give you a list of all users who's username is equal to or starts with the username, Then check if the username you've created exists in the result. If it does, add a number to it. Then check if that username exists in the result. If yes, then increment the number you've added and test again. Do that until it doesn't exist. _Then_ you save the user. — M. Eriksson, Jan 10 '20 at 18:02
[They were....](https://stackoverflow.com/q/59672859/1415724) @JayB — Funk Forty Niner, Jan 10 '20 at 18:22
@FunkFortyNiner I couldn't get hash_password to work for some reason, so I switched to md5 — Omar, Jan 10 '20 at 18:26
1) { /// Below code generates a new username $newUsername=$name.$Gennum; ///Then run insert code here with new user name $newUsername } else { /// Else run insert code with user name submitted through //the form which is $name } mysqli_close($conn); ?> — Dero3376, Jan 10 '20 at 18:26
Please just don't dump code in comments @Dero3376 It's unreadable. — Jay Blanchard, Jan 10 '20 at 18:37
You need to get `password_hash()` to work. MD5 is a disaster waiting to happen. — Jay Blanchard, Jan 10 '20 at 18:39

Dharman · Accepted Answer · 2020-01-11T18:34:11.833

You need to fetch the count of usernames starting with the common pattern from the database and then increment this by 1. Then you can pad it with 0 and save that in the database.

Few points to note:

Never store passwords in clear text or using MD5/SHA1! Only store password hashes created using PHP's password_hash(), which you can then verify using password_verify(). Take a look at this post: How to use password_hash and learn more about bcrypt & password hashing in PHP. Make sure the column is VARCHAR(255).
Use mb_substr() instead, because names can contain non-latin letters.
To use LIKE with mysqli you must concatenate the % in PHP and then bind that variable to the query.
To understand how to get the count of matching rows with MySQLi see How to get count of rows in MySQL table using PHP?

if (isset($_POST["btnAddUser"])) {
    $password = password_hash($_POST["s_password"], PASSWORD_DEFAULT);
    $name = $_POST["s_name"];
    $surname = $_POST["s_surname"];
    $dob = $_POST["s_dob"];
    $a = "Admin";
    $fistLetter = mb_substr($name, 0, 1);
    $shortSurname = mb_substr($surname, 0, 4);
    $usernamenew = $fistLetter.$shortSurname;

    $searchString = $usernamenew.'%';

    $stmt = $conn->prepare('SELECT COUNT(Username) FROM tbluser WHERE Username LIKE ?');
    $stmt->bind_param('s', $searchString);
    $stmt->execute();
    $countUsername = $stmt->get_result()->fetch_row()[0];

    $usernamenew .= str_pad($countUsername+1, 2, '0', STR_PAD_LEFT);

    //preparing statement to protect against sql injections
    $stmt = $conn->prepare("INSERT INTO tbluser (Username, Password, Role) VALUES (?,?,?)");
    $stmt->bind_param("sss", $usernamenew, $password, $a);
    $stmt->execute();
}

Results in:

How to add number to username if it already exists

1 Answers1