Check If Username Exists in MySQL Table via PHP

Query mySQL to check if user already exists in two tables

Since all you want to do is check whether the registration values already exist, it is probably easiest to use EXISTS subqueries rather than JOINing both tables together. Something like this:

SELECT
    EXISTS (SELECT * FROM users WHERE username = ?) AS found_username,
    EXISTS (SELECT * FROM registered WHERE nameFirst = ? AND nameLast = ?) AS found_name,
    EXISTS (SELECT * FROM registered WHERE email = ?) AS found_email

In this query the ? represent placeholders for the username, first name, last name and email values. The purpose of using prepared statements with placeholders is to protect against SQL injection, for more information on that see this Q&A. Using them also has the benefit of removing the need to escape special character in inputs (for example, if you wanted to insert O'Hara into the nameLast field using a value enclosed in single quotes).

So, for your code, you would do something like:

if ($stmt = $con->prepare('SELECT
        EXISTS (SELECT * FROM users WHERE username = ?) AS found_username,
        EXISTS (SELECT * FROM registered WHERE nameFirst = ? AND nameLast = ?) AS found_name,
        EXISTS (SELECT * FROM registered WHERE email = ?) AS found_email')) {
    // Bind parameters (s = string, i = int, b = blob, etc)
    $stmt->bind_param('ssss', $_POST['username'], $_POST['firstname'], $_POST['lastname'], $_POST['email']);
    $stmt->execute();
    $stmt->bind_result($found_username, $found_name, $found_email);
    $stmt->fetch();
    // Store the result so we can check if the account exists in the database.
    if ($found_username) {
        // Username already exists
        echo 'Username already exists. Please choose another.';
    }
    elseif ($found_name) {
        // Name already exists
        echo 'Name already exists. Please choose another.';
    }
    elseif ($found_email) {
        // Email already exists
        echo 'Email already exists. Please choose another.';
    }
    else {
        // Insert new account
    }
    $stmt->close();
} 
else {
    // Something is wrong with the sql statement, check to make sure accounts table exists with all 3 fields.
    echo 'Could not prepare statement!';
}
$con->close();

How to check if username already exists with PHP/MYSQL

MYSQL: This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used.

MYSQLI Reference: http://php.net/manual/en/mysqli.query.php

PDO Reference: http://php.net/manual/en/book.pdo.php

Advantages of using PDO:

Most PHP programmers have discovered how to use MySQLi and MySQL extensions. However, PHP Data Objects (PDO) offer ways to work with objects and retrieve prepared statements which make work much easier.

PDO is a database access tool in PHP which enables uniform access across multiple databases. It does not support syntax's specific to databases, but it permits relatively seamless switching between different platforms and databases, which can simply be done by changing the connection string.

Below is a little information about PDO, mainly directed at programmers who are still using MySQL and MySQLi extensions, outlining the superiority of the former. Different aspects will be studied in the next few paragraphs.

Database support

The PDO extension has the capability to access any database which the PDO driver has been written for. There are many PDO drivers available, a few of which include PDO drivers meant to access Free TDS, Sybase, Microsoft SQL Server, IBM DB2, Firebird/Interbase 6, Oracle Call Interface and PostgreSQL databases, among many more.

The drivers are not automatically available in every system, so you will have to find your available drivers and add the ones that you need.

Database connecting

There are different syntax's to establish database connections which depend on specific databases. When using PDO, you want to ensure that your operations are wrapped in try/catch blocks and that you utilize the exception technique.

In normal cases, only a single connection need be made, and connections are closed by programming the database handle as a null. You can look up more specific options and syntax's in various resource sites.

Error handling

PDO allows for the use of exceptions for error-handling, which is why you’re advised to wrap PDO in try/catch blocks. This way, PDO can be forced into the relevant error mode attribute in order to produce an exception.

There are three – silent (default), warning and exception – modes. The latter two are more useful in DRY programming. ‘Warning’ error mode is useful for debugging and the ‘exception’ mode allows graceful error handling while hiding data that a person might use to exploit your system.

Inserts and Updates

PDO condenses the common insert and update database operations into a simple two-step process: Prepare >> [Bind] >> Execute. With this method, you can take full advantage of PDO’s prepared statements, which offer you protection against malicious attacks through SQL injection.

Prepared statements are pre-complied SQL statements which may be executed several times by sending this data to the servers. They are advantageous in that data used within the placeholders is automatically protected from SQL injection attacks.

Hence It is better to use PDO and it will avoid the SQL Injections that are prevailing now a days.

PDO Code to check whether the username and passwords fields exist in the DB:

 <?php
//Connections
try {
    $handler = new PDO('mysql:host=localhost;dbname=s','root', '*');
    $handler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e){
    exit($e->getMessage());
}

$name = $_POST['name']; 
$username = $_POST['username']; 
$email = $_POST['email'];   
$password = $_POST['password']; 
$password1 = $_POST['passwordconf'];
$ip = $_SERVER['REMOTE_ADDR'];

//Verifcation 
if (empty($name) || empty($username) || empty($email) || empty($password) || empty($password1)){
    $error = "Complete all fields";
}

// Password match
if ($password != $password1){
    $error = "Passwords do not match";
}

// Email validation

if (!filter_var($email, FILTER_VALIDATE_EMAIL)){
    $error = "Enter a Valid email";
}

// Password length
if (strlen($password) <= 6){
    $error = "Choose a password longer then 6 character";
}

if(!isset($error)){
//no error
$sthandler = $handler->prepare("SELECT username FROM users WHERE username = :name");
$sthandler->bindParam(':name', $username);
$sthandler->execute();

if($sthandler->rowCount() > 0){
    echo "User Already Exists.";
} else {
    //Securly insert into database
    $sql = 'INSERT INTO users (name ,username, email, password, ip) VALUES (:name,:username,:email,:password,:ip)';    
    $query = $handler->prepare($sql);

    $query->execute(array(

    ':name' => $name,
    ':username' => $username,
    ':email' => $email,
    ':password' => $password,
    ':ip' => $ip

    ));
    }
}else{
    echo "error occured: ".$error;
    exit();
}

Hope so this reference might be useful for you to develop the codes in the future project by yourself.

How To Check If Username Is Already Exists In a MySQL Table show hidden password field

1)Create a new folder in your Root directory. Put inside it the 3 following files:

2)Create a HTML file name it "index.php" then copy/paste this code inside it and save it.

<!DOCTYPE html>
<html lang="fr_FR">
  <head>
    <meta charset="utf-8">
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
    <script src="check_username.js"></script>
    <title>Username check</title>
  </head>

  <body>
    <form id="login" action="" method="post">
       <div>username:</div>
        <input name="username" id="username" value="" maxlength="100" type="text" onkeyup="checkUsername();" />
       <div id="lay_pw" style="display:none">
        <div>password:</div>
        <input name="password" id="password" value="" type="password" />
      </div>
      <br />
      <input type="submit" name="go" value="login" class="main_btn1" />
    </form>
  </body>
</html>

3)Create a javascript file and name it "check_username.js", then copy/paste this code inside it and save it.

$.customPOST = function(data,callback){
    $.post('check_username.php',data,callback,'json');
}
//when typing, the script checks if the username exists
function checkUsername() {
  $.customPOST({username: $('#username').val()},function(r){
    //username exists
    if(r.exists == 'yes'){
     $( "#lay_pw" ).toggle(400,function(){});
    }
    else{
     $( "#lay_pw" ).css("display", "none");
    }
  });
}

4)Create a PHP file, name it "check_username.php", copy/paste this code inside it and save it.

<?php
$link = mysql_connect('localhost','root','');
mysql_select_db('database');
$response = 'no';
if(isset($_POST['username']) && trim($_POST['username']) != ''){
 $query = mysql_query("SELECT * FROM table WHERE username = '".mysql_escape_string(trim($_POST['username']))."'", $link);
 if(mysql_num_rows($query) > 0){
  $response = 'yes';
 }
}
echo json_encode(array('exists' => $response));
?>

5)Use this sample database (i used it for testing this stuff)

CREATE TABLE IF NOT EXISTS `users` (
  `user` varchar(50) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`user`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `users` (`user`, `password`) VALUES
('test1', '5a105e8b9d40e1329780d62ea2265d8a'),
('test2', 'ad0234829205b9033196ba818f7a872b'),
('alpha', '2c1743a391305fbf367df8e4f069f9f9'),
('beta', '987bcab01b929eb2c07877b224215c92');

MySQL - Check if username already exists before submitting the form

If you want to check before submitting the form you can go for ajax only.check after click the submit button this will work you can try this out.

<?php
    $servername = "xxxxxxxxx";
    $username = "xxxxxxxxxx";
    $password = "";
    $dbname = "test";
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    if(isset($_POST['submit'])) {
        $username=$_POST['username'];
        $email = $_POST['email'];
        $password = $_POST['password'];

        //Query statement with placeholder
        $query = "SELECT fname
                  FROM person
                  WHERE fname = '$username'";

        // Execute it
        try {
            $stmt = $conn->prepare($query);
            //for you no need to pass parameter inside execute statement
            $result = $stmt->execute();
            //After executing the query store the result like below
            $stmt->store_result();
        } catch(PDOException $ex) {
            echo $ex->getMessage();
        }

        //Now Check for the row count
       //you have to check numrows >0 like this
       if($stmt->num_rows>0) {
           echo "Account Exists";
           die;
       } else {
           $sql = "INSERT INTO person(username,email,password) VALUES ('$username','$email','$password')";

          if ($conn->query($sql) === TRUE) {
             echo "New record created successfully";
          } else {
             echo "Error: " . $sql . "<br>" . $conn->error;
          }
      }
   }
   $conn->close();
?>

Check if username already exists using PHP

You are manually adding spaces around your username so it will look like it does not exist:

$query = "SELECT * from User WHERE username=' ".$username." ' ";
                                             ^             ^

Should be:

$query = "SELECT * from User WHERE username='".$username."' ";

Using a prepared statement would avoid that problem and potential sql injection problems in one go:

$query = "SELECT * from User WHERE username=?";

Also make sure you consistently use your table- and column names: User is not necessarily the same as user.

Also note that you should never store plain-text passwords in a database, you should salt and hash them.

Checking if mysql user exists

If you're deleting the MySQL user anyways, then there's really no need to check if it exists first. MySQL won't throw any errors if there's nothing to delete:

DELETE FROM mysql.user WHERE User = 'username';