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

Answer 1

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];

Answer 2

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

Answer 3

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.

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

Answer 5

Keep in mind that a PDOStatement is Traversable. Given a query:

$query = $dbh->query('
    SELECT
        *
    FROM
        test
');

It can be iterated over:

$it = new IteratorIterator($query);
echo '<p>', iterator_count($it), ' items</p>';

// Have to run the query again unfortunately
$query->execute();
foreach ($query as $row) {
    echo '<p>', $row['title'], '</p>';
}

Or you can do something like this:

$it = new IteratorIterator($query);
$it->rewind();

if ($it->valid()) {
    do {
        $row = $it->current();
        echo '<p>', $row['title'], '</p>';
        $it->next();
    } while ($it->valid());
} else {
    echo '<p>No results</p>';
}

Answer 6

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 );
    }
}

Answer 7

Maybe this will do the trick for you?

$FoundRows = $DataObject->query('SELECT FOUND_ROWS() AS Count')->fetchColumn();

Answer 8

You have to use rowCount — Returns the number of rows affected by the last SQL statement

$query = $dbh->prepare("SELECT * FROM table_name");
$query->execute();
$count =$query->rowCount();
echo $count;

Answer 9

What about putting the query results in an array, where you can do a count($array) and use the query resulting rows after? Example:

$sc='SELECT * FROM comments';
$res=array();
foreach($db->query($sc) as $row){
    $res[]=$row;
}

echo "num rows: ".count($res);
echo "Select output:";
foreach($res as $row){ echo $row['comment'];}

Answer 10

That's yet another question, which, being wrongly put, spawns A LOT of terrible solutions, all making things awfully complicated to solve a non-existent problem.

The extremely simple and obvious rule for any database interaction is

Always select the only data you need.

From this point of view, the question is wrong and the accepted answer is right. But other proposed solutions are just terrible.

The question is "how to get the count wrong way". One should never answer it straightforward, but instead, the only proper answer is "One should never select the rows to count them. Instead, ALWAYS ask the database to count the rows for you." This rule is so obvious, that it's just improbable to see so many tries to break it.

After learning this rule, we would see that this is an SQL question, not even PDO related. And, were it asked properly, from SQL perspective, the answer would appeared in an instant - DISTINCT.

$num = $db->query('SELECT count(distinct boele) FROM tbl WHERE oele = 2')->fetchColumn();

is the right answer to this particular question.

The opening poster's own solution is also acceptable from the perspective of the aforementioned rule, but would be less efficient in general terms.

Answer 11

There are two ways you can count the number of rows.

$query = "SELECT count(*) as total from table1";
$prepare = $link->prepare($query);
$prepare->execute();
$row = $prepare->fetch(PDO::FETCH_ASSOC);
echo $row['total']; // This will return you a number of rows.

Or second way is

$query = "SELECT field1, field2 from table1";
$prepare = $link->prepare($query);
$prepare->execute();
$row = $prepare->fetch(PDO::FETCH_NUM);
echo $rows[0]; // This will return you a number of rows as well.