How to prevent duplicate usernames when people register?

by

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

More Related Contents:

  1. Can I mix MySQL APIs in PHP?
  2. How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)
  3. mysqli or PDO – what are the pros and cons? [closed]
  4. What is the difference between MySQL, MySQLi and PDO? [closed]
  5. PHP PDO and MySQLi [duplicate]
  6. Unknown database error in PHP but it exists in PHPMyAdmin
  7. PHP: mysql v mysqli v pdo [closed]
  8. PHP PDO and Mysql [closed]
  9. How to properly set up a PDO connection
  10. Fatal error: Call to a member function bind_param() on boolean [duplicate]
  11. When should I use MySQLi instead of MySQL?
  12. PDO binding values for MySQL IN statement [duplicate]
  13. PDO mysql: How to know if insert was successful
  14. implement LIKE query in PDO
  15. PHP PDO prepared statement — MySQL LIKE query
  16. How can I use mysqli_fetch_array() twice?
  17. Fetching single row, single column with PDO
  18. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  19. How do I loop through a MySQL query via PDO in PHP?
  20. using nulls in a mysqli prepared statement
  21. Laravel 5.4 on PHP 7.0: PDO Exception – Could not find driver (MySQL)
  22. Using LIKE in bindParam for a MySQL PDO Query [duplicate]
  23. Use of PDO in classes [duplicate]
  24. How to handle PDO exceptions [duplicate]
  25. php artisan migrate throwing [PDO Exception] Could not find driver – Using Laravel
  26. PDO and MySQL Fulltext searches
  27. mysqli – fetch_Array error call to a member function fetch_array() on a non-object mysqli [duplicate]
  28. Cannot figure out how to run a mysqli_multi_query and use the results from the last query
  29. How to get a list of databases?
  30. PDO and MySQL ‘between’

Leave a Comment