get number of rows with pdo

Question

I have a simple pdo prepared query:

$result = $db->prepare("select id, course from coursescompleted where person=:p"); 
$result ->bindParam(':p', $q, PDO::PARAM_INT);
$result->execute();
$rows = $result->fetch(PDO::FETCH_NUM);
echo $rows[0];

the echo seems to be returning the ID value of the record, not the number of records returned by the query?

any idea or explanation for this?

score 18 · Answer 1 · answered Nov 02 '12 at 13:16

You've executed a query that returns rows from the database, fetched the first row from the result into a variable and then echo'd the first column of that row.

If you want to count, do an SQL count()

$result = $db->prepare("select count(*) from coursescompleted where person=:p"); 
$result->bindParam(':p', $q, PDO::PARAM_INT);
$result->execute();
$rowCount = $result->fetchColumn(0);
echo $rowCount;

Anirudh Ramanathan · Accepted Answer · 2012-11-02T13:59:59.167

11

PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set, starting at column 0

You aren't fetching the row-count at all.

SELECT COUNT(*) FROM coursescompleted where person=:p

This query would return total rows in $rows[0];

EDIT: Please see @ray's answer. using count(id) is better than count(*) for InnoDB.

You could get row-count in the following manner, from your earlier query.

$row_count = $result->rowCount();

But be warned:

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

Documentation

edited Nov 02 '12 at 13:59

answered Nov 02 '12 at 13:15

Anirudh Ramanathan

46,179
22
132
191

Thanks, works perfectly. my problem is in my body I fetch the returmed results using `while($row = $result->fetch(PDO::FETCH_ASSOC))` how could I accomplich both with a single mysql query or do I need two for each function? Thanks again. – Smudger Nov 02 '12 at 13:24
I'm not sure. Could you try `$row_count = $result->rowCount();` with your old query? Some report that PDO_MySQL driver allows that to work, despite the documentation saying otherwise. – Anirudh Ramanathan Nov 02 '12 at 13:30
If that doesn't work, I think you could still count the rows in PHP as you iterate through the rows, in `fetch`. Not sure how bad that is though. – Anirudh Ramanathan Nov 02 '12 at 13:37
You said you were using the contents of the query also, does that mean you now run 2 queries, one for row count and one for the actual data? – Martin Lyne Nov 02 '12 at 20:33

score 3 · Answer 3 · answered Nov 02 '12 at 13:24

3

Assuming id is the primary key use:

 SELECT COUNT(id) FROM coursescompleted WHERE person=:p;

Avoid a count(*). If your storage engine is InnoDB (possibly others except MyIsam) you'll take a performance hit.

answered Nov 02 '12 at 13:24

Ray

40,256
21
101
138

Are you certain there is a performance hit? [This answer](http://stackoverflow.com/a/1697144/759019) says otherwise, for mysql. – Anirudh Ramanathan Nov 02 '12 at 13:27
1

@Cthulhu I'm certain. MyISAM can do count(*) and count(id) if id is a PK in the same amount of time (memory tables too are good with count(*)'s), but if your table is Innodb storage engine you're going to shoot your foot: http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/ – Ray Nov 02 '12 at 13:31

Martin Lyne · Answer 4 · 2012-11-02T20:01:03.863

0

Try echo count($rows); as $rows is an array.

Edit:

To use the results

$rows = $result->fetchAll(/* nothing here */);
if(count($rows) > 0) {
  // Show results, perhaps using a foreach($rows as $row)
} else {
 echo "Sorry, no results found";
}

edited Nov 02 '12 at 20:01

answered Nov 02 '12 at 13:15

Martin Lyne

3,157
2
22
28

1

This would give him the number of columns fetched, i.e. 2, every time. PDOStatement::fetch() returns a single row. PDOStatement::fetchAll() returns the entire result set as an array – Stephen Nov 02 '12 at 13:17
1

That's what the question is asking "the echo seems to be returning the ID value of the record, `not the number of records returned` by the query?" – Martin Lyne Nov 02 '12 at 13:17
Though I suppose it doesn't look like he actually needs the query contents, so an SQL `count()` is preferable. – Martin Lyne Nov 02 '12 at 13:20
the number of records returned !== the number of columns in each record. – Stephen Nov 02 '12 at 13:23
Thanks, I need the query content and the number of rows. so if there is content, display it or if no content show user message the there is no content... if that makes sense. thanks again. – Smudger Nov 02 '12 at 13:27
1

@Smudger: Sounds like you could just execute the query normally then use the `count($rows)` to see if it found anything, I'll expand answer a little. – Martin Lyne Nov 02 '12 at 13:31
Using PHP 7.1 and the PDO library this is the *only* thing that works that is not intrusive to the code. Porting from PHP 5.6 and there is no easy way to get numRows. rowCount() returns -1, so that's no good. – Michael Fever Nov 03 '17 at 18:45

score -2 · Answer 5 · answered Feb 16 '16 at 10:22

You can use this

<?php
$result = $db->prepare("select id, course from coursescompleted where person=:p"); 
$result ->bindParam(':p', $q, PDO::PARAM_INT);
$result->execute();
$rows = $result->rowCount();
echo $rows;
?>

This sometimes does not work on SELECT queries. But based on personal experience, and since you didn't mention porting it to other database systems, it should work on MySQL.

More info is in PHP manual here

get number of rows with pdo

5 Answers5

Linked