PDO rowCount only returning one

Question

I have a SELECT query, like this:

$stmnt = $conn->prepare("SELECT post_title, posts_cat FROM posts WHERE posts_cat=:posts_cat");
$stmnt->execute(array(':posts_cat' => $cat_id));
$post_info = $stmnt->fetch();
$count = $stmnt->rowCount();

If there are no posts it shows none, but if there's one or more then it displays only one.

Can someone tell me why this is?

Use PDO's `fetchAll()` function to fetch all the rows into an array — S.I., Nov 01 '16 at 06:46
Example 2 in http://php.net/manual/en/pdostatement.rowcount.php#refsect1-pdostatement.rowcount-examples — u_mulder, Nov 01 '16 at 06:47

score 2 · Accepted Answer · answered Nov 01 '16 at 06:57

2

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

rowCount() is not for a SELECT query, use a separate COUNT query for that or fetch all rows in an array and count its size

answered Nov 01 '16 at 06:57

Hanky Panky

46,730
8
72
95

thank you for your answer. Is it correct to use SELECT COUNT with fetchColumn? – Jordyn Nov 01 '16 at 07:08
Yes it is fine to do so – Hanky Panky Nov 01 '16 at 07:09
Ok great thank you so much. I'm just migrating from MySQLi to PDO. – Jordyn Nov 01 '16 at 07:12
that nasty rumor *again* – Your Common Sense Nov 01 '16 at 09:20
You might want to quash it then because this thesis doesn't. http://stackoverflow.com/a/16776839/1570534 – Hanky Panky Nov 01 '16 at 09:35

Rajesh Patel · Answer 2 · 2016-11-01T08:15:13.617

1

Please do not use rowCount() with select query
use a different query with count() for counting rows

$sql = "SELECT count(*) FROM `posts` WHERE posts_cat = :posts_cat"; 
$stmnt = $conn->prepare($sql); 
$stmnt->execute(array(':posts_cat' => $cat_id)); 
$count = $stmnt->fetchColumn();

edited Nov 01 '16 at 08:15

answered Nov 01 '16 at 06:48

Rajesh Patel

1,946
16
20

1

Wrong answer and absolutely wrong up-votes. – Hanky Panky Nov 01 '16 at 06:56
@Rajesh Patel thank you so much. I have one question whiteout using `$stmnt->fetchAll();` cant you directly use `$stmnt->rowCount();` like in mysqli `num_rows`? – Jordyn Nov 01 '16 at 06:58
thanks @HankyPanky, it was not known to me many thanks – Rajesh Patel Nov 01 '16 at 07:12
You're most welcome! If you edit the answer to reflect that I will be able to take away my down-vote. Currently i cant since its locked – Hanky Panky Nov 01 '16 at 08:09

score 0 · Answer 3 · edited May 23 '17 at 10:32

0

Of course you are getting the right number of rows returned. 1 means that only one row was found. If you want more rows to be found, add more rows to your database to match the condition.

Whether you need such a function at all - is another question, already answered in this post

edited May 23 '17 at 10:32

Community

1
1

answered Nov 01 '16 at 09:44

Your Common Sense

156,878
40
214
345

PDO rowCount only returning one

3 Answers3