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()) ?
-
1possible duplicate of stackoverflow.com/questions/460010/… - This question is the same as yours. The accepted answer there is what you want.zombat– zombat2010-04-23 17:34:31 +00:00Commented Apr 23, 2010 at 17:34
-
5I 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– Ian2010-04-23 18:57:15 +00:00Commented Apr 23, 2010 at 18:57
Add a comment
|
4 Answers
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.
8 Comments
Ian
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.
Pascal MARTIN
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...
Ian
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..
Pascal MARTIN
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 ?
Bill Karwin
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 dev.mysql.com/doc/refman/5.1/en/mysql-num-rows.html |
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.
answered Apr 5, 2014 at 13:33
Your Common Sense
158k42 gold badges226 silver badges374 bronze badges
3 Comments
Ian
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. "
Your Common Sense
Well if you are asking for the portable application, don't tag your question with mysql then.
oldboy
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???
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()
1 Comment
Your Common Sense
this answer makes no sense nowadays because you can have the count the regular way
The Easiest
$stmt = $datalink->query('SELECT * FROM projects');
$rows = $stmt->fetchAll();
$num_rows = count($rows);
echo $num_rows ;
1 Comment
Your Common Sense
The Most Dreadful in fact. Not every database is a silly list of one's favorite pets. Some consist of thousands and millions rows.