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- gt query  SELECT a  b  c FROM tbl WHERE oele   2 GROUP BY boele     rows    q- gt 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

User · Answer

You have to use rowCount     Returns the number of rows affected by the last SQL statement   query    dbh- gt prepare  SELECT   FROM table name     query- gt execute     count   query- gt rowCount    echo  count

User · Answer

It s a little memory-inefficient but if you re using the data anyway  I use this frequently    rows    q- gt fetchAll     num rows   count  rows

User · Answer

Maybe this will do the trick for you    FoundRows    DataObject- gt query  SELECT FOUND ROWS   AS Count  - gt fetchColumn

User · Answer

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    Running a select count   on it as a subquery  thus avoiding the issue you described in your PS   Opening a cursor  running fetch all and counting the rows  or 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

User · Answer

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  quot how to get the count wrong way quot   One should never answer it straightforward  but instead  the only proper answer is  quot One should never select the rows to count them  Instead  ALWAYS ask the database to count the rows for you  quot  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- gt query  SELECT count distinct boele  FROM tbl WHERE oele   2  - gt 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

User · Answer

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- gt query  sc  as  row        res    row     echo  num rows    count  res   echo  Select output    foreach  res as  row   echo  row  comment

User · Answer

The method I ended up using is very simple    query    SELECT a  b  c FROM tbl WHERE oele   2 GROUP BY boele    nrows    db- gt query  SELECT COUNT 1  FROM   query  x  - gt fetchColumn      Might not be the most efficient  but it seems to be foolproof  because it actually counts the original query s results

User · Answer

Keep in mind that a PDOStatement is Traversable  Given a query     query    dbh- gt query       SELECT               FROM         test       It can be iterated over    it   new IteratorIterator  query   echo   lt p gt    iterator count  it     items lt  p gt        Have to run the query again unfortunately  query- gt execute    foreach   query as  row        echo   lt p gt     row  title      lt  p gt        Or you can do something like this    it   new IteratorIterator  query    it- gt rewind     if   it- gt valid          do            row    it- gt current            echo   lt p gt     row  title      lt  p gt             it- gt next          while   it- gt valid       else       echo   lt p gt No results lt  p gt

User · Answer

Here is the solution for you   sql  SELECT count    FROM  tablename  WHERE key          sth    this- gt db- gt prepare  sql    sth- gt execute array  key     rows    sth- gt fetch PDO  FETCH NUM   echo  rows 0

User · Answer

There are two ways you can count the number of rows     query    SELECT count    as total from table1    prepare    link- gt prepare  query    prepare- gt execute     row    prepare- gt 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- gt prepare  query    prepare- gt execute     row    prepare- gt fetch PDO  FETCH NUM   echo  rows 0      This will return you a number of rows as well

User · Answer

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- gt pdo   new PDO   dns   unam   pwd   opts               public function   call   nm   args                  ret   call user func array  array   this- gt 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- gt pdo     pdo           this- gt stat    stat          this- gt query    query             public function rowCount                 if  strtolower  substr   this- gt query  0  6         select                             replace the select columns with a simple  count                 res    this- gt pdo- gt query                         SELECT COUNT                                  substr   this- gt query                                 strpos  strtolower   this- gt query     from                           - gt fetch  PDO  FETCH NUM                return  res  0                      return  this- gt stat- gt rowCount               public function   call   nm   args                 return call user func array  array   this- gt stat   nm     args

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

Examples related to php

Examples related to database

Examples related to sqlite

Examples related to pdo