4

Does PHP's PDO run a silent select count(*) statement for it's rowCount() when used after a select statement, or does it get it's result using some other approach?

$query = $conn->prepare('select name, alias from accounts where status = 0');

$query->execute();

$queryCount = $query->rowCount();

$profiles = $query->fetchAll(PDO::FETCH_ASSOC);

if($queryCount > 0) {
    print_r($profiles);
} else {
    echo 'No records found';
}

In the above code, everything runs fine, and I'm able to get the correct number of rows as the result. But is there a count statement running in there? How does PHP do this?

jmenezes
  • 1,888
  • 6
  • 28
  • 44
  • Are you only asking about the PDO MySQL driver? – mario Feb 03 '15 at 16:01
  • 1
    `COUNT` is used in a query to count the rows that match a condition. `rowcount()` returns the number of rows **affected** by a query that modified rows. – AbraCadaver Feb 03 '15 at 16:02
  • Since you are using `fetchAll`, using `rowCount` in your case is useless. You can simply execute the query, invoke `fetchAll` and perform `if(count($profiles))`. However, no, PDO does no magic `SELECT COUNT(*)`. Certain databases can return meta information such as number of rows affected etc, and if they do PDO can expose that info to you. However, it's the best if you don't rely on magic nor on `rowCount()`. You can avoid it in majority of use cases and implement clearer, easier to understand code. – N.B. Feb 03 '15 at 16:40

4 Answers4

2

That depends on the PDO database driver really. Despite what the manual says, it usually works for MySQL connections. With recent versions of mysqlnd anyway. Older versions and the old libmysqlclient interface can be initialized with PDO::MYSQL_ATTR_FOUND_ROWS to also return row counts for SELECT statements.

There's no automatic SELECT COUNT() requery when you ask for ->rowCount(). The driver receives and keeps a uint64_t row_count; internally. Server responses pretty much always include a result row count for prepared statements.

For ->fetchAll and iterations, the PDO mysqlnd driver even just manually set->row_count++ calculates it. Have a look at https://github.com/php/php-src/blob/master/ext/mysqlnd/mysqlnd_result.c for what's actually happening.

The older mysql driver calls mysql_num_rows.c which only returns the correct result count after all rows have been fetched.

mario
  • 144,265
  • 20
  • 237
  • 291
0

From here:

Example #2 Counting rows returned by a SELECT statement

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement.

Means: you may not rely on what you have currently implemented!

Axel Amthor
  • 10,980
  • 1
  • 25
  • 44
0

No. From the docs:

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.

In the deprecated PHP function mysql_num_rows(), the MySQL function mysql_num_rows() was used. I suppose PDO does the same.

More information can be found here.

NB: this means that your current code may work in some cases, but you can't rely on it. Use a COUNT(*) query instead.

Community
  • 1
  • 1
  • 1
    Useful to point out, also, that both `SELECT COUNT(*) ...` and `->rowCount();` are executed by the database. The difference is that the first gives you an accurate number of records as you've specified in the sql statement, and the second just asks the DB "Give me the record counts affected by that last statement I just executed", which as pointed out by the documentation, can be unreliable. – JNevill Feb 03 '15 at 16:07
  • Absolutely true. And in any case one should use the database to compute this number as it's far more efficient on that than PHP or another client implementation. –  Feb 03 '15 at 16:08
-2

From PHP's docs:

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

Styphon
  • 10,304
  • 9
  • 52
  • 86
casraf
  • 21,085
  • 9
  • 56
  • 91