Work-around for PHP5's PDO rowCount MySQL issue

Question

I've recently started work on a new project using PHP5 and want to use their PDO classes for it. The problem is that the MySQL PDO Driver doesn't support rowCount() so there's no way to run a query and then get the number of affected rows, or rows returned, which is a pretty big issue as far as I'm concerned. I was wondering if anyone else has dealt with this before and what you've done to work around it. Having to do a fetch() or fetchAll() to check if any rows were affected or returned seems like a hack to me, I'd rather just do $stmt->numRows() or something similar.

Answer 1

Try this and tell me if it works. I'm interested in this as well :)

http://us3.php.net/manual/en/pdostatement.rowcount.php

You can also use the "exec" method, who's return value is the number you want.

Answer 2

You can issue a SELECT FOUND_ROWS() query right after the original SELECT query to get row count.

$pdo->query("SELECT * FROM users");
$foundRows = $pdo->query("SELECT FOUND_ROWS()")->fetchColumn();

See also: MySQL Docs on FOUND_ROWS()

Answer 3

For those of you who are using MySQL stored procedures, this solution isn't really feasible. What I would suggest that you do is have your stored procedure create two rowsets. The first one will contain one row and one column, containing the number of records. The second will be the recordset you will use for fetching that number of rows.

The number of unlimited rows can be a SELECT COUNT(*) with the exact same WHERE clause as the second rowset without the LIMIT/OFFSET clauses.

Another idea could be to create a temporary table. Use your SELECT statement to populate the temporary table. Then you can use "SELECT COUNT(*) FROM tmpTable" for your first rowset and "SELECT * FROM tmpTable" for your second.