How to prevent duplicate usernames when people register?

Question

I have been making a login/register system and I am drawing close to finishing my register portion of code. The only problem I am running into is how to make it so that users cannot register with duplicated usernames. I want it to work so that my database won't accept the information, and it will tell the user about the error.

My PHP

<?php

include 'database_connection.php';
if (isset($_POST['formsubmitted'])) {
    $error = array(); //Declare An Array to store any error message
if (empty($_POST['name'])) {//if no name has been supplied
    $error[] = 'Please Enter a name '; //add to array "error"
} else {
    $name = $_POST['name']; //else assign it a variable
}

    if (empty($_POST['e-mail'])) {
        $error[] = 'Please Enter your Email ';
    } else {
        if (preg_match("/^([a-zA-Z0-9])+([a-zA-Z0-9\._-])*@([a-zA-Z0-9_-])+([a-zA-Z0-9\._-]+)+$/", $_POST['e-mail'])) {
            //regular expression for email validation
            $Email = $_POST['e-mail'];
        } else {
            $error[] = 'Your EMail Address is invalid  ';
        }
    }

    if (empty($_POST['Password'])) {
        $error[] = 'Please Enter Your Password ';
    } else {
        $Password = $_POST['Password'];
    }

    if (empty($error)) {
        //send to Database if there's no error '
    }
}

Answer 1

The best way to prevent duplicate usernames in the database is to make the database column PRIMARY KEY or mark it as UNIQUE.

-- Make it a primary key
ALTER TABLE users ADD PRIMARY KEY(username);
-- or set it to be unique
ALTER TABLE users ADD UNIQUE (username);

This will prevent duplicate records in the table with the same username. When you try to insert the same one then an error will be generated.

You can then catch the exception in PHP and check the reason. The duplicate constraint SQL error code is 1062.

Here is an example of how to catch this error when using PDO:

$error = [];
$username = 'Dharman';

$pdo = new \PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", 'user', 'password', [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,  // make sure the error reporting is enabled!
    \PDO::ATTR_EMULATE_PREPARES => false
]);

try {
    $stmt = $pdo->prepare('INSERT INTO users(username) VALUE(?)');
    $stmt->execute([$username]);
} catch (\PDOException $e) {
    if ($e->errorInfo[1] === 1062) {
        $error[] = "This username is already taken!";
    } else {
        throw $e; // let the exception to be processed further 
    }
}

Here is an example of how to catch this error when using mysqli:

$error = [];
$username = 'Dharman';

// make sure the error reporting is enabled!
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'user', 'password', 'test');
$mysqli->set_charset('utf8mb4');

try {
    $stmt = $mysqli->prepare('INSERT INTO users(username) VALUE(?)');
    $stmt->bind_param('s', $username);
    $stmt->execute();
} catch (\mysqli_sql_exception $e) {
    if ($e->getCode() === 1062) {
        $error[] = "This username is already taken!";
    } else {
        throw $e; // let the exception to be processed further 
    }
}

Answer 2

You can do it like this when the user post the username for example and click submit you can write this code or add it to your code with your modification:

<?php

// make sure the error reporting is enabled!
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'user', 'password', 'test');
$mysqli->set_charset('utf8mb4');

$username = $_POST['username'];
$stmt = $conn->prepare("SELECT * FROM table_name where username=?");
$stmt->execute([$username]);
$result = $stmt->get_result();
$user = $result->fetch_assoc();
if ($user) {
    echo "user exists";
} else {
    echo "user does not exists";
}

When you create the column of the user you can make it unique, for example create table users(username varchar(350) not null unique).