Search code examples
phpmysqlpdorowcountprepared-statement

PHP PDO statement returns incorrect row count with SELECT?

Why does this portion of code return true even when it shouldn't be?

$stmt = $dbh->prepare("SELECT COUNT(`user_id`) FROM `users` WHERE `username`= :username LIMIT 1");
$stmt->bindParam(':username', $username);
$stmt->execute();
return ($stmt->rowCount() == 1) ? true : false;

If I enter a username in a field that has already been registered, it returns true which then outputs:

That username has already been taken!

But if I enter a username that hasn't been registered, it still returns true and outputs the line above. I'm unsure why this is and how it can be fixed.

I know that the PHP manual states that rowCount() has some issues with SELECT queries, but I can't find a workaround for this that returns the number of rows affected by a SELECT query.

Solution

  • Because COUNT() will always return 1 row, although its value may be 0.

    You can do a SELECT TRUE instead:

    SELECT TRUE FROM `users` WHERE `username`= :username LIMIT 1

    Or you can check if the value is greater than 0:

    return ($stmt->fetchColumn() > 0);

    BTW - the "? true : false" part is redundant; having the boolean condition by itself does just that.