13

PDO apparently has no means to count the number of rows returned from a select query (mysqli has the num_rows variable).

Is there a way to do this, short of using count($results->fetchAll()) ?

Ry-
  • 218,210
  • 55
  • 464
  • 476
Ian
  • 24,116
  • 22
  • 58
  • 96
  • 1
    possible duplicate of http://stackoverflow.com/questions/460010/work-around-for-php5s-pdo-rowcount-mysql-issue - This question is the same as yours. The accepted answer there is what you want. – zombat Apr 23 '10 at 17:34
  • 5
    I saw that question, but the answer sucks. Running a second query to the db just to see the number of rows that is now being stored in a record set is really dumb. – Ian Apr 23 '10 at 18:57

4 Answers4

12

According to the manual, there is a PDOStatement->rowCount method ; but it shouldn't be used (quoting) :

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement.
Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned.
Your application can then perform the correct action.


If you already have a recordset, and want to know how many lines are in it, you'll have to fetch the data, using one of the fetch* methods ; and use count -- like you suggested.

Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
  • 8
    Yeah, thus my problem. How is it possible that PDO *doesn't* have a num_rows function? Every other db connector lib out there has it. – Ian Apr 23 '10 at 17:34
  • If you have the data, you can count the number of lines it contains by yourself *(like you suggested)* ; so I suppose there is no *real* need for a method that would do that... – Pascal MARTIN Apr 23 '10 at 17:35
  • 7
    Grabbing all the data just for the sake of counting, then throwing that array away is very wasteful, especially if it's a big array.. – Ian Apr 23 '10 at 18:56
  • 2
    That's why I said *"if you already have a recordset"* ; if you don't, and want to know how many rows your query could give, when not using a LIMIT clause, I'd say that another query with a count() is the solution you're looking for ? – Pascal MARTIN Apr 23 '10 at 19:26
  • That's a good point. I can just change the way cycle through the record set to use the `$arr = $rs->fetchAll();` instead of `$rs->fetch()`.. dunno why that didn't occur to me. :S – Ian Apr 23 '10 at 19:35
  • That would be a solution ;-) ;; another one would be to increment a counter variable while looping with fetch() -- but this would only work if you don't need to know the final count before the end of the loop. – Pascal MARTIN Apr 23 '10 at 19:37
  • 5
    FWIW, no connector can give you an accurate count of the rows *without fetching them*. Even in the MySQL C API call `mysql_num_rows()`, you can't get the count until you've fetched all the rows. See http://dev.mysql.com/doc/refman/5.1/en/mysql-num-rows.html – Bill Karwin Aug 25 '10 at 04:29
  • is there a method to count rows for PDO yet?? – oldboy Oct 22 '17 at 22:15
4

Although PDO apparently has all means to count the number of rows returned from a select query for mysql, what is more important is that there is no use for such a function in the first place.

Every time you have an idea to use rowCount() for a SELECT query, it would be either superfluous or even harmful. See PDO rowCount():

  • in case you have the data selected already, this function is superfluous as you simply can count the data
  • in case you want to use this function to get the count only, it would harmful, as you should never select data only to count it. Select only count, using SELECT count(*) instead.
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • 3
    Did you even read the accepted answer to this question? From PDO's documentation: "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. " – Ian Apr 05 '14 at 14:05
  • 4
    Well if you are asking for the portable application, don't tag your question with mysql then. – Your Common Sense Apr 05 '14 at 14:11
  • how should i go about counting then? i'm using a PHP MySQL query later in the HTML document to display data from the database, but the element in which i want to display the count comes before fetching and displaying the data??? – oldboy Oct 22 '17 at 22:21
0

Another option that may be closer to the num_rows variable in mysqli and the corresponding C API function mysql_num_rows(). Is to use the MySQL function FOUND_ROWS() that returns the same information without having to count all the records in the result again.

Example:

$pdoDB->query('SELECT FOUND_ROWS()')->fetchColumn()
Angelstam
  • 9
  • 2
-2

The Easiest

  $stmt = $datalink->query('SELECT * FROM projects');
  $rows = $stmt->fetchAll();
  $num_rows = count($rows);
  echo $num_rows ;
The Naga Tanker
  • 441
  • 1
  • 4
  • 17