CODEWITHSUNDEEP
phpmysqlpdorowcountprepared-statement
marked-down
marked-down

Reputation: 10408

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.

Upvotes: 1

Views: 900

Answers (3)

radri
radri

Reputation: 531

Try simple without binding:

$res = $dbh->query('SELECT COUNT(`user_id`) AS total FROM `users` WHERE `username`= "'.$username.'" ')->fetch();

return ($res['total'] == 1) ? true : false;

Upvotes: 0

hakre
hakre

Reputation: 197787

You are checking the number of result rows. As your query returns always exactly one result row, rowCount() returns 1. The one result row will contain the count, e.g. 0 or 1 in with your query.

You need to check that count value in the result-row or change your query to not return any rows in case the user does not exists.

Upvotes: 1

sagi
sagi

Reputation: 5737

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.

Upvotes: 2

Related Questions