get count with db_select

By tomas.teicher on 22 Jul 2011 at 08:49 UTC

Hi,
is it possible to get number of results with db_select function?

Comments

The easiest way would

Anonymous (not verified) commented 22 July 2011 at 09:57

The easiest way would be:

$results = db_select('table')->fields(NULL, array('field'))->execute()->fetchAll();
$count = count($results);

Best way to do this...

Joel Costa commented 29 July 2011 at 11:46

    $result = db_select('table_name', 'table_alias')
            ->fields('table_alias')
            ->execute();
    $num_of_results = $result->rowCount();

thank you both, I think

tomas.teicher commented 29 July 2011 at 13:14

thank you both,
I think better is rowCount(),
but count() works too
thanks
Tomas

Diference

Joel Costa commented 29 July 2011 at 20:38

The difference here is:

If you use count() you need to execute fetchAll() first, this means you need to get all data from your database first. So, if your result have 10.000 rows, than you will need to fetch it from your database and load into memory (assign it to a variable).

With rowCount() you don't need to fetch data from database, so it is more efficient.

similarly, you can add an

drnikki commented 2 November 2011 at 20:53

similarly, you can add an expression. See http://drupal.org/node/1266664

$countnode = db_query("SELECT

itqn2004 commented 27 January 2015 at 04:58

$countnode = db_query("SELECT COUNT(nid) AS count FROM {taxonomy_index} WHERE tid = :tid", array(':tid' => $tid))->fetchField();

http://jobs68.com
Build Drupal website please contact me itqn2004@gmail.com
TanTran

The other way : countQuery()

kunalkursija commented 28 December 2014 at 10:01

countQuery() with dynamic queries is also one of the way to acheive this.


$results = db_select('table')->fields(NULL, array('field'))->countQuery()->execute()->fetchField();

print_r($results);

This is 10x more efficient

chrisroditis commented 10 October 2016 at 14:21

This is 10x more resource efficient than using ->rowCount() after execute()

A healthy disregard for the impossible.

This is the proper way. Be

jeff h commented 10 November 2016 at 03:11

This is the proper way. Be warned though—stupidly it returns the integer count as a string.

_________
http://marmaladesoul.com

Major performance improvement

Anthony Robertson commented 1 March 2018 at 19:48

While it wasn't a problem until my table got huge, this method is incredibly faster.

Here are some results obtained from devel query log, based on my table and data:

db_select('table', 'alias')->fields(NULL, array('a_field'))->countQuery()->execute()->fetchField();
7ms

db_select('table', 'alias')->fields('alias')->execute()->rowCount();
4365ms

Wow, that's a pretty big

jaypan

Victoria, BC

commented 2 March 2018 at 05:25

Wow, that's a pretty big difference. Nice benchmarking. It definitely makes more sense though - with the faster query you're simply querying the number of rows. With the slower query, you're selecting all the data in the rows, then counting it in PHP after it's been retrieved.

Contact me to contract me for D7 -> D10/11 migrations.

Thanks, Anthony for

samaphp

Arabic

Riyadh, SA 🇸🇦

commented 27 February 2019 at 15:13

Thanks, Anthony for mentioning and sharing this result. (thumbsup)