How to prevent duplicate usernames when people register?

Solution 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!";
    }
}

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!";
    }
}

Solution 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

$connect = mysql_connect('whatever', 'whatever', 'whatever');
$database = mysql_select_db('your dbname');
$username = $_POST['username'];
if (isset($username)) {
    $mysql_get_users = mysql_query("SELECT * FROM table_name where username='$username'");

    $get_rows = mysql_affected_rows($connect);

    if ($get_rows >= 1) {
        echo "user exists";
        die();
    } else {
        echo "user do not exists";
    }
}

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