How to prevent username duplicates in mySQL (PHP)

Question

This is how my users register for database, but my question is: How can I prevent the database from having copies of the same username, or in other words, how can I prompt to the user that "Your username already exists" if their username exists in the database.

 <?php

        $error = ""; // error
        $GoodJob = ""; 
        //When submit button is pressed, send data.  

        if(isset($_POST['submit'])){

            if (empty($_POST['username']) || empty($_POST['email']) || empty($_POST['password'])) {

                $error = "<br>Please insert only letters or numbers.";

            }else{

            // Define username, firstname, lastname, email and password.
            $username = $_POST['username'];
            $firstname = $_POST['firstname'];
            $lastname = $_POST['lastname'];
            $email = $_POST['email'];
            $password = $_POST['password'];

            // Define database information
            $hostnameV = "***";
            $usernameV = "***";
            $passwordV = "***"; 
            $databaseV = "***"; 



            //connection to the database
            $connection = mysql_connect($hostnameV, $usernameV, $passwordV) 
                 or die("Unable to connect to MySQL");
            echo "Connected to MySQL<br>";


            //select a database to work with
            $selected = mysql_select_db($databaseV,$connection) 
              or die("Could not select company");

            // To protect MySQL injection for Security purposes
            $username = stripslashes($username);
            $firstname = stripslashes($firstname);  
            $lastname = stripslashes($lastname); 
            $email = stripslashes($email); 
            $password = stripslashes($password); 

            $username = mysql_real_escape_string($username); 
            $firstname = mysql_real_escape_string($firstname); 
            $lastname = mysql_real_escape_string($lastname); 
            $email = mysql_real_escape_string($email); 
            $password = mysql_real_escape_string($password); 

            // SQL query to send information of registerd users
            # FORMULA: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

            $query = mysql_query("INSERT INTO `company`.`users` (`id`, `username`, `firstname`, `lastname`, `email`, `password`) 
                                  VALUES (NULL, '$username', '$firstname', '$lastname', '$email', '$password')", $connection); 


            //close the connection
            mysql_close($connection);    

            } // end if statement
            ***
        }

        ?>

         <div id="main">
                <div id="login">
                    <h2>REGISTER</h2>
                    <form action="" method="post">
                            <input id="name" name="username" placeholder="Pick a username" type="text">
                            <input id="name" name="email" placeholder="Your email" type="text">
                            <input id="name" name="firstname" placeholder="firstname" type="text">
                            <input id="name" name="lastname" placeholder="lastname" type="text">
                            <input id="password" name="password" placeholder="Create a password" type="password"> 
                            <input name="submit" type="submit" value=" REGISTER ">
                        <span><?php echo $error; ?></span>
                    </form>
                </div>
            </div>

Answer 1

I would first start by not using the mysql_* functions as they are deprecated and switch to PDO as it works correctly.

To answer your question, you should simply query the database for the username, if it exists, tell the user.

Before the Insert, using PDO and prepared statements.

$stmt = $dbh->prepare('SELECT count(*) FROM user WHERE username = ?');
$stmt->execute(array($username));
$res = $stmt->fetch(PDO::FETCH_NUM);
$exists = array_pop($res);
if ($exists > 0) {
  // tell the user it already exists.
} else {
  // carry on with your insert.
}

As others have suggested, making the username unique should also be done. I wouldn't write my code to fail though when trying to insert a duplicate username, I would still test if it exists.

So to provide a bit more clarity, in reference to the below comments about using exceptions, here's why I wouldn't write my code to throw an exception if someone enters a username that is already taken. Exceptions, in my opinion, should be exceptions. That means that something exceptional happened. Someone entering a username that is already taken, I consider that to be a normal operation of the application and it should be handled in a normal non-exceptional fashion. If however, someone entered a valid username and between the time that I checked that it was not taken and when I'm running my insert, someone else grabbed that username (in that 2 millisecond timeframe), that would be an exception and I would handle it. To the user, that "exception" would look exactly the same, but in my code it would be handled as an exception.

So that's why I wouldn't just write my code to do an insert that throws an exception when someone enters a username that is already taken. I would check whether it's taken or not and then insert it, throwing an exception if that username was snagged between the time I checked and when it was inserted. I think that's good application design and proper use of exceptions. Others can disagree, but that's how I would do it. Exceptions should be exceptional, not part of the normal course of doing business.

Answer 2

You can set a validation for checking the username is exist or not.

    <?php
    function checkUserExist($username){
    $checkExist = "SELECT username from userTable WHERE username ='" . $username . "'" or die(mysql_error());
    mysql_query($checkExist);
       if($checkExist > 0){//That means username is existed from the table
          return true;
       }else{//username isn't exist
          return false;
       }
    }

    if(checkUserExist($username)){//function return true
       echo "username is already exist, please change.";
    }else{
       //Insert the user info into db
    }

This is an example for checking the username by using function with mysql_query, you can also use PDO to implement it, Cheers!