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 executed by the corresponding PDOStatement Object.

If the last SQL statement executed associated with this PDOStatement was SELECT , some databases may return the number of rows returned by this statement . However, this behavior is not guaranteed for all databases and should not be relied on for portable applications.

I found this recently. I just changed the db abstraction layer to no longer use SELECT COUNT(1) ... because just querying the actual rows and then counting the result will be much more efficient. And now PDO does not support this !?

I do not use PDO for MySQL and PgSQL, but I do it for SQLite. Is there a way (without completely changing the dbal back) to count lines like this in PDO? In MySQL, it will 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 MySQLi and PgSQL drivers, this is possible. With all PDOs, this is not so !?

PS. My initial solution was to extend the SQLResult-> count method (my own) to replace SELECT ... FROM with SELECT COUNT(1) FROM and just return that number (very inefficient, but only for SQLite PDO). This is not very good, because in the example above there is GROUP BY , which will change the value / function of COUNT(1) .

Answer 1

The method I used 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(); 

It may not be the most efficient, but it seems reliable because it really takes into account the original results of the query.

Answer 2

Here is the solution

 $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 3

This is a bit inefficient, but if you use the data anyway, I often use this:

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

Answer 4

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

According to this comment , the SQLite problem was introduced by changing the API in 3.x.

However, you can check how PDO actually implements the functionality before using it.

I am not familiar with its internal components, but I would be suspicious that PDO parses your SQL (since SQL syntax error will appear in the database logs), not to mention trying to make even the slightest sense of it, so that to count the rows using the optimal strategy.

Assuming this is not the case, realistic strategies for returning the number of all applicable strings in a select expression include string manipulation of the limit clause from your SQL statement and any of the following:

• Executing select count () on it as a subquery (this way you avoid the problem described in PS);
• Opening the cursor, running fetch all and counting lines; or
• By opening such a cursor in the first place and likewise counting the remaining lines.

However, a much better way to count would be to perform a fully optimized query that will do this. Most often, this means rewriting significant fragments of the original request that you are trying to paginate - deleting unnecessary fields and the order of operations, etc.

Finally, if your data sets are large enough to take into account any delays, you can also consider returning the estimate obtained from statistics instead and / or periodically caching the result in Memcache. At some point, correct calculations are no longer useful ...

Answer 5

Keep in mind that PDOStatement is Traversable . Given the request:

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

It can be repeated:

 $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 are ready to give up a hint of abstraction, you can use your own wrapper class, which simply passes everything up to PDO. Say something like this: (Warning, code not verified)

 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 help you?

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

Answer 8

You should 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

How to place the query results in an array, where you can do the calculation ($ array) and use the subsequent query lines? 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

This is another question, which, when mistakenly posed, gives rise to many terrible decisions, all complicates the task of solving a nonexistent problem.

An extremely simple and obvious rule for any interaction with the database is

Always select only the data you need.

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

The question is, "how to get the bill wrong." You should not answer it simply, but instead, the only correct answer is: "You 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 incredible to see how many are trying to break it.

Having studied this rule, we will see that this is an SQL issue, not even related to PDO. And, if asked correctly, from the point of view of SQL, the answer would appear instantly - DISTINCT .

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

- The correct answer to this specific question.

The own decision to open the poster is also acceptable from the point of view of the aforementioned rule, but will be less effective in general terms.

Answer 11

There are two ways to 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 the second way

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