How to prevent duplicate Usernames in php/mysql

Question

(This is my first try with php)

I have a very basic register page. http://graves-incorporated.com/test_sites/member_test/register/register.php

* I just remembered PHP code doesn't show up in the source so here it is:

enter code here
<?php
include('connection.php');

if(isset($_POST['form'])){
    if(empty($_POST['username']) || empty($_POST['password']) || empty($_POST['conf_pass']) || empty($_POST['email'])){
        echo '<b>Please fill out all fields.</b>';

    }elseif($_POST['password'] != $_POST['conf_pass']){
        echo '<b>Your Passwords do not match.</b>';
    }else{
        $url = 'http://graves-incorporated.com/test_sites/plantation_park_2012/';
        echo '<META HTTP-EQUIV=Refresh CONTENT="2; URL='.$url.'">';
        echo '<b>Congrats, You are now Registered.</b>';
        mysql_query("INSERT INTO users VALUES(NULL, '$_POST[username]', '$_POST[password]', '$_POST[email]')");     
    }
}
?>

I want to make sure I don't get duplicate users and/or email addresses in the database. I set up a Unique Key in MySQL for Username and Email, which prevents it, but the user on the actual form doesn't know that, it still tells them "Congrats, you are signed up" or whatever it says... haha

So what can I add to the php code (and where in the code) that would prevent this?

Thanks for helping this major noob, Dan Graves

Answer 1

You can use ajax query before submit to check/prevent is already exists Also in back-end after you do INSERT INTO... you can do SELECT FOUND_ROWS() to check if it's actually equals 1, it means one row inserted. Then redirect user to error page with information what is not correct. For better user experience, I recommend first option, with JQuery it will not take a lot of time to implement.

Answer 2

From a user interface stand point, I think it would be great if they have the ability to check via ajax request if the username exists in the db or not, sort of like an availability checker.

It would also save you cpu time if you isolate the process of checking username availability rather than processing everything just to find out that the username is not available.

To do so via jquery, i would recommend:

$('#checkAvailabilityButton').click(function() {
    var usernameVal = $('#usernameField').val(); // assuming this is a input text field
    $.post('checkusername.php', {username=usernameVal}, function(data) {
        alert('data');
    });
});

And on your php end run a query on your database that would look like:

"SELECT Username FROM users WHERE Username = 'POSTVALUE'"

if (mysql_num_rows > 0) {
    echo "Username is taken"
}

Also be very very careful not to allow unsanitized post variables into your database to prevent SQL injections.

Lastly, try to use a better PHP database extensioin like MySQLi, most STMT requests automatically sanitize variables via mysqli_prepare.

Good luck!

Answer 3

Which DB driver are you using? Are you looking for DB errors? Some of them just happily sail past any DB errors and you have to call a function specifically to check for DB errors. your DB should throw an error on duplicate data, and you can pick that error up and alert the user.

Answer 4

<?php
include('connection.php');

if(isset($_POST['form'])){
    if(empty($_POST['username']) || empty($_POST['password']) || empty($_POST['conf_pass']) || empty($_POST['email'])){
        echo '<b>Please fill out all fields.</b>';

    }elseif($_POST['password'] != $_POST['conf_pass']){
        echo '<b>Your Passwords do not match.</b>';
    }else{

        $dup = mysql_query("SELECT username FROM users WHERE username='".$_POST['username']."'");
        if(mysql_num_rows($dup) >0){
            echo '<b>username Already Used.</b>';
        }
        else{
            $url = 'http://graves-incorporated.com/test_sites/plantation_park_2012/';
            echo '<META HTTP-EQUIV=Refresh CONTENT="2; URL='.$url.'">';

            $sql = mysql_query("INSERT INTO users VALUES(NULL, '$_POST[username]', '$_POST[password]', '$_POST[email]')");     
            if($sql){
                 echo '<b>Congrats, You are now Registered.</b>';
            }
            else{
                echo '<b>Error Registeration.</b>';
            }
        }
    }
}
?>