PHP choose another username

Question

I have made a registration PHP file that runs through an authentication and connects to my database that I made in phpMyAdmin. The problem is, I can put in the same username without consequence and it adds to the database, so I could put; dogs as the username and then again put the same. How can I make it so the user is told; that username already exists choose another one. Here's my php so far; Also please tell me where to insert it.

<?php
require('db.php');
// If form submitted, insert values into the database.
if (isset($_POST['username'])) {
    $username = $_POST['username'];
    $email    = $_POST['email'];
    $password = $_POST['password'];
    $username = stripslashes($username);
    $username = mysql_real_escape_string($username);
    $email    = stripslashes($email);
    $email    = mysql_real_escape_string($email);
    $password = stripslashes($password);
    $password = mysql_real_escape_string($password);
    $trn_date = date("Y-m-d H:i:s");
    $query    = "INSERT into `users` (username, password, email, trn_date) VALUES ('$username', '".md5($password)."', '$email', '$trn_date')";
    $result   = mysql_query($query);
    if ($result) {
        echo "<div class='form'><h3>You are registered successfully.</h3><br/>Click here to <a href='login.php'>Login</a></div>";
    }
} else {
?>

Answer 1

You should query the database before inserting any record (user) to users table. Try the code below:

<?php
$username = mysql_real_escape_string( $username ); //Sql injection prevention
$existance = mysql_query("SELECT username FROM users WHERE username = '" . $username . "'");
if( !$existance ){
    $query    = "INSERT into `users` (username, password, email, trn_date) VALUES ('$username', '".md5($password)."', '$email', '$trn_date')";
    $result   = mysql_query( $query );
    if ( $result ) {
        echo "<div class='form'><h3>You are registered successfully.</h3><br/>Click here to <a href='login.php'>Login</a></div>";
    }
    else{
        //unsuccessful insertion
    }
} else {
    //the user existed already, choose another username
}
?>

Answer 2

Create an if-statement where you check if $username exists in the db. If it does, throw an error. If not, continue with the code.

Note

Your code is vulnerable to SQL-injection. Read this post: How can I prevent SQL injection in PHP?

Answer 3

Rewriting my entire answer to a working example. I'm going to assume your post variables are the same as mine: email, password, username

<?php
$errorMessage = "";



function quote_smart($value, $handle) {

   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }

   if (!is_numeric($value)) {
       $value = "'" . mysql_real_escape_string($value, $handle) . "'";
   }
   return $value;
}
$email = $_POST['email'];
$password = $_POST['password'];
$username = $_POST['username'];


        $email1 = $_POST['email'];
        $username1 = $_POST['username'];
        $password1 = $_POST['password'];

$email = htmlspecialchars($email);
$password = htmlspecialchars($password);
$username = htmlspecialchars($username);

        $connect = mysql_connect("localhost","DBuser", "DBpassword");
    if (!$connect) {
        die(mysql_error());
    }
    mysql_select_db("DBName");
            $results = mysql_query("SELECT * FROM users WHERE username = '$username'");
            while($row = mysql_fetch_array($results)) {
            $kudots = $row['username']; }
            if ($kudots != ""){
                $errorMessage = "Username Already Taken";
                $doNothing = 1;
            }
            $result = mysql_query("SELECT * FROM users WHERE email = '$email'");
            while($row2 = mysql_fetch_array($results)) {
            $kudots2 = $row2['email']; }
            if ($kudots2 != ""){
                $errorMessage = "Email Already in use";
                $doNothing = 1;
            }

//test to see if $errorMessage is blank
//if it is, then we can go ahead with the rest of the code
//if it's not, we can display the error

    if ($errorMessage == "") {

    $user_name = "DBUsername";
    $pass_word = "DBPassword";
    $database = "DBName";
    $server = "localhost";

    $db_handle = mysql_connect($server, $user_name, $pass_word);
    $db_found = mysql_select_db($database, $db_handle);

    if ($db_found) {

        $email = quote_smart($email, $db_handle);
        $password = quote_smart($password, $db_handle);
        $username = quote_smart($username, $db_handle);


        if ($username1 == ""){
            $errorMessage = "You need a username";
        }
        if ($password1 == ""){
            $errorMessage = $errorMessage . "<br>You need a password.";
        }
        if (!(isset($_POST['email']))){
            $errorMessage = $errorMessage . "<br>You need an email.";
        }
        $SQL = "SELECT * FROM users WHERE email = $email";
        $result = mysql_query($SQL);
        $num_rows = mysql_num_rows($result);

        if ($num_rows > 0) {
            $errorMessage = "email already exists";
            $doNothing = 1;
        }


        if ($errorMessage == "") {

            $SQL = "INSERT INTO users (email, username, password) VALUES ($email, $username, $password)";

            $result = mysql_query($SQL);

            mysql_close($db_handle);

        //=================================================================================
        //  START THE SESSION AND PUT SOMETHING INTO THE SESSION VARIABLE CALLED login
        //  SEND USER TO A DIFFERENT PAGE AFTER SIGN UP
        //=================================================================================
                session_start();
                $_SESSION['email'] = "$email1";
                $_SESSION['password'] = "$password1";

                header ("Location: myaccount.php");

    else {
        $errorMessage = "Database Not Found";
    }

}

OK, now echo $errorMessage right below or above the form, to inform the user that the Email, or Username is taken. I'm pretty sure I have a duplicate function in here for the Email, but this code does work; disregard if somebody says it's vulnerable to SQL injection; this is a working EXAMPLE! If you want to do MySQL real escape string, just Google it. I had to rewrite a couple things because I don't want my full code on a public board, if for some odd reason this doesn't work; send me an eMail(canadezo121@gmail.com) and I'll send you the full page code. (Which WORKS!) This code will probably raise some concerns with other more professional coders, this example gives you a good logical viewpoint of what goes on and how it works. You can adjust it to MySQLi, PDO, etc as you get more familiar with PHP and MySQL.

Answer 4

1 you must verify if the username all ready exists in database (Select)

2 if not exists after you can insert the new user