PDO rowCount() always returning 1

Question

I'm making a script to add new users.

All is working fine, but now I'm trying to filter out duplicate emails. So if the email already exists it should give an error message.

But for some reason $count always is > 0...

This is my code:

$query = $db->prepare("SELECT COUNT(*) FROM `users` WHERE `email` = ':email'");
$query->execute(array(
       ':email' =>  $email
        ));
$count = $query->rowCount();
echo $count;

What am I doing wrong?

Probably because `SELECT COUNT(*)` is returning `0` for the row, which is `1` row. Either change to `SELECT \`email\`` or get the returned value from the query, i.e. `$query->fetch()[0];` — Sean, Dec 06 '15 at 14:50
Just a guess but try adding $query->store_result() after your execute statement. I had this same problem with a mysqli query and that fixed it but I don't know a lot about PDO so it really is just a guess. — user3154948, Dec 06 '15 at 14:50
@user3154948 `->store_result()` is a `mysqli` method, and is not a `PDO` method, so that won't work here. — Sean, Dec 06 '15 at 14:54
@sean: Thanks, that solved it. Although, I used the query in phpmyadmin and `count(*)` returned 0, but didn't rinkle my bell. But then your answer made it clear.. Thanks ! Write as an answer and I'll accept it :) — Jeroen Bellemans, Dec 06 '15 at 14:56
@Sean I wondered if that might be the case, hence the guess statement...LOL — user3154948, Dec 06 '15 at 14:58
Also, you have quotes around your placeholder `':email'`, which are not needed, and may cause issues. It should just be `...WHERE \`email\` = :email`. — Sean, Dec 06 '15 at 15:05
Ok, any examples of issues which can occur? I can still accept your answer if you write one btw ;) — Jeroen Bellemans, Dec 06 '15 at 15:08
[The most popular APIs available in PHP, PDO and MySQLi, expect *unquoted* placeholders](http://stackoverflow.com/a/11321508/689579) and if you look at the docs [`PDO::prepare`](http://php.net/manual/en/pdo.prepare.php) all examples show *unquoted* placeholders. — Sean, Dec 06 '15 at 15:24

score 2 · Accepted Answer · edited Mar 04 '18 at 04:28

2

SELECT COUNT(*) is returning 0 for the row, which is 1 row.So change count(*), to email

<?php
$query = $db->prepare("SELECT email FROM `users` WHERE `email` = :email");
$query->execute(array(
':email'    =>  $email
));
$count = $query->rowCount();
echo $count;
?>

edited Mar 04 '18 at 04:28

ishegg

9,685
3
16
31

answered Dec 06 '15 at 14:56

Vigneswaran S

2,039
1
20
32

1

Your answer is phrased almost exactly like [my comment](http://stackoverflow.com/questions/34118646/pdo-rowcount-always-returning-1?noredirect=1#comment55986290_34118646) that was posted 6 mins before you posted. coincidence? – Sean Dec 06 '15 at 15:07
@Sean: but the answer will be the same always. more over u dint say how to solve it is "SELECT email FROM `users` WHERE `email` = ':email'" result the answer, which he needs. am I right ? – Vigneswaran S Dec 06 '15 at 15:10
@Sean: even the first votes up for your comment is given by me :). know that also – Vigneswaran S Dec 06 '15 at 15:12
I did say how to solve it. [Either change to `SELECT \`email\`` or get the returned value from the query, i.e. `$query->fetch()[0];`](http://stackoverflow.com/questions/34118646/pdo-rowcount-always-returning-1#comment55986290_34118646). I don't need the rep, so I am glad you answered, just a warning that some people on SO will downvote when they think someone posted an answer by taking from some else's comment. – Sean Dec 06 '15 at 15:15
@Sean: relly i dint saw your Select Either change to SELECT `email` or get the returned value from the query, i.e. $query->fetch()[0]; comment. please post your answer. i will delete my answer since you have solved it first it seems. no worries :) – Vigneswaran S Dec 06 '15 at 15:18
I am good with your answer. I like the fact you added a screenshot to show that `SELECT COUNT(*)` still returns a row, even if the value of that row is `0`. so +1 from me. – Sean Dec 06 '15 at 15:25
@Sean: :) only 4 comments are visible to others who is not involving into comments. ie) add a comment | show 3 more comments .. so only i dint saw your 5th comment (Either change to SELECT `email` or get the returned value from the query, i.e. $query->fetch()[0];). Don't mistake me – Vigneswaran S Dec 06 '15 at 15:29
`':email'` there shouldn't be quotes around that. It's taken as a string literal rather than a bind. – Funk Forty Niner Dec 06 '15 at 15:45
@Sean : sir,one thing i should now. is there any advantage of getting good reputation. will it helps for my employment. since still i am unemployed :(. no idea about reputations. – Vigneswaran S Dec 06 '15 at 15:48
1

@VigneswaranS no, the rep points are just here on SO. The more rep points you have, the more privileges you have here on SO http://stackoverflow.com/help/privileges. Unfortunately I doubt rep points here on SO will directly help your employment, but participating here on SO will hopefully increase your learning/knowledge which might be helpful in your job searching. – Sean Dec 06 '15 at 15:55
@Sean: thanks for your valuable comment sir :). yes here i am learning more – Vigneswaran S Dec 06 '15 at 15:57
1

Reputation helps in just that, your "reputation" to give good answers and with no syntax errors. Your answer still contains the same syntax error that the OP did `= ':email'");` in the `WHERE` clause, having quotes around it which MUST read as `= :email");` and you need to correct it. Future visitors to the question/answer may think that the syntax is correct, where in fact it is NOT. – Funk Forty Niner Dec 06 '15 at 16:05
@Fred-ii- updated. thanks for finding the syntax error – Vigneswaran S Dec 06 '15 at 16:16

PDO rowCount() always returning 1

1 Answers1