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()) ?
views:
427answers:
1
A:
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 aSELECTstatement.
Instead, usePDO::query()to issue aSELECT COUNT(*)statement with the same predicates as your intendedSELECTstatement, then usePDOStatement::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
2010-04-23 17:33:09
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
2010-04-23 17:34:01
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
2010-04-23 17:35:39
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
2010-04-23 18:56:19
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
2010-04-23 19:26:29
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
2010-04-23 19:35:52
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
2010-04-23 19:37:24
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
2010-08-25 04:29:51