How to tell if a username is already taken in a database

Question

How do I tell if a username in a database is already taken with php for a signup page?

PvdL · Answer 1 · 2013-09-10T22:01:23.067

5

something like:

$sql = 'SELECT * FROM USERS where user_name = "'.$_GET['username'].'"';
$res = mysql_query($sql);
if($res && mysql_num_rows($res)>0){
  echo 'taken';
} else {
  echo 'free';
}

edited Sep 10 '13 at 22:01

answered Jul 07 '11 at 17:40

PvdL

1,578
1
20
33

For anyone finding this now, please don't use this deprecated code. See: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – Erty Seidohl Aug 10 '14 at 08:43

score 4 · Answer 2 · edited May 23 '17 at 11:54

4

Conceptually, you would probably use GET for the username field to a script that runs a query against the database with a WHERE clause, where username is equal to the GET variable. If it returns a match, the username is taken.

There's a lot of ways to do this, if you had a more specific specification, I could elaborate even more.

We have some very interesting points from the comments; from my experience with UI, I'd definitely do this check "inline", meaning that you could check the status of the username via AJAX to give some feedback instantly as to whether or not the username is taken. In that case, you would most definitely use GET.

If that is not your intent and you are submitting any sensitive information (in this case, probably a password) in your signup form, use POST (see this question: GET versus POST in terms of security?)

edited May 23 '17 at 11:54

Community

1
1

answered Jul 07 '11 at 17:38

Nic

13,287
7
40
42

1

Why post? You're not really performing an action, so wouldn't get be more appropriate? – sdleihssirhc Jul 07 '11 at 17:40
I can get behind that. I'm so used to `POST`ing sensitive data that I used it here. – Nic Jul 07 '11 at 17:43
Think about the workflow here: 1) User fills out the form, 2) User posts the data to the server, 3) Validation <-- this is where we check if the username is taken already – Swift Jul 07 '11 at 17:45
@Mike Swift ideally (at least from a UI perspective) the check should be done via AJAX, but the question is a little vague in that regard. – Nic Jul 07 '11 at 17:46
@melee You are correct. The workflow changes to a GET in that case. – Swift Jul 07 '11 at 17:49

Swift · Answer 3 · 2011-07-08T00:18:44.843

2

I think you're looking for something like this.

$username = $_POST['username'];
$query = sprintf("SELECT * FROM users WHERE user='%s'", mysql_real_escape_string($username));
$result = mysql_query($query);

if(!$result) {
    // Some kind of error occurred with your connection.
} else {
    if(mysql_num_rows($result) > 0) {
        // Throw an error, the username exists
    } else {
        // Good to go...
    }
}

edited Jul 08 '11 at 00:18

answered Jul 07 '11 at 17:43

Swift

13,118
5
56
80

Its giving me this error Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given. – mshell Jul 07 '11 at 20:36
Try this. It sounds like you're getting false from the `mysql_query`. Are you sure your database is properly configured? – Swift Jul 08 '11 at 00:19

score 1 · Answer 4 · answered Aug 10 '14 at 12:53

Try this maybe it can help you of course you need to trim the input

//first check if it's not empty input
if (empty($_POST['userName'])) {
        $userNameErr = "User name required";
    } else {
        $userName = $_POST['userName'];
        $getData= $db->prepare("SELECT * FROM tableName WHERE user_name=?");
        $getData->bind_param('s', $userName);
        if($getData->execute()){
            $results = $getData->get_result();
            if($results->num_rows>0){
                $userNameErr = "User name already token";
            }
        }
        //check the input characters
        if (!preg_match("/^[0-9_a-zA-Z]*$/", $userName)) {
            $userNameErr = "Only letters, numbers and '_' allowed";
        }
    }

hope this helps.

How to tell if a username is already taken in a database

4 Answers4