How to detect if a user exists in a database

Question

I would like to have some php code detect if a user exists in a database or not, and execute specific code according to the condition. This is what I have tried:

$query = "SELECT * FROM users WHERE 
            username='$username' 
            OR email='$email' 
            LIMIT 1";
$result = mysqli_query($db, $query);
while ($row = mysqli_fetch_assoc($result)) {
    if(count($row) > 0) {
        echo "The user exists";
    } else {
        echo "The user does not exist";
    }
}

It echos "The user exists" when the user exists but not "the user does not exist" when the user doesn't exist. Why is this?

Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187) You should consider using [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenated values — RiggsFolly, Dec 16 '20 at 17:37
It echos "The user exists" when the user exists but not "the user does not exist" when the user doesn't exist. Why is this? — Nikhil Singh, Dec 16 '20 at 17:39
If the user doesn't exist, it will never enter the while loop. — aynber, Dec 16 '20 at 17:39
FYI Your count is countintg how may columns there are in a row of the `users` table, only if the user exists. Otherwise the code does not even enter the while loop — RiggsFolly, Dec 16 '20 at 17:41

score 1 · Accepted Answer · answered Dec 16 '20 at 17:42

You don't need a loop, since the query returns at most one row. If the user exists it will return that row, otherwise there won't be any rows, and your loop is never entered.

$row = mysqli_fetch_assoc($result);
if ($row) {
    echo "The user exists";
} else {
    echo "The user does not exist";
}

If you don't actually need the information about the user, there's no need to fetch it at all. Just fetch the count of existing rows.

$query = "SELECT COUNT(*) AS count FROM users WHERE username = ? OR email = ?";
$stmt = $db->prepare($query);
$stmt->bind_param("ss", $username, $email);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
if ($row['count'] > 0) {
    echo "The user exists";
} else {
    echo "The user does not exist";
}

How to detect if a user exists in a database

1 Answers1