3

My DB type is MySQL and i'm using PDOStatement::rowCount() to :

  1. Count selected rows on SELECT statement
  2. Get affected rows on UPDATE statement
  3. Get deleted rows on DELETE statement

And i haven't any problem with that and works correctly for me.

But ,recently i've seen that :

  1. php.net says :

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 ...

  1. Some saying that using COUNT(*) in MySQL is much better and more faster instead of using PDOStatement::rowCount() - link
  2. And this answer says:

rowCount() is the most misused PDO function. While it us scarcely needed once or twice per application

why ? i use this function a lot ! Whats wrong with PDOStatement::rowCount() although it works fine for me ?

Edit : So is this an appropriate way to count rows like bellow code ,even if i don't need to fetched data ? :

$stmt -> execute();
$result = $stmt -> fetchAll();
return count($result);
Community
  • 1
  • 1
AliN11
  • 2,387
  • 1
  • 25
  • 40
  • 6
    rowCount() will work; but it means that the db has to access select all the records into the recordset to return; using a SELECT COUNT reduces this overhead on the database, because it only needs to return the count, not the recordset – Mark Baker Jan 22 '16 at 12:01
  • Is the second quote true ? – AliN11 Jan 22 '16 at 12:05
  • 1
    For certain RDBMs PDO will use 'paging' while fetching the results of a select query, which means that the resource holding the results will not hold all records that would be returned by the select, only a subset of them. rowCount() method will return the number of records held by the resource, which may not be accurate. I belive that there can be valid reasons for using rowCount() (such as determining if any records have been returned by a query), but I have seen it (and similar functions for other database APIs) being misused many times. – Shadow Jan 22 '16 at 12:08
  • @Shadow : what do you suggest ? – AliN11 Jan 22 '16 at 12:14
  • And what if i use `rowCount` for `UPDATE` and `DELETE` statements ? – AliN11 Jan 22 '16 at 12:18
  • 1
    For data modification statements it should be fine, all RDBMs I'm aware of return the affected rows for such statements. For selects some return this number, others do not. – Shadow Jan 22 '16 at 12:30
  • There cannot be generic suggestion for this. You have to consider each time you want to use rowCount() whether it is an adequate solution, also taking into account the RDBMS and its version you use. Most importantly: do not assume that it will provide you the right result, always test it to be sure! – Shadow Jan 22 '16 at 12:38
  • 1
    For paging purposes, do not rely on `rowCount()`. To verify how many records were affected by a `DELETE` or `UPDATE`, do use the `rowCount()`. However, to avoid any type of magic, issue a query asking the database how many rows were affected by the last query you issued. That's the actual safe way. Don't rely on things that are cloaked by magic. As for speed and using `COUNT(*)` - the database is always faster than any language when it comes to finding out how much data / records it stores. Ask your database. Also, speed of counting is heavily influenced by the hard disk and amount of data. – N.B. Jan 22 '16 at 12:42
  • @Shadow thanks. Please look my edits. – AliN11 Jan 22 '16 at 16:43

0 Answers0