Best way to check for existing user in mySQL database?

Question

I am trying to create a user login/creation script in PHP and would like to know the best way to check if a username exists when creating a user. At the moment, I have the following code:

function createUser($uname,$pword) {
        $server->connect(DB_HOST,DB_USER,DB_PASS,DB_NAME);
        $this->users = $server->query("SELECT * FROM user_list");
        while ($check = mysql_fetch_array($this->users) {
            if ($check['uname'] == $uname) {

What I'm not sure about is the best logic for doing this. I was thinking of adding a boolean variable to do something like (after the if statement):

$boolean = true;
}
if ($boolean) {
    echo "User already exists!";
    }
else {
    $server->query("INSERT USER INTO TABLE");
    echo "User added Successfully";
    }

But this seems a little inefficient - is there a more efficient way to do this? Sorry if this has a basic solution - I'm a relatively new PHP programmer.

Gumbo · Accepted Answer · 2010-08-19T10:40:12.300

7

Use the WHERE clause to get only rows with the given user name:

"SELECT * FROM user_list WHERE uname='".$server->real_escape_string($uname)."'"

Then check if the query results in selecting any rows (either 0 or 1 row) with MySQLi_Result::num_rows:

function createUser($uname,$pword) {
    $server->connect(DB_HOST,DB_USER,DB_PASS,DB_NAME);
    $result = $server->query("SELECT * FROM user_list WHERE uname='".$server->real_escape_string($uname)."'");
    if ($result->num_rows() === 0) {
        if ($server->query("INSERT INTO user_list (uname) VALUES ('".$server->real_escape_string($uname)."'")) {
            echo "User added Successfully";
        } else {
            echo "Error while adding user!";
        }
    } else {
        echo "User already exists!";
    }
}

edited Aug 19 '10 at 10:40

answered Aug 19 '10 at 10:33

Gumbo

620,600
104
758
828

Awesome answer. Thank you! One question: What does the . before $server do? Never seen that before. – Saladin Akara Aug 19 '10 at 10:40
1

@Saladin Akara: That’s the [string concatenation operator](http://php.net/language.operators.string). – Gumbo Aug 19 '10 at 10:43
1

The OP should also consider making his DB_USER column a UNIQUE INDEX – Richard H Aug 19 '10 at 10:44
… or making it the primary key. – Gumbo Aug 19 '10 at 10:46
@Gumbo Haha, of course! I'm used to seeing it with spaces. Like text . $variable . text @Richard Never thought of that. I already have a UID as primary key, for other stuff I might want to do in the future, ya see. But sounds like a plan. – Saladin Akara Aug 19 '10 at 10:52
If you're using the SELECT method you can also add LIMIT 1. No point in searching on after you've found a match... – Michael Clerx Aug 19 '10 at 12:49
@Saladin Akara: The *primary key* constraint is a combination of the *unique* constraint and the *not null* constraint. – Gumbo Aug 19 '10 at 12:54

score 2 · Answer 2 · answered Aug 19 '10 at 10:46

This basically involves doing a query, usually during validation, before inserting the member into the database.

<?php
$errors = array();
$alerts = array();

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

    $pdo = new PDO('[dsn]', '[user]', '[pass]');

    // first, check user name has not already been taken
    $sql = "SELECT COUNT(*) AS count FROM user_list WHERE uname = ?";
    $smt = $pdo->prepare($sql);
    $smt->execute(array($_POST['uname']));
    $row = $smt->fetch(PDO::FETCH_ASSOC);
    if (intval($row['count']) > 0) {
        $errors[] = "User name " . htmlspecialchars($_POST['uname']) . " has already been taken.";
    }

    // continue if there are no errors
    if (count($errors)==0) {
        $sql = "INSERT INTO user_list ([fields]) VALUES ([values])";
        $res = $pdo->exec($sql);
        if ($res==1) {
            $alerts[] = "Member successfully added.";
        } else {
            $errors[] = "There was an error adding the member.";
        }
    }
}

The above example uses PHP's PDO, so change the syntax to use whatever database abstraction you use.

Best way to check for existing user in mySQL database?

2 Answers2

Linked