Check Username Availability In PHP MYSQL (Step-By-Step Example)

Welcome to a tutorial on how to check for user availability in PHP and MySQL. So, you are creating your own registration page, and need to check if a username is already taken? There are quite a few things to look out for – Read on for a detailed example!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Create a test database and import 1-users.sql.
  • Change the database settings to your own in 2-users-lib.php.
  • Access 4a-register.html in your browser. Captain Obvious, use http://. Not file://.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

SCREENSHOT

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

CHECK USERNAME AVAILABILITY

All right, let us now get into the example of checking for the username availability in PHP and MYSQL.

 

PART 1) THE DATABASE

1-users.sql
CREATE TABLE `users` (
  `user_id` bigint(20) NOT NULL,
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `users` (`user_id`, `user_name`) VALUES
(1, 'joedoe'),
(2, 'johdoe'),
(3, 'joidoe'),
(4, 'jondoe'),
(5, 'joydoe');

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_name` (`user_name`);

ALTER TABLE `users`
  MODIFY `user_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

First, let us start with the database. This over-simplified example only has the primary key user_id, and a user_name column… Add more fields as required in your own project. A few things to take note though:

  • ADD UNIQUE KEY `user_name` The user name is set to unique to prevent duplicates.
  • utf8mb4_bin The user name is case-sensitive. That is, jondoe is different from JonDoe. If you prefer case-insensitive, just use the “normal” utf8mb4-general-ci.

 

 

PART 2) PHP USER LIBRARY

2-users-lib.php
<?php
class Users {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = null;
  function __construct () { try {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  } catch (Exception $ex) { exit($ex->getMessage()); }}

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }

  // (C) HELPER FUNCTION - RUN SQL QUERY
  function query ($sql, $data=null) {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }

  // (D) GET USER BY USERNAME
  function getByName ($name) {
    $this->query("SELECT * FROM `users` WHERE `user_name`=?", [$name]);
    return $this->stmt->fetch();
  }

  // (E) ADD NEW USER
  function add ($name) {
    // (E1) VALID USER NAME
    if (preg_match("/\s/", $name)) {
      $this->error = "User name cannot contain spaces.";
      return false;
    }

    // (E2) ALREADY REGISTERED
    $user = $this->getByName($name);
    if (is_array($user)) {
      $this->error = "$name is already registered.";
      return false;
    }

    // (E3) INSERT
    $this->query(
      "INSERT INTO `users` (`user_name`) VALUES (?)",
      [$name]
    );
    return true;
  }
}

// (F) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (G) NEW USERS OBJECT
$_USR = new Users();

With the database in place, we can now work on the library. This looks massive at first, but keep calm and look carefully – These are quite literally “SQL-related functions” only.

  • (A, B, G) When $_USR = new Users() is created, the constructor will automatically connect to the database. The destructor closes the connection.
  • (C) query() A helper function to run a SQL query.
  • (D) getByName() Get the user with the given user name.
  • (E) add() Register a new user, but check for a valid user name first. Yes, modify this to your own needs – Add more of your own checks, register more fields, or even send out an email.
  • (F) Change the database settings to your own.

 

 

PART 3) REGISTRATION AJAX HANDLER

3-users-ajax.php
<?php
if (isset($_POST["req"])) {
  require "2-users-lib.php";
  switch ($_POST["req"]) {
  // (A) INVALID REQUEST
  default: echo "Invalid request"; break;
 
  // (B) ADD NEW USER
  case "add":
    echo $_USR->add($_POST["name"]) ? "OK" : $_USR->error;
    break;
}}

With the foundations in place, we will deal with the actual processing next.

  • Just send $_POST["req"] to this script to specify the request. Right now, this simple script only has an add endpoint.
  • That is, send $_POST["req"] = "add" and $_POST["name"] = "USERNAME" to process “add new user”.

Feel free to add more endpoints and services as required.

 

PART 4) REGISTRATION PAGE

4A) THE HTML

4a-register.html
<form id="register" onsubmit="return register()">
  <label>User Name</label>
  <input type="text" name="name" required>
  <input type="submit" value="Register">
</form>

Lastly, the registration page itself. Nothing “special” here, just the usual HTML form.

 

 

4B) THE JAVASCRIPT

4b-register.js
function register () {
  // (A) FORM DATA
  var data = new FormData(document.getElementById("register"));
  data.append("req", "add");

  // (B) AJAX FETCH
  fetch("3-users-ajax.php", { method:"POST", body:data })
  .then(res => res.text())
  .then(txt => {
    // (B1) ADD OK - REDIRECT TO WELCOME PAGE? RELOAD?
    if (txt == "OK") {
      alert("OK");
      // location.href = "welcome.html";
    }

    // (B2) NOT OK
    else { alert(txt); }
  })
  .catch(err => console.error(err));

  // (C) PREVENT FORM SUBMIT
  return false;
}

Yep, we get the HTML form here, and POST it to the above 3-users-ajax.php for processing. The end.

 

EXTRA BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

CHECK USERNAME AVAILABILITY WITHOUT SUBMITTING THE FORM

  • Add a new case "check" in 3-users-ajax.phpecho is_array($_USR->getByName($_POST["name"])) ? "TAKEN" : "NOT".
  • Change the HTML form 4a-register.html<input type="text" onchange="check()">.
  • Add a new check() function in 4b-register.js. Pretty much a clone of register().

But personally, this does not make sense… You have to double-check the user name on submission anyway.

 

COMPATIBILITY CHECKS

Will work generally well across all modern browsers.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published.