Returning count from database

Question

I have the following script which is not working as I am expecting it to work:

$DBH = new PDO( "mysql:host=localhost;dbname=database_name", "user", "pass" );
$DBH -> setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$STH = $DBH -> prepare( "select count( users_id ) from table1 where username = :username" );

$STH -> bindParam( ':username', $_POST['username'], PDO::PARAM_STR, 100 );

$STH -> execute();

$strCount = $STH -> rowCount();

if( $strCount == 1 ) {
    echo $strCount;
    echo "user has already registered, do nothing";
} elseif ( $strCount == 0 ) {
    echo $strCount;
    echo "user has not registered, insert into db";
} else {
    echo $strCount;
    echo "something is wrong, should only ever be 0 or 1";
}

It always seems to return 1 for $strCount

In table1, I just have 1 row where the username is username1. If the posted value is username1, then I expect $strCount to be 1, but for some reason, if the posted value is username2, I am still getting $strCount as 1.

Anyone know what I am doing wrong?

possible dup http://stackoverflow.com/questions/460010/work-around-for-php5s-pdo-rowcount-mysql-issue — KJYe.Name, Apr 15 '11 at 18:51

score 1 · Accepted Answer · edited Apr 16 '11 at 00:15

1

you are doing count twice ! count() and rowcount(). PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

Try doing regualr fetch

$count = $db->query("select count(*) from table")->fetch();

edited Apr 16 '11 at 00:15

KJYe.Name

16,969
5
48
63

answered Apr 15 '11 at 18:48

Stewie

3,103
2
24
22

What if I get rid of the count in the sql. Will that solve the problem too? – oshirowanen Apr 15 '11 at 18:53
1

get ride of count at any one place. But I would recommend get rid of it in the PDO phase. When you do count in SQl, it only returns count, thus saving CPU, network bandwidth and overall keeps it clean. While in the other case, it has to return the number of rows and then look at the count (affected) – Stewie Apr 15 '11 at 18:56

score 1 · Answer 2 · answered Apr 15 '11 at 18:48

1

You're doing a COUNT() in the query, which means you'll only ever get a single row back, with one field, containing the count of the records.

You have to retrieve that row and field, then base your decisions on that value.

answered Apr 15 '11 at 18:48

Marc B

356,200
43
426
500

What if I get rid of the count in the sql. Will that solve the problem too? – oshirowanen Apr 15 '11 at 18:54
1

No, then you're forcing the database to retrieve all the matching rows, and then simply throw them away. Since all you want is a row count, the query-based COUNT() function is far more efficient. – Marc B Apr 15 '11 at 18:55

Babak Naffas · Answer 3 · 2011-04-15T18:55:31.700

0

count(blah) will return a single row with the value of the count. Instead of calling rowCount(), I think you need to call fetch(..) after you execute and read the value from there. This is based on the PHP documentation.

http://www.php.net/manual/en/pdostatement.fetch.php

I'm not a PHP developer so I don't want to give you bad code.

edited Apr 15 '11 at 18:55

answered Apr 15 '11 at 18:48

Babak Naffas

12,395
3
34
49

Which is exactly what I thought I was doing, but the overall code is not working. – oshirowanen Apr 15 '11 at 18:49

Returning count from database

3 Answers3