How to get the total number of rows of a GROUP BY query?

Question

From the PDO manual:

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

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.

I found that out only very recently. I had just changed my db abstraction layer to not use SELECT COUNT(1) ... anymore, because just quering the actual rows and then counting the result would be much more efficient. And now PDO doesn't support that!?

I don't use PDO for MySQL and PgSQL, but I do for SQLite. Is there a way (without completely changing the dbal back) to count rows like this in PDO? In MySQL, this would be something like this:

$q = $db->query('SELECT a, b, c FROM tbl WHERE oele = 2 GROUP BY boele');
$rows = $q->num_rows;
// and now use $q to get actual data

With the MySQLi and PgSQL drivers, this is possible. With all PDO it isn't!?

PS. My initial solution was to extend the SQLResult->count method (my own) to replace SELECT ... FROM by SELECT COUNT(1) FROM and just return that number (very inefficient, but only for SQLite PDO). That's not good enough though, because in the example query above is a GROUP BY, which would change the meaning/function of the COUNT(1).

Solution 1

The method I ended up using is very simple:

$query = 'SELECT a, b, c FROM tbl WHERE oele = 2 GROUP BY boele';
$nrows = $db->query("SELECT COUNT(1) FROM ($query) x")->fetchColumn();

Might not be the most efficient, but it seems to be foolproof, because it actually counts the original query's results.

Solution 2

Here is the solution for you

$sql="SELECT count(*) FROM [tablename] WHERE key == ? ";
$sth = $this->db->prepare($sql);
$sth->execute(array($key));
$rows = $sth->fetch(PDO::FETCH_NUM);
echo $rows[0];

Solution 3

It's a little memory-inefficient but if you're using the data anyway, I use this frequently:

$rows = $q->fetchAll();
$num_rows = count($rows);

Solution 4

I don't use PDO for MySQL and PgSQL, but I do for SQLite. Is there a way (without completely changing the dbal back) to count rows like this in PDO?

Accordingly to this comment, the SQLite issue was introduced by an API change in 3.x.

That said, you might want to inspect how PDO actually implements the functionality before using it.

I'm not familiar with its internals but I'd be suspicious at the idea that PDO parses your SQL (since an SQL syntax error would appear in the DB's logs) let alone tries to make the slightest sense of it in order to count rows using an optimal strategy.

Assuming it doesn't indeed, realistic strategies for it to return a count of all applicable rows in a select statement include string-manipulating the limit clause out of your SQL statement, and either of:

1. Running a select count() on it as a subquery (thus avoiding the issue you described in your PS);
2. Opening a cursor, running fetch all and counting the rows; or
3. Having opened such a cursor in the first place, and similarly counting the remaining rows.

A much better way to count, however, would be to execute the fully optimized query that will do so. More often than not, this means rewriting meaningful chunks of the initial query you're trying to paginate -- stripping unneeded fields and order by operations, etc.

Lastly, if your data sets are large enough that counts any kind of lag, you might also want to investigate returning the estimate derived from the statistics instead, and/or periodically caching the result in Memcache. At some point, having precisely correct counts is no longer useful...

Solution 5

If you're willing to give up a hint of abstraction, then you could use a custom wrapper class which simply passes everything through to the PDO. Say, something like this: (Warning, code untested)

class SQLitePDOWrapper
{
    private $pdo;

    public function __construct( $dns, $uname = null, $pwd = null, $opts = null )
    {
        $this->pdo = new PDO( $dns, $unam, $pwd, $opts ); 
    }
    public function __call( $nm, $args )
    {
        $ret = call_user_func_array( array( $this->pdo, $nm ), $args );
        if( $ret instanceof PDOStatement )
        {
            return new StatementWrapper( $this, $ret, $args[ 0 ] ); 
               // I'm pretty sure args[ 0 ] will always be your query, 
               // even when binding
        }

        return $ret;
    }

}

class StatementWrapper
{
    private $pdo; private $stat; private $query;

    public function __construct( PDO $pdo, PDOStatement $stat, $query )
    {
        $this->pdo  = $pdo;
        $this->stat = $stat;
        this->query = $query;
    }

    public function rowCount()
    {
        if( strtolower( substr( $this->query, 0, 6 ) ) == 'select' )
        {
            // replace the select columns with a simple 'count(*)
            $res = $this->pdo->query( 
                     'SELECT COUNT(*)' . 
                          substr( $this->query, 
                              strpos( strtolower( $this->query ), 'from' ) ) 
                   )->fetch( PDO::FETCH_NUM );
            return $res[ 0 ];
        }
        return $this->stat->rowCount();
    }

    public function __call( $nm, $args )
    {
        return call_user_func_array( array( $this->stat, $nm ), $args );
    }
}

View more solutions

Comments

[Nev Stokes]

This has bitten me in the past too but it's always been this way — it hasn't changed suddenly. Postgres will give you the count, MySQL won't. You're assuming that executing the querying and counting the results would be more efficient but what if you have 10s of thousands of rows?

[Rudie]

MySQL will give you the count. PHP's 'native' SQLlite client also. Just not PDO. With any driver.

[Radu Maris]

If you need a solution outside of PDO (if it doesn't suport countRows), you can try something like: "select SUM(1) as count from ... ", I know it works in mySQL, it just add's 1 for every returned row in the query (didn't tested this in sqlLite), in the end the value of "count" = count(*).

[Rudie]

This is what I get back: no such function: FOUND_ROWS

[tradyblix]

hmm..is that an error from your code from actual SQL return when you use FOUND_ROWS? that's odd.

[Rudie]

That's the message from the exception thrown by PDO. (I only use PDO for SQLite, so maybe it was thrown by the SQLite layer inside PDO.) It's not my code, I promise =) edit PDO sucks!?

[Paul DelRe]

FOUND_ROWS doesn't appear in their list of functions. Since it isn't available in the database, it seems that you must use count(*) Source. This is from SQLite, not PDO.

[Rudie]

Yeah, but count doesn't do the same thing... See my example in the question: if there's a GROUP BY, COUNT 'means' something else.

[Rudie]

So there's no way? I don't want to count thousands of rows. I just want to know if there's 0, 1 or more results. I don't want to fetch 2 results when I don't need any. I only want to fetch 1, but I want to know if there were more.

[Denis de Bernardy]

There's absolutely no way for a DB to know if a row is there or how many rows there are in your set without actually looking. The next best things include deriving an approximation using its statistics, fetching an extra row (i.e. limit 11 instead of 10) to know if there's a next page, and to avoid re-counting on every page through caching.

[Geoffrey]

An iterator doesn't necessarily know how many records it contains, from what I understand this will still be counting every record on the client side (in php)

[Rudie]

Well ofcourse it should be looking, but there's a big difference between looking and fetching. That's why db adapters have ResultSet classes: it executed the query, but didn't fetch the results yet. That's what I want (and that's what MySQL, pgSQL and SQLite can do, but apparantly PDO can't).

[Rudie]

which means fetching the results, which is what I want to avoid. The ResultSet itself ($query is an object) should know how many results there are.

[Nev Stokes]

The iterator won't fetch anything until you start to traverse it. In the second example you can see if there are any rows returned by checking if the iterator is valid. Granted, if you want to actually know how many rows were returned you'll need to fetch the result. Ideally the ResultSet (as you describe it) should know how many rows it has, but it doesn't. Them's the breaks with PDO I'm afraid.

[Rudie]

How does this help? It creates a (faulty) COUNT(*) statement that I already have..? I don't see the advantage of the wrapper part... My framework already has a wrapper. A generic Database class and specific adapters like MySQL, SQLite, PDOSQLite and specific resultsets like MySQLResult, SQLiteResult, PDOResult. I could alter the PDOResult class entirely without changing any app functionality or calls (, but I rather not).

[mjec]

Correct, you can't do a fetchAll() later. But, well, you've already done it? So can't you use the result of that instead of fetching again? It may require a bit of rewriting depending on the code you use.

[Denis de Bernardy]

"but there's a big difference between looking and fetching" -- I must have failed to properly communicate that there is little difference between the two. In either case you need to physically visit the DB row; the only difference is that one the one hand side, you grab it (in order), and on the other you count it. My point was that PDO's row count functionality almost certainly does both -- making it slower than a count query you'd write.

[Rudie]

I've altered my result class so it uses ->valid (sort of). Just to check if a resultset is empty or not, before fetching all objects. Empty or not is good enough for me. It's not pretty but it is efficient and it works. In case you're interested: github.com/rudiedirkx/db_generic/commit/…

[Devin Crossman]

I didn't need the data but just wanted to see if the query returned any data so checking if($sth->fetch(PDO::FETCH_NUM) > 0) worked for me. Thanks!

[Jose A]

The solution posted on this answer worked flawlessly. Thanks a lot.

[itsazzad]

What is array($key) ?

[dotancohen]

FOUND_ROWS() is for MySQL, SQLite does not have a similar option. However, for MySQL this is certainly the right answer.

[Admin]

@SazzadHossainKhan it's a prepared statement, it should be something like : $sth->execute(array("key" => $key_value)); with $key_value a variable declared before this block.

[user9342572809]

Is = not ==. stackoverflow.com/questions/12908502/mysql-double-equal-sign

[mts7]

This worked for me once I removed the ORDER BY clause from the query while using MS SQL Server 2012. As I'm using a PDO wrapper, I had to create a new PDO connection since I hadn't fetched any rows.

[Christopher K.]

If the result is huge, fetching the rows one by one is far more efficient, even if you use all of them anyway. If you fetch them one by one and return the data to the browser, you don't need much memory. But if you fetch all, you need the memory for the complete result.

[Rudie]

Has been suggested a few times. What if there are 4012706 results? I only want the number, not all the results.

[Rudie]

Not reading the question well enough. Point is when COUNTint a query with a GROUP BY in it. Results are not as you expect. Was this not obvious enough?

[Rudie]

Not reading the question well enough. Point is when COUNTint a query with a GROUP BY in it. Results are not as you expect. Was this not obvious enough?

[XaviQV]

You're actually not forced to do anything in the foreach loop, so you could use my example with just the first one, or you could replace "$res[]=$row;" with "$i++;" if you don't find a way to do it with more legitime methods.

[Admin]

$query = "SELECT * FROM market.orderList WHERE supplierID=".$uID." group by UID "; $sql = "SELECT count(*) FROM ($query) x";