PHP PDO statement returns incorrect row count with SELECT?

Question

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.

sagi · Accepted Answer · 2011-12-27 21:24:00Z

2

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.

answered Dec 27, 2011 at 21:24

sagi

5,7621 gold badge34 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marked-down Over a year ago

Thanks for you answer - I'd like to know more about SELECT TRUE. Is there any documentation on it? I've tried searching but can't find anything.

Marc B Over a year ago

it's no different than executing select 1 or select 'blahblahblah' or select 1+1. You don't HAVE to select from a table, you can select fixed text which simply gets returned (or returns 2` in the case of 1+1). However, some DBs (like oracle) require you to select form a special pseudo-table named 'dual' for such queries.

hakre · Accepted Answer · 2011-12-27 21:31:18Z

1

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.

answered Dec 27, 2011 at 21:31

hakre

200k55 gold badges454 silver badges865 bronze badges

Comments

radri · Accepted Answer · 2011-12-27 21:31:36Z

0

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;

answered Dec 27, 2011 at 21:31

radri

5313 silver badges7 bronze badges

Collectives™ on Stack Overflow

PHP PDO statement returns incorrect row count with SELECT?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related