Check if username exists in mysql table via php?

Question

I am trying to figure out how to have my register php code check whether or not the registee's username is already taken, and if it is, don't register it, tell the user that it's taken. Here's my entire register processing file.

<?php
$con=mysqli_connect("localhost","root","","users");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$hpassword = hash( 'sha512', $_POST['password'] );
$eusername = mysqli_real_escape_string( $con, $_POST['username'] );
$eemail = mysqli_real_escape_string( $con, $_POST['email'] );
$fusername = str_replace(' ', '', $eusername);

$sql="INSERT INTO users (username, password, email)
VALUES
('$fusername','$hpassword','$eemail')";

if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error($con));
  }
mysqli_close($con);
?>

Set a unique constraint on `username`, then read `mysqli_error($con)`, one hit to the database. — Dave Chen, Jul 23 '13 at 02:02
Edit: I also want to know if it's possible to notify the member that he/she can't use spaces in their username. Right now it's set to remove the spaces and then insert it. — Trevor Zucker, Jul 23 '13 at 02:03
Remove spaces, then compare it with the original input, if it's different, they have spaces! — Dave Chen, Jul 23 '13 at 02:04
@TrevorZucker are you even putting efforts on searching ? the question youre asking and also the question you asked on your comment has been replied a few times already use the search kindly. [How to check if user already exists in MySQL with PHP](http://stackoverflow.com/questions/17465468/how-to-check-if-user-already-exists-in-mysql-with-php?rq=1) [Php Ensuring a unique username](http://stackoverflow.com/questions/15841792/php-ensuring-a-unique-username?rq=1) [unable to check username exists or not](http://stackoverflow.com/questions/16369710/unable-to-check-username-exists-or-not?rq=1) — Prix, Jul 23 '13 at 02:06
@Prix i've googled around a bit, and I can't find a way that works. — Trevor Zucker, Jul 23 '13 at 02:08
Ohhh.. turns out it wasn't working because the user column isn't unique. Silly mistakes. — Trevor Zucker, Jul 23 '13 at 02:10
The first page list enough results with workable solutions http://www.google.com/search?btnG=1&pws=0&q=php+check+if+user+exists+site%3Astackoverflow.com — Prix, Jul 23 '13 at 02:11

score 19 · Answer 1 · answered Jul 23 '13 at 05:09

$sql=mysql_query("SELECT FROM users (username, password, email) WHERE username=$fusername");
 if(mysql_num_rows($sql)>=1)
   {
    echo"name already exists";
   }
 else
    {
   //insert query goes here
    }

you can check from database whether user exists and then paste the code

score 5 · Answer 2 · answered Jul 23 '13 at 02:27

include ('database_connection.php');
if (isset($_POST['formsubmitted'])) {
    $error = array(); 
    if (empty($_POST['username'])) { 
        $error[] = 'Please Enter a name '; 
    } else {
        $username = $_POST['username']; 
    }

    if (empty($_POST['e-mail'])) {
        $error[] = 'Please Enter your Email ';
    } else {

        if (filter_var($_POST['e-mail'], FILTER_VALIDATE_EMAIL)) {
            //for email validation (refer: http://us.php.net/manual/en/function.filter-var.php)

            $email = $_POST['e-mail'];
        } else {
            $error[] = 'Your EMail Address is invalid  ';
        }

    }

    if (empty($_POST['password'])) {
        $error[] = 'Please Enter Your Password ';
    } else {
        $password = $_POST['password'];
    }

    if (empty($error))

    { // If everything's OK...


        $query = "SELECT * FROM members  WHERE username ='$username'";
        $result = mysqli_query($dbc, $query); // here $dbc is your mysqli $link
        if (!$result) {
            echo ' Database Error Occured ';
        }

        if (mysqli_num_rows($result) == 0) { // IF no previous user is using this username.

            $query = "INSERT INTO `members` ( `username`, `email`, `password`) VALUES ( '$name', '$email', '$password')";

            $result = mysqli_query($dbc, $query);
            if (!$result) {
                echo 'Query Failed ';
            }

            if (mysqli_affected_rows($dbc) == 1) { //If the Insert Query was successfull.

                // Send an email

                // Finish the page:
                echo '<div class="success">Thank you for registering! A confirmation email has been sent to ' . $email . ' Please click on the Activation Link to Activate your account </div>';

            } else { // If it did not run OK.
                echo '<div class="errormsgbox">You could not be registered due to a system error. We apologize for any inconvenience.</div>';
            }

        } else { // The username is not available.
            echo '<div class="errormsgbox" >That username has already been registered.
</div>';
        }

    } else { //If the "error" array contains error msg , display them.... e.g....

        echo '<div class="errormsgbox"> <ul>';
        foreach ($error as $key => $values) {

            echo '  <li>' . $values . '</li>';

        }
        echo '</ul></div>';

    }

    mysqli_close($dbc); //Close the DB Connection

} // End of the main Submit conditional.

score 3 · Answer 3 · answered Jul 23 '13 at 02:05

3

Either you can use Dave's way and check' the error code, or you can precheck whether the user exists

 $sql="SELECT FROM users (username, password, email) WHERE username=$fusername"

Now check the results of this. If a row is fetched, then the user exists. Indicate this to the user. If not, the sun is shining on the user. Give him a cookie

answered Jul 23 '13 at 02:05

SoWhat

5,564
2
28
59

2

Don't do this. It's racy. – Ignacio Vazquez-Abrams Jul 23 '13 at 02:07
racy. what does that mean? – SoWhat Jul 23 '13 at 02:09
2

It means that it involves a [race condition](http://en.wikipedia.org/wiki/Race_condition). There is the chance that another user could take the username in between the `SELECT` and the `INSERT`. – Ignacio Vazquez-Abrams Jul 23 '13 at 02:10
oh true that. but there's no way of letting the user know if his username will be available if you indicate it via ajax – SoWhat Jul 23 '13 at 02:13
@IgnacioVazquez-Abrams Couldn't you then just use a `transaction` to prevent the race condition? – irosenb Jul 23 '13 at 02:27
How I designed mine was to check the database via Ajax (single hit) and just return "User Available" or "Username Taken" but upon submission of the actual form if the user was all of a sudden there... I popped up a funny box saying.. "Awww, looks like someone took your username in the last few seconds. We're sorry, but please try again!". Sprinkles a little humor into the mix. Also, to the above person saying to use a transaction... that wouldn't work well here... you're only checking if the user exists. Another way I thought of doing it was to... – PerryCS Apr 14 '15 at 21:06
have a separate table that temporarily held username Ajax queries for a few minutes before releasing them again. But, too many extra database hits. The other option, is to just insert this username as it's being checked without the rest of the form data, and then populate it later on. But, don't forget to check the DB for usernames that never get filled out and remove them. I like my solution... a little humor and the odds of someone taking your username before you can finish the form... not too likely. – PerryCS Apr 14 '15 at 21:08
To the person asking about using a transaction in this case... it wouldn't work nicely... you're checking the usernames existence without the rest of the form data. To check the name, and then later submit the name and the form would be 2 separate things. As far as I know it wouldn't be a good method of doing that. A transaction can be rolled back but it's usually done in the same "process"... not 5+ min after a user finishes filling out a form... also, if they just navigate away... how long do you "hold open the transaction?" 5 min? 15min" Not a good way to do that if it would even work. – PerryCS Apr 14 '15 at 21:15

Check if username exists in mysql table via php?

3 Answers3

Linked

Related