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 add a UNIQUE index for the column

-- Make it 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 using mysqli:

<?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 1 FROM table_name where username=?");
$stmt->bind_param("s", $username);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_row();
if ($user) {
    $error[] = "This username is already taken!";
}

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

Answer 3

There are two things you should do.

1. Make the user name a primary key in the database table. This is easily done using phpmyadmin.

2. Validate prior to insert.

For the second step, here's an algorithm. You may implement it in your own way using pdo, mysqli or even mysql (although it's not recommended now).

Algorithm at the end of your code (i.e., if there aren't errors)...

Select records that match the USERNAME supplied in the post.

If it exists, give out an error.

If it doesn't exist, insert it.

Answer 4

I used a PDO and class method, may be of some use.

//function for checking if the user already exists in the database
public function userExists($username)
{
    //prepared statements for added security
    $query = $this->db->prepare("SELECT COUNT(`id`) FROM `users` WHERE `username`= ?");
    //execute the query
    $query->execute([$username]);
    $rows = $query->fetchColumn();

    //if a row is returned...user already exists
    return ($rows > 0);
}

note the prepared statements too - definitely the way forward