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...

Answer 1

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: