Check Username Availability In PHP MYSQL (Simple 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!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

 

PART 1) THE DATABASE

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

<?php
class Users {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = null;
  function __construct () {
    $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
    ]);
  }

  // (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) : void {
    $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", "utf8mb4");
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

<?php
if (isset($_POST["req"])) {
  require "2-users-lib.php";
  switch ($_POST["req"]) {
  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

<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

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.

 

 

CHECK USERNAME AVAILABILITY WITHOUT SUBMITTING THE FORM

• Add a new case "check" in 3-users-ajax.php – echo 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

• Arrow Functions – CanIUse
• Fetch – CanIUse

Will work generally well across all modern browsers.

 

LINKS & REFERENCES

• Simple User Registration With PHP MYSQL – Code Boxx