15

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.

Steven Surowiec
  • 10,030
  • 5
  • 32
  • 37

3 Answers3

26

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()

Imran
  • 87,203
  • 23
  • 98
  • 131
  • 2
    One question, rowCount() works well for me, also on SELECT statements. Has this been implemented since this question was answered? – Mattis Jun 19 '11 at 16:34
  • @Imran rowCount() works for me too for select as well as for queries like `SHOW TABLES LIKE ''` queries. I am using PHP 5.2.6. Don't know my Pecl version. How to check it? Could it be a problem with PHP 5.2.6+? Found this question - http://stackoverflow.com/questions/769767/pdos-rowcount-not-working-on-php-5-2-6 – Sandeepan Nath Jun 18 '12 at 17:24
  • rowCount() only works fine if you previously performed a fetchAll(), but not if you are using fetch() (that is better cause consumes much less memory) – itsjavi Jul 08 '13 at 17:16
  • This is a poor answer since it requires two queries instead of one, and the first query could be a full table scan. Many times you just want to know if ONE example of something exists, like a conflicting username. Using SELECT...LIMIT 1 and then seeing if the result set has more than zero rows is much faster than COUNT(*). I rely on rowCount() for MySQL, but do it prior to any fetch. You do not need to fetchAll before rowCount for the correct number of SELECT results to show. – joshstrike Nov 21 '16 at 03:47
1

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.

aksu
  • 5,221
  • 5
  • 24
  • 39
Ronald D. Willis
  • 19
  • 1
-1

This question is based on several false assumptions and one outdated statement.

First of all, do not confuse number of affected and selected rows. PDO supported the former even back in '09.

Speaking of number of rows returned by SELECT statement - you just don't need that number. The data you have is enough.

And yeah, nowadays rowCount() supports number of rows selected from mysql as well. But again - you don't need that number in an average web-application anyway.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • 1
    "You just don't need that number. The data you have is enough". Oh my.. Translates to "You don't need an emailaddress. Having a website is enough!" I nearly ALWAYS need that number, and steer away from having multiple COUNT queries when I can just count the query itself. It's always good to tell the user that "no entries was found" if that's the case, instead of just looping NOTHING. (Also, you can't check if fetch() returns true when also using a while loop) – mowgli Jul 14 '14 at 17:08